How to read a date value from excel to SAS?

[JQian]

Hi, I am having this weird issue when importing data from excel to SAS. One of the columns in the excel is a date value. (Ex: 05/22/2012). However, in this column, there is also N/A which indicates that there is no date value available for this row. When I import the table from excel to SAS, it stored this column as a character value because of the N/A, I totally understand. But it also automatically changed 05/22/2012 to digits like 354562 and stored it as a character. I thought this number is the numeric format for that date. But when I change the number format back to the date format, it is showing me a completely different date like 05/21/2064. Does anyone know why it is happening and how to solve this problem?

[Tom · Accepted solution]

The digit string is the number that Excel uses for the date.

The default in Excel is to use 1900 as the starting point for numbering days.  To convert it add the date '30DEC1899'd to the value.

datevar = input(charvar,32.) + '30DEC1899'd ;
format datevar date9.;

Note that Excel can also use 1904 as the base date.  If that is happening you probable will want to use '31DEC1903'd as the offset to add. The one day difference is because Excel treats 1900 as a leap year.

So your example date of 22MAY2021 will be the number 44,338 in Excel.

586   data test;
587     datevar='22may2021'd;
588     charvar=put(datevar-'30dec1899'd,10.-L);
589     format datevar date9.;
590     put (_all_) (=);
591   run;

datevar=22MAY2021 charvar=44338

[JQian]

Worked perfectly. Thanks!