XLSX-SAS Date issue

[Akshaya_1397]

I just wanted to know that.

I have a excel file where

1.The  date value in the excel cell shows as mm/dd/yy format.(Even after expanding the cell)

2.When i click on the cell it shows with mm/dd/yy 8:24:20 AM.

3.When i check using =value() it displays mm/dd/yy format.

4.when i exported to sas it shows as mm/dd/yy format as numeric 8.

why is this so? any idea about how to handle this data as i would require the exact value to compare against another date field.

Thanks in Advance.

[PaigeMiller]

---

@Akshaya_1397 wrote:

3.When i check using =value() it displays mm/dd/yy format.

---

What does this mean? I don't understand. Please provide more details.

---

4.when i exported to sas it shows as mm/dd/yy format as numeric 8.

---

How did you import this into SAS? What steps? What code?

What happens if you change the format in SAS to a datetime format, like DATETIME16. ??

[Kurt_Bremser]

In Excel, time is stored as a fraction of a day, so dates and datetimes share the same storage method (dates having no fractional part).

Are the dates imported correctly into SAS? If yes, you will probably need to get rid of the hidden time part by using the FLOOR() function.

[Tom]

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.