I watched with wonder as each of my daughters learned how to "tell time." Early in their primary school careers, they brought home worksheets that featured clock faces with big-hand/little-hand configurations that they had to decipher, and exercises that asked them to draw the hands as they should appear given a specific elapsed time. Now my daughters have digital watches, and have even adopted my habit of wearing them with the face on the underside of the wrist and setting the display to 24-hour time -- which serves to confound their friends.
But, to my disappointment, their school has failed to teach my daughters how to read a SAS datetime value, or even the more ubiquitous Unix (POSIX) datetime value. As a parent of the 21st century, I feel it is my duty to fill this gap in their education.
First, let's review how these datetime values are represented:
- A SAS datetime value is the number of seconds that have elapsed since midnight of January 1, 1960 (01JAN1960:00:00:00).
- A Unix (or POSIX) datetime value is the number of seconds* that have elapsed since midnight of January 1, 1970 (01JAN1970:00:00:00).
Conversion from Unix to SAS representation is simple math:
/* Number of seconds between 01JAN1960 and 01JAN1970: 315619200 */ sasDT = unixDT + 315619200; |
Having trouble remembering that 9-digit constant? Then you can get SAS to infer that part for you and use the dhms() function:
/* DHMS function calculates datetime when you provide values for */ /* date, hour, minute, and seconds */ /* In this case, "seconds" is a very high value! */ sasDT = dhms('01jan1970'd, 0, 0, unixDT); |
Raw Unix times are often expressed as UTC, and you might prefer to show local times in your SAS reporting. That's a simple calculation with the (undocumented) gmtoff() function:
/* Convert from UTC to local time using GMTOFF */ sasDT = dhms('01jan1970'd,0,0, unixDT + gmtoff()); |
Update: Bruno told me that TZONEOFF is an official SAS function in SAS 9.4! See the comments for more details how you can use it instead of GMTOFF.
I have one more variation that I use every day. I have a project that reports against a database that stores transaction times in milliseconds instead of seconds. (This uses the database's intrinsic int8 or BIGINT type.) SAS datetime values can represent fractions of seconds, so this conversion simply requires that I divide by 1000. I can use a different SAS datetime format to see the precision (if that's what I want, though it's usually not).
/* for more precision */ format sasDT datetime22.3; /* mlliseconds from transaction database */ unixDTms = 1429193343362; sasDT_ms = dhms('01jan1970'd,0,0, unixDTms/1000); /* result: 16APR2015:14:09:03.362 */ |
Here's a complete program that you can experiment with:
data t; format sasDT_const sasDT_dhms sasDT_local datetime20.; /* for more precision */ format sasDT_ms datetime22.3; /* Unix test datetime of 16APR2015:14:09:03 UTC */ unixDT = 1429193343; /* mlliseconds from transaction database */ unixDTms = 1429193343362; /* Number of seconds between 01JAN1960 and 01JAN1970: 315619200 */ sasDT_const = unixDT + 315619200; /* DHMS function calculates datetime given values for */ /* date, hour, minute, and seconds */ /* In this case, "seconds" is a very high value! */ sasDT_dhms = dhms('01jan1970'd,0,0,unixDT); /* converting a value from milliseconds */ sasDT_ms = dhms('01jan1970'd,0,0,unixDTms/1000); /* Convert from UTC to local time using GMTOFF */ /* use TZONEOFF function in 9.4! */ sasDT_local = dhms('01jan1970'd,0,0,unixDT + gmtoff()); run; |
Sample output from this program (which I ran from my EDT timezone):
* I intentionally avoided any discussion of leap seconds that go along with UTC, but you can learn more about that here.
7 Comments
Hey Chris,
I'm a big INTNX() fan - this also works: SAS_DT=intnx("DTYEAR",Unix_DT,10,"S");
Cheers,
Andrew.
INTNX (a function that increments date/datetime values by a given timespan) is a SAS workhorse that is hugely popular. Thanks for sharing!
Hi Andrew & Chris,
I do not think you can use INTNX in this case. It will cause a difference of -1 day depending on the number of leap days in the years between the Unix date and the SAS date.
The following example shows the difference:
data UNIX_to_SAS;
input UNIX_datetime;
/* The INTNX function accounts for leap years. */
SAS_datetime = intnx('DTyear',UNIX_datetime,10,'s');
SAS_datetime2 = UNIX_datetime + 315619200;
SAS_datetime3 = dhms('01jan1970'd, 0, 0, UNIX_datetime);
format SAS_datetime SAS_datetime2 SAS_datetime3 datetime20.;
datalines;
1285560000
1313518500
1328414200
;
proc print data=UNIX_to_SAS;
run;
Jasper
Chris
Thanks for the insights, can be applied to other system, where the number 0 is not 01Jan1960.
BTW we do not have to rely on the GMTOFF() function. Since SAS9.4 we have the following functions:
TZONEOFF Function Returns the user time zone offset.
TZONES2U Function Converts a SAS date time value to a UTC date time value.
TZONEU2S Function Converts a UTC date time value to a SAS date time value.
Thanks Bruno! These are great updates. Thanks for sharing!
Hi Chris!
I'm in Tokyo at Teradata Japan and we needed this particular blog. I learned something new today. I've never encountered needing to convert these dates. Thanks! Missed getting to got to SGF this year...but I will see you next year.
Wow, I needed this today. First I couldn't figure out why my applied datetime format produced ****. I knew it was a datetime value because a null turned into a 0 and then displayed as midnight January 1, 1970. Another friend clued me in that it was orders of magnitude too large to be a current datetime, so I figured it included milliseconds but not the decimal. So I divided by 1000. That worked and I got the month/day and hh:mm:ss I expected. I didn't realize my years were off until I ran a query to look for records between two dates and kept getting 0 records. Then I realized all my dates were off - by exactly ten years. When I googled 'SAS date off by 10 years', I saw the title of this blog post and BOOM - I realized I had forgotten to account for the different starting points.
For my testing, I can get away with code that wouldn't be suitable for production. I commented exactly what the integers mean.
I went from stamp/1000 to
floor((stamp)/1000)+315619200-14400 format=dateampm20. as Date