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.