Read a Microsoft datetime value into a SAS datetime value

8

A colleague was recently working with a web service that supplies some datetime values using the Microsoft Windows internal representation. He called the web service to retrieve those values (along with other data) from SAS, and he needed convert these values to SAS date-time values.

The Microsoft definition for a datetime value is this:

The number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the Gregorian Calendar.

Right. Now, what's a tick?

A tick is 100 nanoseconds. There are 10 million ticks in a second.

A SAS datetime value, as you might already know, is this:

The number of seconds since 12:00 midnight on January 1, 1960.

This is beginning to smell like an algebra word problem. To solve, you start with the ticks value of '01JAN1960:00:00:00'dt, subtract that from the given time value, divide by 10 million, and -- kapow! -- you've got a SAS datetime value. (Note: doesn't account for a few Leap Day nuances for way-out future dates.)

So you need to know the number of ticks for the SAS baseline datetime? Of course, that's 618199776000000000.

Oh, you don't have that memorized? No worries -- we can get these values by using my old friend, Windows PowerShell. In a PowerShell console, I used the Get-Date command to get a datetime object for the value in question, and then queried the Ticks property. I can also use PowerShell to remind me how many ticks are in a second (a different sort of tick check!):

PS C:\> $base = Get-Date "01/01/1960 12:00:00 AM"
PS C:\> $base.Ticks
618199776000000000
PS C:\> $onesec = Get-Date "01/01/1960 12:00:01 AM"
PS C:\> $onesec.Ticks - $base.Ticks
10000000

Here's a SAS program that shows this approach:

data _null_;
  length start 8 date 8 diff 8 sasdate 8;
 
  /* Ticks value for 01JAN1960:0:0:0 = */
  start=618199776000000000; 
  infile datalines dsd;
  input date;
 
  diff=(date-start)/10000000;
  sasdate=intnx('second','01jan1960:00:00:00'dt,diff);
  put sasdate datetime.;
datalines;
634710667200000000
634710662300000000
634543877200034000
run;

Output:

26APR12:19:52:00
26APR12:19:43:50
16OCT11:18:48:40

An aside:
Friends, if you ever get the chance to implement a web service, do not use vendor-specific date or datetime representations. There are ISO standards for these things that will help your consumers! And SAS can interpret the values that comply with these standards, with no fancy math necessary.

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.

8 Comments

  1. Peter Crawford on

    And when you use SAS dates remember to ensure they are formatted (not just as a number) when passed to a client system (like any DBMS).
    Native teradata has almost no tools I could find to help convert 'number of days since 1960' back to an understandable date. (only with a look-up table written by SAS was teradata able to use the internal SAS date)
    Peter

  2. David Henderson on

    Fantastic.

    As an aside when I want to convert SAS datavalues to Excel (1900 date system) and vice versa I remember this useful formula...

    Excel data = SAS date + 21916

  3. A simple code like the following is not able to keep the date format when exported to excel file.
    Is there any way i can keep the date format?

    proc export
    data=work.QUERY_FOR_DATA
    outfile="Y:\\testdate.xls"
    dbms=excel4 label replace
    ;
    run;

    • Chris Hemedinger
      Chris Hemedinger on

      Yes -- don't use DBMS=Excel4, which is the Excel format from 1992!

      Try one of these:

      proc export
        data=sashelp.air replace
        outfile="c:\temp\air.xls"
        dbms=EXCEL /* dbms=EXCELCS on 64-bit SAS */ 
              label 
      ;
      

      This should transfer the values to Excel as a Date, but the format will show as the Excel date.
      Or, with SAS 9.3m2 or later:
      proc export
        data=sashelp.air replace
        outfile="c:\temp\air.xlsx"
        dbms=XLSX 
              label 
      ;
      

      This should retain the original format if that format can be represented in Excel. (Not all SAS date/datetime formats have Excel equivalents.)

  4. Maria Nicholson on

    Thank you so much, Chris. I found this post extremely useful. I'm executing LDAP call routines to pull the pwdLastSet uid attribute from ActiveDirectory. I need to convert the pwdLastSet value to a SAS date in order to compute number of days until a user's password expires. I first thought the pwdLastSet value was in the same date-time representation as your example. But as it turns out, pwdLastSet is the number of 100 nanosecond intervals since January 1, 1601 (UTC) which is a Windows file time. So rather than the Ticks function to convert 1/1/1960 12:00:00 AM, I used ToFileTimeUTC():

    PS C:\> $base = Get-Date "01/01/1960 12:00:00 AM"
    PS C:\> $base.ToFileTimeUTC()
    113288544000000000
    

    Then I had to make sure I had the correct time (accounting for DST). I was then further helped by your Calculating the UTC offset in your SAS session post. Worked great. Once again, thank you!

  5. Pingback: How to convert a Unix datetime to a SAS datetime - The SAS Dummy

Leave A Reply

Back to Top