BookmarkSubscribeRSS Feed
🔒 This topic is locked. We are no longer accepting replies to this topic. Need further help? Please sign in and ask a new question.
SAS_Tipster
Moderator

Many of us have to convert Excel files into SAS datasets. Converting date/time value from Excel to SAS can be a hassle as both Excel and SAS have different reference dates (i.e. Day 0 in SAS is 1 January 1960 and Day 0 in Excel is 1 January 1900), below formulas offer great help in terms of converting date/time values from Excel to SAS.

  • SAS_date = Excel_date - 21916;
  • SAS_time = Excel_time * 86400;
  • SAS_date_time = (Excel_date_time - 21916) * 86400;

NOTE: Excel has two different datetime "systems" and the default differs depending on the platform. The above formulas assume Excel is using the 1900 date system. If Excel is using the 1904 Date System, use 20454 in the above formulas instead of 21916.

For additional information on the Excel Date Systems, see MS KB article titled XL: The 1900 Date System vs. the 1904 Date System. The 1900 Date system has other well-known quarks, too. For the background of this dual system in the context of a developer's rather funny/scary encounter with Bill Gates, read this blog entry by Joel Spolsky

 

This tip was originally published by Pskoo on sasCommunity.org.

1 REPLY 1
Tom
Super User Tom
Super User

"Magic numbers" like 21916 or 20454 make code confusing and hard to maintain.  And the second one isn't even the right date.

 

For the default Excel base date of 1900 you want to add the date '30DEC1899'd to the value imported from Excel.  Since SAS dates are number of days since 1960 that day in 1899 will be the negative number -21,916.  The reason to use a date value that is two days before 1900 instead of the first day in 1900 is because of the leap year mistake (or simplification) for the year 1900 used in Excel and because of the different decisions made by SAS and Excel about whether to starting counting days from one or from zero.

 

For the 1904 base date the 1900 leap year issue will not impact the dates so instead use '31DEC1903'd as the offset. Which is the number -20,455 not -20,454.

 

Note you will only have this issue when SAS decides to import the column with the dates as character strings.  So to convert the character variable to a number use the INPUT() function.

 

SAS_date = input(Excel_date,32.) + '30DEC1899'x ;
format SAS_date date9.;

 

Also to get the number of seconds in a day you can just use '24:00't rather than hard coding the number 86,400.  Or perhaps just use 24*60*60.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Visit a random SAS tip This SAS Tips board is not open for replies or comments, but we welcome your feedback and questions. Have a question or comment about this tip? Start a new topic in one of our discussion boards, and reference this tip topic.
Discussion stats
  • 1 reply
  • 22964 views
  • 9 likes
  • 2 in conversation