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());
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.