BookmarkSubscribeRSS Feed
Pyrite | Level 9

Hi All,

I read excel file(xlsx) and i have one Date variable. When i read it using proc import i get strange values.

Firs of all the Date variable was converted to character variable, The second, the values i get are very strange.

For example, in excel date value was 28/09/2024 and it was converted to 45563 and not to 23647.(of course it character value).

Please help... What's wrong?

Thank you


Diamond | Level 26

Your favorite internet search engine finds lots of correct answers. Search for:


convert excel date to SAS date


Searching in this forum also finds lots of correct answers.

Paige Miller
Super User Tom
Super User

So the Excel file has mixed numeric (dates are numbers in SAS and Excel) and character cells.


Since your example cell is using the confusing DMY display order for the components of the date you should probably also double check any date value where the DAY value is less than 13 because Excel might have mistakenly confused the month of the year for the day of the month.  For example it might convert the tenth of December into October twelfth.  Note the same thing can happen if you display date in the equally confusing MDY order.  Use either YMD order or DATE9 style to avoid confusion.

Super User

What you get is the raw number behind a date in Excel. Convert to numeric and add '30dec1899'd.

Something confuses PROC IMPORT and the SAS Excel engine.

Save the data to a csv file and read that with a DATA step, so you have full control over the process and do not have to rely on the guessing of PROC IMPORT.

Opal | Level 21

Following what @Kurt_Bremser suggests but for repeated automated processing where I can't change for the data source being Excel what I've done in the past:

1. Proc Import including the headers as data to guarantee that all columns map into SAS character variables

2. Proc Export to a text file

3. SAS data step reading the text file with full control how the data gets read and mapped to SAS variables. Here one can also include some DQ like if the data is in the expected structure - something that's a "must" especially if the Excel source is user provided.


Above is a bit "cumbersome" but so far the best approach I could come up with that also always works in Unix/Linux environments and deals with all the challenges Excels as data source is throwing at us.

Calcite | Level 5

This occurs because excel stores dates in days from January 1, 1900, while sas stores dates in days from January 1, 1960, so 28/09/2024 is stored as 45563 in excel and 23647 in sas.

Super User

@Xianyu wrote:

This occurs because excel stores dates in days from January 1, 1900, while sas stores dates in days from January 1, 1960, so 28/09/2024 is stored as 45563 in excel and 23647 in sas.

Not really. In fact, 1900-01-01 is day 2, and 1899-12-30 is day zero.

Excel keeps a simplified algorithm (as copied from Lotus 1-2-3) which thinks 1900 was a leap year, therefore dates before 1900-03-01 are calculated incorrectly there. More modern office software (e.g. LibreOffice) has this corrrected and is therefore able to work with dates prior to 1900.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6 in conversation