If your imported dates are off by 60 years and one day . . .

[KDEdmiston]

I was pulling my hair out (what's left of it) for hours trying to figure out why my dates were off by 60 years using the XLSX engine to import an Excel file. Turns out there is a bug. The solution is at this SAS link. Just thought I would share in case others are struggling with the same issue:

Kelly

https://support.sas.com/kb/41/000.html

[mkeintz]

And the link you provided has this problem reported way back in SAS 9.2.   Given the elapsed time with no fix, I presume this can't be fixed within SAS. 

[SASKiwi]

@KDEdmiston - Excel records dates internally as  the number of days since 1 Jan 1900 whereas SAS uses 1 Jan 1960. However Excel erroneously thinks 1900 is not a leap year when it is hence the 60 years and one day difference.

[ballardw]

---

@SASKiwi wrote:

@KDEdmiston - Excel records dates internally as  the number of days since 1 Jan 1900 whereas SAS uses 1 Jan 1960. However Excel erroneously thinks 1900 is not a leap year when it is hence the 60 years and one day difference.

---

For quite a few releases Excel also accepted 0Jan1900 as a valid date.

[Tom]

You should check your file more carefully.  I have never had SAS import dates incorrectly, except when they are entered in cell that SAS will treat as character.  In Excel you can place anything into any cell.  But in a dataset each variable has the same type (number or string) for every observation.  So if you have spreadsheet with some cells that are character strings and others that are numbers then the whole variable is made character.  What happens to dates in that situation is that the number that Excel uses to store the date is converted into a text string (just like any other numeric value in that column).  Note this also happens when you have dates as column headers and you ask SAS to treat the headers as variable names.  Since variable names are always character strings any numeric value in the first row is converted to text.

To convert one of these digit strings into a date use the input() to convert it to a number and arithmetic to adjust for the different base date.  Make sure to attach a date type format so the numbers are printed in a human recognizable way.

sas_date = input(excel_string,32.)+'30DEC1899'd ;
format sas_date date9.;

The reason you use 30-DEC-1899 instead of 01-JAN-1900 is because of difference in which day is zero and because Excel treats 1900 as a leap year (to make it compatible with Lotus 1-2-3, which apparently did it to simplify some calculations).

Also note that Excel has two different base days it uses.  You might also have a spreadsheet that used 01JAN1904 instead. In which case your formula would be 

sas_date = input(excel_string,32.)+'31DEC1903'd ;