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.

[ballardw]

Some minor things that when dealing with conversion from Excel to SAS sometimes aren't minor.

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

EXCEL and SAS both use numeric values for date, time or datetime values. The way tell that such values are intended to be used as such is shown by applying a date, time or datetime format. The underlying numeric values do not change  but the format may.

Depending on the particular method used to convert data between Excel and SAS, SAS may attempt to duplicate the display appearance of the dates. If you right click on a cell populated with one of these date values in EXCEL you should see a cell format box pop up that will indicate what role the cell plays such as General, Number, Currancy, Date and a few others. If the cell is a date then that role will be selected and another part of the window should show the current display format in effect though Excel will provide an example appearance (generally) to select. SAS format descriptions use letters such as DD to indicate the day of month position, MM the month position and YY the year position in a date. Some of the behavior of the order of those fields can depend on your national language so it is not impossible to see a value in Excel appear differently in SAS because the numeric value in Excel could be displayed in day moth year order and get transposed to a month day year order if that is the national preference set in SAS (or vice versa).

On not uncommon behavior is for an Excel sheet to have columns that mix numeric values with date display formats and character values of similar appearance if people are entering or modifying data manually. At which point you have have missing values or character date values, again depending on how you convert the data.

Using SAS you do want to get some terminology a bit clearer when using the word "format" as that is a very specific meaning. A variable with have a format and the name is important and would be reported by clicking on displayed tabled headings or running Proc Contents on a data set or even using one of the information functions. Your SAS data set displays a value like  mm/dd/yy but that is not a format as the format name is not allowed to have special characters. The FORMAT might be MMDDYY10. to show 10 characters, default using a / between the elements which would be displayed with 2 digits for month and day and 4 digits for the year. It is a good idea to specify the exact format SAS uses for a variable as some "problems" may well be addressed by changing formats such as displaying more or fewer characters, different or no delimiter between date or time elements and even which elements to display. SAS supplies over 50 date related formats and you can create custom appearances for dates using Proc Format if one of the existing formats doesn't fit your need.