Wednesday 24 October 2012

NOTE: Date Increments (INTNX and INTCK)

If you're an experienced SAS practitioner you'll be familiar with the INTNX (and INTCK) function. INTNX takes date, time, and datetime values and increments (or decrements) them by a specified interval, e.g. hours, weeks or quarters. If you're not familiar with the function, I'll give a quick introduction; if you've been using INTNX for years, I'll highlight the SAMEDAY alignment option introduced in SAS V9. V9 was introduced some time ago, but if you were already familiar with INTNX then you may have over-looked the new SAMEDAY option.

By way of a basic introduction, let's assume we have a variable named START that contains a date value, and we want to add three months to it. If we knew the number of days in three months we could do a simple mathematical addition, but the number of days in any three month period can vary. However, help is at hand with INTNX. The following illustrates the solution.

data demo;
  format start end date11.;
  start = '1jun2012'd;
  end = INTNX('MONTH',start,3);
  put start= end=
run;

START=01-JUN-2012 END=01-SEP-2012

As you can see, the date has been incremented by three months. The first parameter of the INTNX function specifies the type of interval, and the third specifies how many intervals (negative values are permissible and result in the value being decremented). The SAS 9.3 Functions and CALL Routines: Reference lists the valid values for the interval; there are many.

There is one feature you need to be aware of. The value will be incremented (or decremented) to align with the beginning of the interval, so INTNX('MONTH', '10jun2012'd, 3) would also result in '1sep2012'd, not the 10th.

There's a fourth parameter of the INTNX function that allows you to specify the alignment as BEGINNING (the default), MIDDLE, and END.

So far, so good, but (perhaps unknown to experienced SAS programmers) V9 introduced a fourth alignment value: SAME.

Armed with this knowledge, we can increment 10th June and get a result of 10th September: INTNX('MONTH', '10jun2012'd, 3, 'SAME').

And finally, INTNX has two optional adjuncts to the interval. Firstly, the interval can be suffixed with a number to indicate larger intervals, e.g. MONTH2 indicates that intervals that are two months in length should be used. Secondly, another numeric value can follow a dot and indicates an offset starting point, e.g. YEAR.3 specifies yearly periods shifted to start on the first of March of each year, and YEAR2.6 indicates that the intervals are each two years in length and that they start on the sixth month. There's more detail on these optional parameters in the aforementioned SAS 9.3 Functions and CALL Routines: Reference manual

Anf finally, finally, I mentioned INTCK earlier. The INTCK function calculates the number of intervals between two specified date, time or datetime values. It uses the same intervals and general style of syntax as INTNX. If you use dates, times, or datetimes, then you need to be good friends with INTNX and INTCK!