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

1. Peter Crawford
Posted June 15, 2012 at 3:18 am | Permalink

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
Posted June 18, 2012 at 9:21 am | Permalink

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. Joe Kelley
Posted July 10, 2012 at 2:02 pm | Permalink

This is pretty cool stuff Chris. Thanks.
--Joe