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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 885 views
  • 4 likes
  • 6 in conversation