Read a Microsoft date-time value into a SAS date-time value

A colleague was recently working with a web service that supplies some date-time 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 date-time 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 date-time 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 date-time 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 date-time? 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 date-time 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 date-time 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.