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 = %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
%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; |
%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; |