READ DATE variable from excel xlsx

[AlexeyS]

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

[PaigeMiller]

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.

[Tom]

Try this question

https://communities.sas.com/t5/New-SAS-User/Reading-Excel-file-with-mixture-of-date-formats/m-p/5159...

that SAS Communities offered as a related topic.

[Tom]

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]

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]

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]

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]

---

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