Read a Microsoft datetime value into a SAS datetime value

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.

8 Comments

Peter Crawford on June 15, 2012 3:18 am

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
David Henderson on June 18, 2012 9:21 am

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
Joe Kelley on July 10, 2012 2:02 pm

This is pretty cool stuff Chris. Thanks.
--Joe
jon on May 9, 2014 7:37 am

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 on May 9, 2014 9:01 am
  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.)
Maria Nicholson on June 25, 2014 10:03 pm
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:\&gt; $base = Get-Date "01/01/1960 12:00:00 AM"
PS C:\&gt; $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!
- Chris Hemedinger on June 26, 2014 9:54 am
  
  Maria - thanks for the comment. I'm glad you found the information useful!
Pingback: How to convert a Unix datetime to a SAS datetime - The SAS Dummy

Blogs

Blogs

Read a Microsoft datetime value into a SAS datetime value

About Author

8 Comments