11月 222017

Social media has brought anniversary dates to the forefront. Every day, my view of Google Photos or Facebook shows me a collection of photos from exactly some number of years ago to remind me of how good things were back then. These apps are performing the simplest of date-based math to trick me. They think, "We have a collection of photos from this month/day from some previous year -- let's collect those together and then prey on Chris' nostalgia instincts."

Sometimes it works and I share them with friends. Weren't my kids so cute back then? It's true, they were -- but I don't pine for those days. I live in the present and I look towards the future -- as all citizens of the world should.

Looking back at dates in SAS

But you know who likes to look at the past? Managers (and probably a few of my colleagues...). Many of my SAS jobs are in support of date-based reports. They answer questions such as, "what happened in the past 30 days?" or "how much activity in the past 6 months?" When I have SAS date values, going back 30 days is simple. The SAS date for "30 days ago" is simply today()-30. (Because, remember, a SAS date is simply an integer representing the count of days since Jan 1, 1960.)

"6 months ago" is a little more nuanced. I could fudge it by subtracting 183 or 184 from the value of today(), but that's not precise enough for the analytical wonks that I work with. Fortunately, SAS provides a function that can compute any date -- given a starting date -- using just about any criteria you can imagine. The function is called INTNX (

six_mo_ago = intnx(
 'month',  /* unit of time interval */
  today(), /* beginning date */
  -6,      /* number of intervals, negative goes to the past */
  'same'   /* alignment of interval date. "Same" is for same day of month */

If I wanted to go 6 years into the past, I would simply change that first argument to 'year'. 6 days? Change it to 'day'. in %SYSFUNC -- the macro function that breaks out of macro processing to invoke built-in SAS functions. I also need to remove the quotes around the interval and alignment values -- the SAS macro processor will treat these as string literals and would not approve of the quotes.

%let six_mo_ago = 
   month,             /* unit of time interval */
   %sysfunc(today()), /* function to get current date */
   -6,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
 %put &=six_mo_ago;

Output (from today's run):

45          %put &=six_mo_ago;

Notice the result is a number -- which is exactly what I need for date value comparisons. But what if I wanted a formatted version of the date? The

 %let six_mo_ago_fmt = 
   month,             /* unit of time interval */
   %sysfunc(today()), /* function to get current date */
   -6,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
   ), date9.  /* Tell %SYSFUNC how to format the result */
 %put &=six_mo_ago_fmt;

Output (from today's run):

56          %put &=six_mo_ago_fmt;

Adapting INTNX for SAS datetime values

This computed date works perfectly when my data sets contain SAS date values that I want to filter. For example, I can limit the records to those from the past 6 months with code similar to this:

proc freq data=comm.visits (where=(date > &six_mo_ago.)) 
tables geo_country_code / out=country_visits_6mo;

But I realized that some of my data sets use datetime values, not date values. A SAS datetime value is simply the number of seconds since midnight on Jan 1, 1960. I've seen programs that adapt the code above by converting the computed cutoff date from a date value to a datetime value -- it's simple to do with math:

proc freq data=comm.visits (where=(event_time > %sysevalf( &six_mo_ago. * 60 * 60 * 24 ))) 
tables geo_country_code / out=country_visits_6mo;


%let six_mo_ago_dt = 
   dtmonth,              /* unit of time interval */
   %sysfunc(datetime()), /* function to get current datetime */
   -6,                   /* number of intervals, negative goes to the past */
   same                  /* alignment of interval date. "Same" is for same day of month/same time */
 %put &=six_mo_ago_dt;

Output (at this moment):

34          %put &=six_mo_ago_dt;

I can then use the much more readable version of my comparison code:

proc freq data=comm.visits (where=(event_time > &six_mo_ago_dt)) 
tables geo_country_code / out=country_visits_6mo;

Learning more about date and datetime intervals

First, an acknowledgment. I recently had the privilege of presenting at the Quebec user groups with SAS-world superstar Marje Fecht. Marje is an excellent instructor, and she delivered a popular talk about working with SAS date values -- a topic that trips up many new SAS users. It's useful for me -- someone who has used SAS for a long time -- to see a basic topic presented to me as if I were brand new. Her talk reminded me of some good practices that I was able to bring home and apply in my own production SAS jobs. I know that Marje was invited to present this talk at SAS Global Forum 2018, and I hope that I did not steal too much of her thunder. She has a good origin story about the 01JAN1960 date decision. You should attend the conference and see her talk in person.

The INTNX (and its sister function for computing date differences, INTCK) are powerful tools for manipulating date and datetime values. Other programming languages offer complex code libraries to accomplish what these two functions can do as part of Base SAS. They are tricky to learn at first, but once you get the hang of them they can really simplify your SAS programs that deal with time-based data.

Rick Wicklin presented a useful introduction to both functions in INTCK and INTNX: Two essential functions for computing intervals between dates in SAS.

While these functions are available in Base SAS, they are maintained by the developers who look after SAS/ETS (econometrics and time series). You'll find see this thread on the SAS Support Communities.

The post Computing a date from the past (or future) with SAS appeared first on The SAS Dummy.

4月 162015

timeI 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_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 */
  sasDT_local = dhms('01jan1970'd,0,0,unixDT + gmtoff());

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.

See also

Read a Microsoft datetime value into a SAS datetime value

tags: date-time, SAS programming, utc

The post How to convert a Unix datetime to a SAS datetime appeared first on The SAS Dummy.

6月 152012

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
PS C:\> $onesec = Get-Date "01/01/1960 12:00:01 AM"
PS C:\> $onesec.Ticks - $base.Ticks

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 = */
  infile datalines dsd;
  input date;
  put sasdate datetime.;



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.
tags: date-time, PowerShell, SAS programming, web services