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

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. 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

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 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:\&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

Maria - thanks for the comment. I'm glad you found the information useful!