ODS Excel in XLSX format - issue with the date output

[snigdhaguha01]

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.

[Tom]

Create a small example dataset that re-produces the issue and post the code to create the dataset and the code used to generate the XLSX file from that dataset.

Why do you have dates that are 7,000 years in the future? Are you working on astrophysics?

[snigdhaguha01]

This is related to Employee data, where dates are not shown as blank. Say, any Member who is still in service will be having Termination_Date = 12/31/9999 , but if the member gets terminate in near future say by beginning of Next year then the Termination_Date changes to 01/01/2020.

We are basically exporting the data from SAS dataset to excel. And if it is Exported using ODS TAGSETS.EXCELXP then it correctly reported as 12/31/9999 , but if we use ODS EXCEL we get the  incorrect Date as 12/29/9999. Except the High End Date ,i.e. 12/31/9999 all dates are coming fine.

Attaching a sample data for your reference .

[Tom]

Why did you post an XLS file instead of an XLSX file?

Here is a simple example:

%let path=c:\downloads\;
data test;
  date1='31dec9999'd ;
  date2=today();
  date3='29dec9999'd ;
  format date: date9. ;
run;

libname out xlsx "&path.date_test1.xlsx";
data out.test; set test; run;
libname out clear ;

ods excel file="&path.date_test2.xlsx" ;
proc print data=test; run;
ods excel close;

Looks like ODS EXCEL is subtracting 2 days but the XLSX libname engine does not.

Try using a different extreme date and see if there is some point in time where it starts getting confused.

[Ksharp]

proc report .........
........
define xx/ style={ tagattr=' type: text '  };

[ballardw]

---

@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.