In the year 9999...

8

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

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

Related Posts

8 Comments

  1. Pingback: Read a Microsoft date-time value into a SAS date-time value - The SAS Dummy

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

    • Chris Hemedinger
      Chris Hemedinger on

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

  3. Pingback: SAS knows it's a leap year. Do you? - The SAS Dummy

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

    • Chris Hemedinger
      Chris Hemedinger on

      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.

  5. Edward Ballard on

    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.

Leave A Reply

Back to Top