...if man is still alive, will he be importing Excel spreadsheets and wondering why his leap years are off?
I received this report from SAS Technical Support, on behalf of a customer who uses SAS Enterprise Guide to import spreadsheet data:
The date "12/31/9999" will import as "02Jan****" when reading the date from an Excel file. The informat used is date9. If you manually change it to mmddyy10., it will import the date properly.
The year 9999? That's one SAS customer who is planning ahead.
It turns out that this has also been reported in the past with the IMPORT procedure in SAS:
...you will find that the dates in Excel that are 9/9/xxxx beyond the year 4000 are off by one with their SAS values. From the values around the leap day in the year 4000 - 2/28/4000, 2/29/4000, and 3/1/4000, one can see that Excel is acknowledging that 2/29/4000 is a valid date - whereas after the import, SAS has deemed these dates 2/28/4000, 3/1/4000, and 3/2/4000. All subsequent dates beyond 3/1/4000 will be one off from their Excel counterpart. Another 'day off' bump occurs at the year 8000. Leap years are every four years, with the caveat that years ending in 00 are not leap years, except those divisible by 400'. Thus 2000,4000, are leap years. It appears SAS (in this sense) recognizes 2/29/2000 as a valid day, but not for years 4000, 8000.
In our defects tracking system, the developer for the IMPORT procedure included this history lesson, lifted from another site on the web.
This is a compatibility problem between SAS and Microsoft Jet data provider. SAS treats year 4000 and 8000 as non-leap years but the Microsoft Jet provider treats them as leap years.
One Year is Approximately Equal to 365.24219878 Days (Give or Take)
The Egyptians called it 365 and left it at that. The Romans wised up and added the leap day every four years to get the 365.25 day Julian year. By 1582 the calendar was about ten days out of whack, so Pope Gregory XIII included the correction that's still in use today. If the year is divisible by 100, it's not a leap year UNLESS it is also divisible by 400. More recently, proposals for fixes have gotten even better than that. One suggested change is to add on "if the year is also divisible by 4000, it's not a leap year." Of course, we have to adjust one day in year 20,000 too.
This discrepency was fixed in SAS 9.1.3 and SAS 9.2, you'll be happy to know. In plenty of time, I'd say.
10 Comments
Pingback: Read a Microsoft date-time value into a SAS date-time value - The SAS Dummy
Chris,
Didn't the "fix" only return a missing value rather than an error?
While neither of us are likely to still be around then, from what I've been able to research, the 4000 year concept is currently only a proposal and has never been officially adopted.
Art
I didn't test it, Art. But I just scheduled a cron job to test it out in just under 2000 years, and send e-mail to my descendants with the results. (But I'm hoping e-mail has been abolished by then, along with the rack and other torture devices.)
Pingback: SAS knows it's a leap year. Do you? - The SAS Dummy
Chris.....
Expanding on discussions of Dates In SAS....
Is it time for an article explaining what has happened when a date formatted variable appears with asterisks?
A condition that also provides guidance (imho) on a special missing value for dates that would test higher/later than any valid date.
Hope to hear you're interested.....
Peter.C
Asterisks indicate a special date, right? For example, I format my wife's birthday with asterisks.
Okay, actually it happens most often when an incompatible date format is applied to a datetime value -- requiring the use of the datepart() function or else a date-only-display format like DTDATE9. If you have an additional special tip to share on this Peter, I invite you to start a discussion in the SAS programming community.
Some experimentation yields that the MDY function doesn't report an error (forward) until 1/1/20001. Other functions appear to work with dates until then as well.
I ran into this dealing with Y2K issues in 1999 and having to repeatedly respond that the dates I was using in SAS data sets were good. They shut up when I said the dates would work through the year 20,000.
And why would anyone be surprised with an Excel date conversion issue when Excel accepts 1/0/1900 as a "date". Try entering 1/1/1900 in a cell then a formula subtracting 1 (day) => 1/0/1900.
Really neat. The link to mitre.org is broken... luckily the Internet Archive has an archived version here.
Hi,
I have just run a test in SAS9.4 M5 and it appears that the 29th February 4000 has disappeared. I noticed this as I have been required to compare a SAS program and a T-SQL program that are calculating the number of days between two dates, two of which are 04MAR2010 and 04MAR5310 - undoubtedly the result of a data entry error but nevertheless...
The SAS INTCK function returns 1205299 days but the T-SQL DATEDIFF function returns 1205300 days which I have tracked down to SAS not acknowledging the existence of 29th Feb 4000.
Thanks,
Mark
Interesting! I'll need to check this. According to this, there is a proposal to exclude years divisible by 4,000 -- maybe SAS is an early adopter.
Here's a quick test for 100-year leap years:
data leap_years(keep=year); length date 8; do year=2000 to 4400 by 100; /* MISSING when Feb 29 not a valid date */ date=mdy(2,29,year); if not missing(date) then output; end; run;