Every year at Halloween, I post an article that shows a SAS trick that is a real treat. This article shows how to use the INTNX function to find dates that are related to a specified date. The INTNX function is a sweet treat, indeed.

I previously wrote an article about how to use the INTCK and INTNX functions to compute with dates. These Base SAS functions are very powerful and deserve to be known more widely. In particular, the INTNX function enables you to compute the next or previous date that is a certain number of time units away from a known date. The "time unit" is usually a day, week, a month, or a year, but the function supports many options.

Recently, a SAS programmer asked how to get "the first and last days of the previous month." The programmer added that the expression needs to be used in a WHERE clause. Because the expression needs to appear in a WHERE clause, it should be concise and not require several statements or temporary variables.

The first day of a previous (or subsequent) time interval

Finding the first day of the previous month is an ideal situation for using the INTNX function. The basic syntax of the INTNX function is
      INTNX(timeUnit, startDate, numberOfUnits)
This form of the INTNX function returns the first day of the specified time unit. For example, the following statements give dates relative to the bombing of Pearl Harbor on 07DEC1941. The time interval is 'month'. Notice that you can ask for dates after the given date (a positive number of time units) or before the given date (a negative number of units). If you specify 0 for the third argument, you get the current month.

data Months;
date = '07DEC1941'd;
FirstDayCurrMonth = intnx('month', Date,  0);   /*  0 = current month */
FirstDayPrevMonth = intnx('month', Date, -1);   /* -1 = previous month */
FirstDayNextMonth = intnx('month', Date,  1);   /*  1 = next month */
FirstDay6Months   = intnx('month', Date,  6);   /*  6 = six months later */
format _ALL_ date9.;
run;
 
proc print data=Months noobs;
run;

Because the time unit is 'month' for this example, the calculated dates are the first day of the months relative to 07DEC1941. If you change 'month' to 'year', all the calculated dates will be 01JAN of some year relative to 1941.

The last (or same) day of a previous (or subsequent) time interval

A cool fact about the INTNX function is that it supports an optional fourth argument that enables you to specify whether you want the calculated date to be at the beginning, the middle, or the end of the specified time interval. You can even specify that you want the "same" characteristics as the source date, which is useful for finding anniversaries of an event. For example, the following statements vary the fourth argument, which can be one of four values:

data FirstLastMiddle;
date = '07DEC1941'd;
FirstDayPrevMonth = intnx('month', Date, -1, 'B');   /* B = beginning */
LastDayPrevMonth  = intnx('month', Date, -1, 'E');   /* E = end */
MiddlePrevMonth   = intnx('month', Date, -1, 'M');   /* M = middle */
FirstAnniv        = intnx('year',  Date,  1, 'S');   /* S = same */
format _ALL_ date9.;
run;
 
proc print data=FirstLastMiddle noobs;
run;

The program shows that you can find the first day of the previous month, the last day of the previous month, the middle of the previous month, or an anniversary of the specified date. In particular, the program answers the programmer's question by showing a concise "one-liner" that you can use to get the first and last days of the previous month.

In summary, the INTNX function is a powerful tool for working with dates. It enables you to find dates that are related to a specified date. You can use the first argument to specify the time unit (day, week, month, year,...) and the third argument to specify the number of time units before or after the specified data. The optional fourth argument determines whether you want the first, last, middle, or "same" portion of the time interval.

Whether you work with dates in SAS every day or whether you work with them occasionally, the INTNX function is a sweet treat to remember. No tricks required.