Prev: Compiled Secure Macro on Unix SAS 9.2
Next: assign a unique random integer to each unique id
From: Savian on 15 Jan 2010 09:18
On Jan 15, 6:30 am, aziegen...(a)ANAXIMA.COM (Allen Ziegenfus) wrote:
> Hey everyone,
>
> Does anyone know why SAS calculates the number of days between 31DEC9999 and
> 01JAN1960 differently than Excel / SQL Server, for example? SAS reports this
> value as 2936547 while Excel reports it as 2936549.
>
> I wanted to create a SQL Server function to interpret SAS dates. The
> function looks like this:
>
> return (dateadd(day,@sasdate, '1960-01-01'));
>
> Unfortunately if you pass this function the value 2936547, which SAS
> interprets as 31DEC9999, it will return 29DEC9999, not 31DEC9999.
>
> Any suggestions about how to change the function to make it mirror exactly
> how SAS interprets dates?
>
> This also seems to have been the cause of various SAS-Excel import problems
> over the years. There is a SAS Note for SAS 8 that 31DEC9999 was interpreted
> as 29DEC9999. We also cannot import this date value from Excel correctly
> with PROC IMPORT under Solaris. Additionally, the Table Viewer in DI Studio
> in Edit Mode displays 31DEC9999 as 29DEC9999.
>
> Allen

I am speculating here but I think you are running into the leap year
issue put in by Excel for Lotus 1-2-3 compatibility. Lotus programmers
had a bug in their leap year calculations and treated 1900 as a leap
year when it was not. Microsoft replicated the bug (yes, they knew it
was wrong) to maintain compatibility back in the day.

You may need to simply hardcode the adjustment in recognition of the
bug. I think it is being thrown off by the year 1900. Background info
(and a great read):

http://www.joelonsoftware.com/items/2006/06/16.html


Alan
http://www.savian.net
From: ajs2004 on 22 Jan 2010 06:04
"SAS can perform calculations on dates ranging from A.D. 1582 to A.D.
19,900."
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm

I don't know whether Excel handles dates accurately up to the year
9999 -
How to use dates and times in Excel - http://support.microsoft.com/kb/214094
doesn't seem to cover it.

On Jan 15, 1:30 pm, aziegen...(a)ANAXIMA.COM (Allen Ziegenfus) wrote:
> Hey everyone,
>
> Does anyone know why SAS calculates the number of days between 31DEC9999 and
> 01JAN1960 differently than Excel / SQL Server, for example? SAS reports this
> value as 2936547 while Excel reports it as 2936549.
>
> I wanted to create a SQL Server function to interpret SAS dates. The
> function looks like this:
>
> return (dateadd(day,@sasdate, '1960-01-01'));
>
> Unfortunately if you pass this function the value 2936547, which SAS
> interprets as 31DEC9999, it will return 29DEC9999, not 31DEC9999.
>
> Any suggestions about how to change the function to make it mirror exactly
> how SAS interprets dates?
>
> This also seems to have been the cause of various SAS-Excel import problems
> over the years. There is a SAS Note for SAS 8 that 31DEC9999 was interpreted
> as 29DEC9999. We also cannot import this date value from Excel correctly
> with PROC IMPORT under Solaris. Additionally, the Table Viewer in DI Studio
> in Edit Mode displays 31DEC9999 as 29DEC9999.
>
> Allen

 | 
Pages: 1
Prev: Compiled Secure Macro on Unix SAS 9.2
Next: assign a unique random integer to each unique id