Prev: closing excel file opened with dde
Next: proc sql & left join & where statement
From: Arthur Tabachneck on 10 Sep 2006 12:38
Dimitri,

I think you have to blame Microsoft for this one. I don't believe Excel
can interpret a date earlier than January 1, 1900.

However, as long as you don't have to manipulate the field as a date in
Excel, you can get around it by sending the date as a string. For example:

data test (drop=dob);
input order 1 name $3-19 @22 dob mmddyy10.;
format dob date9.;
format dob_string $10.;
dob_string=input(put(dob,date9.),$10.);
cards;
1 George Washington 2/22/1732
John Adams 10/30/1735
3
run;

proc export
data = test
outfile = "c:\test.xls"
dbms = excel97 replace;
run;

HTH,
Art
-------------
On Sun, 10 Sep 2006 11:41:40 -0400, Dimitri Shvorob
<dimitri.shvorob(a)VANDERBILT.EDU> wrote:

>SAS-to-Excel Oddity II
>
>I've had my share of troubles importing spreadsheets into SAS (9.1), but
an
>incorrect export is a first. I have a test dataset created with
>
>data test;
> input order 1 name $3-19 @22 dob mmddyy10.;
> format dob date9.;
> cards;
>1 George Washington 2/22/1732
> John Adams 10/30/1735
>3
> run;
>
>When I export it to Excel,
>
>proc export
> data = test
> outfile = "C:\test.xls"
> dbms = excel97 replace;
> run;
>
>
>I get the following warnings
>
>WARNING: During insert: : Data value was not sent for column. dob
>WARNING: During insert: : Data value was not sent for column. dob
>
>and, sure enough, an empty (bar the column name) DOB column. (I have
tried
>'excel' and 'excel97' - I have Excel 2002 - and initially had a missing
date
>in row 1 too; filling it in didn't help). I wonder if other users can
>replicate the problem and perhaps identify the cause. (I wish I could just
>blame SAS/Access and leave it at that :)).
>
>Thank you.
From: Dimitri Shvorob on 11 Sep 2006 06:08
Alas, my PC's hard drive has crashed - karmic retribution for doubting
SAS/Access? :) - so I no longer have SAS and cannot check, but I think
I have tried 19xx dates. I started with the original ones, but deleted
'format' statement in the DATA step, and found that this way, numbers
were output in DOB cells; however, these were not displayed as dates
when date formatting was applied. (Format > Cell). Then I changed the
dates to recent ones, repeated the export, applied date formatting in
Excel, and did get the DOB entries displayed as dates - I recall that
month and day values were correct, but year values shifted.
From: Arthur Tabachneck on 10 Sep 2006 14:16
Dimitri,

Once you get your harddrive back, assuming SAS isn't still angry, you
could also export the dates as numbers, say in the form of yyyymmdd.

That way, while you still couldn't treat them as dates in excel, you would
at least be able to sort by date.

For example:

data test (drop=dob);
input order 1 name $3-19 @22 dob mmddyy10.;
format dob date9.;
dob_number=put(put(dob,yymmddn8.),8.);
cards;
1 George Washington 2/22/1732
John Adams 10/30/1735
3
run;
proc export
data = test
outfile = "s:\test.xls"
dbms = excel97 replace;
run;

Art
--------
On Mon, 11 Sep 2006 05:08:39 -0500, Dimitri Shvorob
<dimitri.shvorob(a)VANDERBILT.EDU> wrote:

>Alas, my PC's hard drive has crashed - karmic retribution for doubting
>SAS/Access? :) - so I no longer have SAS and cannot check, but I think
>I have tried 19xx dates. I started with the original ones, but deleted
>'format' statement in the DATA step, and found that this way, numbers
>were output in DOB cells; however, these were not displayed as dates
>when date formatting was applied. (Format > Cell). Then I changed the
>dates to recent ones, repeated the export, applied date formatting in
>Excel, and did get the DOB entries displayed as dates - I recall that
>month and day values were correct, but year values shifted.
 | 
Pages: 1
Prev: closing excel file opened with dde
Next: proc sql & left join & where statement