Difference In Dates From Data Imported (SAS Local Data Provider 9.42)

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Difference In Dates From Data Imported (SAS Local Data Provider 9.42)

Hi friends,

Upon change of my laptop while I found out the only way to work with *.sas7bdat files was via SAS Data Driver, and I downloaded the complete package of OLE Drivers, while I tried to import the SAS Data either on SQL Server or Excel I found the date fields of the datasets to be reflecting a constant difference of 21916 days in all? In other words, while every other data item seems to be be well enough, a date like 2-Jan-2013 appears as 1-Jan-1953?

Any reason of, and way to overcome, the same?


Accepted Solutions
Solution
‎01-06-2015 10:54 AM
Grand Advisor
Posts: 10,211

Re: Difference In Dates From Data Imported (SAS Local Data Provider 9.42)

Microsoft products use a different base value for dates, I believe 1 Jan 1900, than SAS which uses 1 Jan 1960. Both are just integers. So the 1/1/1953 is the number of days off set from 1/1/1900 in Microsoft terms. The 1 day shift occurs because Microsoft starts with that base day having a value of 1 whereas SAS 1/1/1960 is 0 internally.

I think that if you add 21915 to the SAS date values you'll get the match in Microsoft products. Barring issues such as dates prior to around 1581AD or after 19999AD. I'm also not sure of the limits of Microsoft date interpretations.

View solution in original post


All Replies
Solution
‎01-06-2015 10:54 AM
Grand Advisor
Posts: 10,211

Re: Difference In Dates From Data Imported (SAS Local Data Provider 9.42)

Microsoft products use a different base value for dates, I believe 1 Jan 1900, than SAS which uses 1 Jan 1960. Both are just integers. So the 1/1/1953 is the number of days off set from 1/1/1900 in Microsoft terms. The 1 day shift occurs because Microsoft starts with that base day having a value of 1 whereas SAS 1/1/1960 is 0 internally.

I think that if you add 21915 to the SAS date values you'll get the match in Microsoft products. Barring issues such as dates prior to around 1581AD or after 19999AD. I'm also not sure of the limits of Microsoft date interpretations.

Occasional Contributor
Posts: 12

Re: Difference In Dates From Data Imported (SAS Local Data Provider 9.42)

Hi!

 

I know this is an old topic, but I came across your answer as I was trying to resolve the issue - that %^%#* Excel can't handle dates properly.

 

I'm wondering why we'd add 21915, rather than 21916.  That additional +1 would resolve the single day shift, which you nicely explained as being due to Microsoft counting the base date as 1 while SAS counts it as 0.

 

Am I missing a reason to NOT add 21916?

 

thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 327 views
  • 1 like
  • 3 in conversation