In the year 9999...

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