BookmarkSubscribeRSS Feed
AlexeyS
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

 

7 REPLIES 7
PaigeMiller
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
Tom
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.

Kurt_Bremser
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.

Patrick
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.

Xianyu
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.

Kurt_Bremser
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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1872 views
  • 4 likes
  • 6 in conversation