@snigdhaguha01 wrote:
While I am doing the export using the ODS Excel in the XLSX format the date "12/31/9999" is getting converted to "12/29/9999", rest there is no impact on the output. If I use Proc Export the output is coming fine, only issue is the leading zeros in the dates are getting removed. Please help.
Some time in the past I did an experiment with the SAS dates to Excel. You will find that Excel does very poorly with certain leap years because they have not implemented all of the rules. So engine that translates SAS dates to the Excel offset and then gets displayed by Excel loses a leap day (if you create an output for an entire year you will find two SAS dates that get interpreted by Excel incorrectly.
Consider this code (send to a path on your machine).
data junk;
do year = 4000 to 4004 by 4;
month=2;
day=27;
date=mdy(month,day,year);
tdate= put(date,date9.);
output;
date=date+1;
tdate= put(date,date9.);
output;
date=date+1;
tdate= put(date,date9.);
output;
end;
format date date9.;
keep date tdate;
run;
ods excel file='x:\data\datesuspects.xlsx';
proc print data=junk label;
label tdate='Text version of SAS date'
date = 'Date as converted to Excel'
;
run;
ods excel close;
You will find that Excel has a different date for every day after 28Feb4000 differs from the SAS date. And you will get another day of difference occurring in year 8000 which creates the additional day of difference you see. Excel is not really sophisticated in dealing with dates.
BTW SAS will quit dealing with dates using year 20,001 or greater. Note that the formats will not display any year greater than 9999 though.
... View more
