Date Interval Functions – INTNX and INTCK in SAS
One trick to remember the working of these two functions is:
INTCK – INTerval CheCK
INTNX – INTerval NeXt
The INTNX Function
INTNX(interval <multiple><.shift-index>, start-from, increment <, 'alignment'>)
Interval is an interval name. Interval can appear in uppercase or lowercase. The possible values of the interval are listed in the below table.
The type of interval (date, DateTime, or time) must match the type of value in start-date.
You can use any SAS date format to convert the numeric SAS date.
Example 1: Simple Examples of INTNX functions
data _null_; day=intnx('day', '01AUG2020'd, 7); week=intnx('week', '01AUG2020'd, 1); tenday=intnx('tenday', '01AUG2020'd, 2); weekday = intnx('weekday', '01AUG2020'd, 2); month_=intnx('month', '01AUG2020'd, 2); semimonth=intnx('semimonth', '01AUG2020'd, 3); qtr=intnx('qtr', '01AUG2020'd, 2); semiyear= intnx('semiyear', '01AUG2020'd, 3); year=intnx('year', '01AUG2020'd, 1); format day week month_ year date9.; put _all_; run;
day=08AUG2020 week=02AUG2020 tenday=21AUG2020 weekday=04AUG2020 month_=01OCT2020 semimonth=16SEP2020 qtr=01JAN2021 semiyear=01JAN2022 year=01JAN2021
The result are:
- day = 08FEB2010 (+7 days from 01AUG2020 is 08AUG2020)
- week = 07FEB2010 ( 01AUG2020 is Saturday so +1 week returns 02AUG2020 which is Sunday)
- tenday = 21AUG2020 (2 times 10 day interval which +20 days from 1st AUG2020)
- weekday = 04AUG2020 (+2 days excluding saturdays and sundays)
- month_ = 01APR2010 (+2 month returns 01OCT2020)
- semimonth = 01SEP2020 (+3 semi months, 1 semi months is from 1st to 15th of a month.)
- qtr = 01JAN2021 (+2 Quarter)
- semiyear = 01JAN2022 (+3 semi years. Jan-Jul is 1 semi year)
- year = 01JAN2011 (+1 year)
Example 2: How to determine the date that is 6 weeks from the week for August,1,2020
data _NULL_; date=intnx('week', '05AUG2020'd, 6); put date date9.; run;
The date returned is the starting date of the week that is 6 weeks from August,5,2020.
In addition to these three arguments, an optional fourth argument can be included. This allows greater control over the exact date returned; the values for the fourth argument are b, e, s and m and must also be enclosed in quotation marks. The effect of adding these values as the fourth argument is listed below:
- b – The date of the beginning of the interval is returned (first day of the week/ month/year). This is also the default if nothing is entered.
- e– The date of end of the interval is returned (last day of the week/ month/year).
- m– The date of the middle of the interval (middle day of the week/ month/year).
- s – The date of the same day within the interval is returned (same day of the week/ month/year).
By default, both INTNX and INTCK functions use the number of “calendar boundaries” between the dates, such as the first day of a year, month, or week.
For example, if you choose to measure year intervals, the INTCK function counts how many times 01JAN occurred between the dates, and the INTNX function returns a future 01JAN date. Similarly, if you measure month intervals, the INTCK function counts how many first-of-the-months occur between two dates, and the INTNX function returns a future first-of-the-month date.
Example 3: Alignment within the interval.
date=intnx('year', '05AUG2020'd, 5); date_b=intnx('year', '05AUG2020'd, 6,'b'); date_e=intnx('year', '05AUG2020'd, 6,'e'); date_m=intnx('year', '05AUG2020'd, 6,'m'); date_s=intnx('year', '05AUG2020'd, 6,'s'); format date date_b date_e date_m date_s date9.; put date = ; put date_b =; put date_e =; put date_m =; put date_s =; run;
date = 01JAN2025 date_b = 01JAN2026 date_e = 31DEC2026 date_m = 02JUL2026 date_s = 05AUG2026
INTCK in SAS
The INTCK function returns the number of interval boundaries between two dates, times, or DateTime values.
INTCK(interval <multiple> <.shift-index>, start-date, end-date, <'method'>)
- Interval – It is the interval name and values of the interval can be any of the Date and Time Intervals listed in Table 1 above.
- Multipliers and shift indexes – These options can be used with the basic interval to construct more complex interval specifications.
Example 4: Find the number of days remaining until Christmas
data _null_; days=intck('day',today(),'24dec2017'd); put days; run;
What if you would want to have the end date dynamically take the Christmas date every year?
You can combine holiday function and the year function with the INTCK to dynamically return the number of days until Christmas every year.
data _null_; days=intck('day',today(),holiday('christmas',year(today()))); put days; run;
Example 5: Find the number of days to Christmas starting from next weekend.
data _null_; next_weekend= intnx('week.7',today(),1); put next_weekend date9.; days=intck('days',next_weekend,holiday('christmas',year(today()))); put days; run;
This example uses INTCK, INTNX, year and today functions. First, INTNX is used to find the date of next weekend then the INTNX and holiday functions are used to calculate the number of days. Today function is used to dynamically return the current year. You can combine the above program into a single line of code as below.
Creating your own Intervals Using Multipliers and Shifting Intervals
To create complex intervals, you can use multipliers and shift indexes. For example, if you want your year to start on July 1 instead of January 1 (as in a financial year) you can do that using shift intervals.
“YEAR.7” moves the start of the YEAR interval to the seventh month which July 1st instead of January
The syntax follows below.
The three parts of the interval name are listed below:
- Interval – It specifies the name of the basic interval type. For example, YEAR specifies yearly intervals. Refer to the Commonly Used Time Intervals.
- Multiple – specifies an optional multiplier that sets the interval equal to a multiple of the period of the basic interval type. For example, the interval YEAR2 consists of two-year, or biennial, periods.
- Shift index – It is used to shifts the interval to start at a specified subperiod starting point. For example, YEAR.7 specifies yearly periods shifted to start on the first of July of each calendar year and to end in June of the following year.
Note that the shift index cannot be greater than the number of subperiods in the entire interval. For example, you could use
YEAR2.25 would be an error because there is no 25th month in a two-year interval.
multiplier and the shift–index arguments are optional and default to 1.
For example, YEAR, YEAR1, YEAR.1, and YEAR1.1 are all represented as ordinary calendar years that begin in January.
To create a 100 Year interval or a century, you can use YEAR100 as the interval.
Example 6: Multiplying and shifting intervals.
data _null_; day=intnx('day2', '01AUG2020'd, 2); week=intnx('week1.3', '01AUG2020'd, 1); biweekly= intnx('week2.2', '01AUG2020'd, 1); monthly=intnx('month2.2', '01AUG2020'd, 1); year=intnx('year1.3', '01AUG2020'd, 1); format day week biweekly monthly year date9.; put _all_; run;
day=05AUG2020 week=04AUG2020 biweekly=03AUG2020 monthly=01OCT2020 year=01MAR2021
The result values will be:
- day = 05AUG2020 (+2 days interval twice)
- week = 04AUG2020 (Next Tuesday)
- biweekly – 03AUG2020 (Biweekly intervals starting on first Monday
- monthly – 01OCT2020 (+2 months starting from February till January of the following year
- year = 01MAR2021 (Next Year’s third month (March) 1st day)
Time intervals that do not nest within years or days are aligned relative to the SAS date or DateTime value 0. SAS uses the arbitrary reference time of midnight on January 1, 1960, as the origin for non-shifted intervals. Shifted intervals are defined relative to January 1, 1960.
For example, MONTH13 defines the intervals January 1, 1960, February 1, 1961, March 1, 1962, and so on, and the intervals December 1, 1958, November 1, 1957, and so on, before the base date January 1, 1960.
As another example, the interval specification WEEK6.13 defines six-week periods starting on second Fridays. The convention of alignment relative to the period that contains January 1, 1960, determines where to start counting to determine which dates correspond to the second Fridays of six-week intervals.
CONTINUOUS and DISCRETE
Both functions support many options to modify the default behaviour. For example, to count full-year intervals, instead of the number of times people celebrated Christmas, these function has options to count the number of “anniversaries” between two dates and to compute the date of a future anniversary.
You can use the ‘CONTINUOUS’ option for the INTCK function and the ‘SAME’ option for the INTNX function, as follows:
The ‘CONTINUOUS’ option in the INTCK function enables you to count the number of anniversaries of one date that occur prior to a second date. For example, the statement
Years_c = intck('year', '31DEC2020'd, '01JAN2021'd, 'CONTINUOUS'); Years_d = intck('year', '31DEC2020'd, '01JAN2021'd, 'd');
The first statement returns 0 because there are no full years between two dates. The discrete options it returns 1 because 01JAN occurs 1 time between the two dates.
Leap years are handled automatically in these functions. If you want to find the number of days between two dates, the INTCK function includes leap days in the output.