Computing a date from the past (or future) with SAS

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 (link to INTNX function doc). Here’s an example that computes the date from 6 months ago:

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‘. Here’s the (very long) list of built-in intervals that INTNX supports.

Using INTNX in SAS macro language

The form I showed above works with DATA step, but I usually capture this value in a macro variable so that I can reference it across different procedures without having to recompute it. To express this in the SAS macro language, I need to wrap those two function calls (for the TODAY function and the INTNX function) 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 = 
 %sysfunc(
  intnx(
   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;
SIX_MO_AGO=20960

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 %SYSFUNC macro function has an optional second argument that accepts a SAS format name, allowing me to create a value for display.

 %let six_mo_ago_fmt = 
  %sysfunc(
   intnx(
   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;
SIX_MO_AGO_FMT=21MAY2017

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.)) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

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 ))) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

The %SYSEVALF function tells the SAS macro processor to evaluate the mathematical expression within the parenthesis. Multiplying the SAS date value by the number of seconds in a day (60 seconds * 60 minutes-per-hour * 24 hours-per-day) results in the equivalent datetime value. (Where have I seen such crazy code? Certainly not in my own SAS programs — no sir. Never happened.)

But this is SAS — there is an easier way (again) with the INTNX function. INTNX can process datetime values too, and supports special datetime intervals. In this case, all I need to do is swap in the ‘dtmonth‘ interval for ‘month‘, and the DATETIME() function for TODAY():

%let six_mo_ago_dt = 
 %sysfunc(
  intnx(
   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;
SIX_MO_AGO_DT=1810991917.93526

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)) 
 noprint 
 ;
tables geo_country_code / out=country_visits_6mo;
run;

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 comprehensive usage information in the SAS/ETS documentation. The functions are incredibly important, as they are often used to calculate dates and times that are used in legal and financial applications. Those industries take their dates/times very seriously. For a fun example of how deep it can get, 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.