Excel stores datetime values as number of days since 1900. It stores the time of day the fractional part (number of seconds since midnight divided by number of seconds in a day).
You can then use the formatting style to display these numbers in different ways. So I made new XLSX file and create three column headers in the first row: DATETIME, DATE and NUMBER. I then and typed "12/12/2025 08:41am" into the first row under the DATETIME column header. And copied the same value into the other two columns. I then used the Formatting menu item to change the style of the DATE column to YYYY-MM-DD and the NUMBER column as just plain number. This is what it looks like Google Sheets.
Then I ran this SAS program to use PROC IMPORT to read in the first sheet from the XLSX file and display the values. First I displayed the values using the format that PROC IMPORT attached to them. Then I displayed them using the BEST32. format instead.
Results:
71 proc import file='~/date_formatting.xlsx' dbms=xlsx out=test replace;
72 run;
NOTE: The import data set has 1 observations and 3 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
73
74 data _null_;
75 set test;
76 put (_all_) (=);
77 put (_all_) (=:best32.);
78 run;
datetime=12DEC25:08:41:00 date=December 12, 2025 number=46,003.36
datetime=2081148060 date=24087.36181 number=46003.36181
So PROC IMPORT noticed that the third column was just a number, so it copied as it was, but attached a format that displayed with with thousands separators.
The second column it noticed that it was being displayed as a DATE value so it adjusted the number of days to reflect SAS's use of a different base date (SAS counts days from 1960). Notice that it did not remove the fraction of a day that 8:41am represents. The SAS date type display formats will ignore that fractional value.
And for the first column it noticed that it had a datetime type display format, so it converted the number into a SAS datetime value (number of seconds since 1960).
On the SAS side if you want to convert DATE to match DATETIME you could multiple it by the number of seconds in a day. (Note: no need to figure out what number that is, just use '24:00't ) Or you could use the DHMS() function and just put the DATE value (which includes the fraction of a day) as the D value and use zeros for the H,M and S values.
To convert NUMBER into a SAS date you would need to change the base value. So just add '30DEC1899'd. The reason that you don't use '01JAN1900'd or '31DEC1899'd is because (1) SAS counts from 0 and Excel counts from 1 (2) Excel decided to mimic the over simplication that LOTUS 123 used and treats 1900 as a leap year, so it has second extra day (unless the value is before 01MAR1900). You might want to use FLOOR() to remove the fraction of day caused by the 8:41am.
To convert the NUMBER into SAS datetime do both. First add '30DEC1899'd to convert it to a SAS date and then convert the SAS date into a SAS datetime.
... View more
