A SAS user (who lives in the the US) emailed me a question about SAS functions. He was reading UTC (Coordinated Universal Time) datetime values from server logs, and to make future calculations and comparisons easier, he wanted to transform the value to local datetime. The INTNX() function worked great, but did not account for daylight savings time (DST), and, he wondered, “Does SAS have an interval calculating function that takes DST into account?” I couldn’t find one, so I finally had to answer "No, it doesn't". And I really hated having to say that! I thought “Surely, the power of the SAS can bend this data to my will!” And off I went to conquer the problem using the SAS Function Compiler procedure, affectionately known as PROC FCMP.
Introduced in SAS 9.1, PROC FCMP lets you to create custom SAS functions and CALL routines. PROC FCMP syntax is very much like DATA step, and you can leverage most features of Base SAS when defining your routines. The custom functions and CALL routines created are used in subsequent DATA steps or SAS procedures just as you would any standard SAS function or CALL routine.
First I searched for some starter code, and found this excellent tidbit “Sample 24735: Compute daylight saving time”. I thought I remembered that Canada and the US shared the days and times they shifted to DST. Because the US recently legislated changes to DST, I searched the Internet and found this article which says the US and Canada still shift on the same days. And I read it on the Internet - so it must be true!! With the truth now firmly on my side, I set out to write a function what would convert DST to local time, and would work for Canada and US time zones.
The first step was writing a data step prototype which would successfully adjust datetime values. Here is the prototype of the function I want to build:
data TEST; /* Set values for testing */ DT='26JUN12:17:00:00'dt; FORMAT DT DATETIME. NEWDATE DATETIME.; ZONE=-5; year=year((datepart(dt))); day1=mdy(1,1,year); /* Year2006 DST starts 2nd SUN in March, ends 1st SUN in November. */ else do; dst_month_start=intnx('month',day1,2); dst_beg=intnx('week.1',dst_month_start , (weekday(dst_month_start) in (2,3,4,5,6,7))+1); dst_month_end=intnx('month',day1,10); dst_end=intnx('week.1',dst_month_end,(weekday(dst_month_end) ne 1)); end; NEWDATE=dt+(zone*60*60); if dst_beg
This worked like a charm, so now it was time to convert my data step code into a function with PROC FCMP. However, it was annoying to have to look up the correct “minus number” for my time zone every time I used the routine while I was testing. I wanted my function to use the text time zone name instead - ‘Eastern’ for example. So I added a new character variable "Z" and a SELECT group do the lookup for the ZONE variable numeric value. The function takes 2 parameters: DT (a datetime value) and Z (the name of the time zone). Here is my code:
proc fcmp outlib=sasuser.myfunc.UTC2LOCAL; function UTC2LOC(dt,z $); Z=UPCASE(Z); select (z); when ('NEWFOUNDLAND') ZONE=-3.5; when ('ATLANTIC') ZONE=-4; when ('EASTERN') ZONE=-5; when ('CENTRAL') ZONE=-6; when ('MOUNTAIN') ZONE=-7; when ('PACIFIC') ZONE=-8; when ('SASKATCHEWAN') ZONE=-6; when ('ARIZONA') ZONE=-7; when ('ALASKA') ZONE=-9; when ('HAWAII') ZONE=-11; otherwise do; zone=0; PUT 'Valid zones: NEWFOUNDLAND, ALLANTIC, EASTERN, ' 'CENTRAL, MOUNTAIN, PACIFIC, ALASKA, ARIZONA, ' 'HAWAII or SASKATCHEWAN.'; end; end; year=year((datepart(dt))); day1=mdy(1,1,year); /* Year2006 DST from 2nd SUN in MAR to 1st SUN in NOV */ else do; dst_month_start=intnx('month',day1,2); dst_beg=intnx('week.1',dst_month_start ,(weekday(dst_month_start) in (2,3,4,5,6,7))+1); dst_month_end=intnx('month',day1,10); dst_end=intnx('week.1',dst_month_end,(weekday(dst_month_end) ne 1)); end; NEWDATE=dt+(zone*3600); if dst_beg
And now to test my handiwork! This data step will use the new function to convert a UTC datetime value to local time:
options cmplib=sasuser.myfunc; data TEST; do UTC='31JAN12:17:00:00'dt ,'31MAR12:17:00:00'dt ,'30JUN12:17:00:00'dt ,'30NOV12:17:00:00'dt; Newfoundland=UTC2LOC(UTC,'Newfoundland'); Atlantic=UTC2LOC(UTC,'atlantic'); Eastern=UTC2LOC(UTC,'EASTERN'); Central=UTC2LOC(UTC,'Central'); Saskatchewan=UTC2LOC(UTC,'SASKATCHEWAN'); Mountain=UTC2LOC(UTC,'Mountain'); Arizona=UTC2LOC(UTC,'Arizona'); Alaska=UTC2LOC(UTC,'Alaska'); Hawaii=UTC2LOC(UTC,'Hawaii'); output; end; FORMAT UTC -- HAWAII datetime.; run; PROC PRINT data=test noobs; format UTC -- HAWAII tod.; RUN;
It worked just like I had hoped! But I wondered – would it work in PROC SQL? Let’s see:
proc sql; select UTC ,UTC2LOC(UTC,'EASTERN') format=datetime. as Eastern from test ; quit;
Beautiful! I've decided I like PROC FCMP!!
That's all for this episode. Until the next time, may the SAS be with you!
PS: You can download the package for this blog posting here.