One of the best ways to understand the INTNX and INTCK functions and how they work is to see some easy examples. Thus, in this article you will find some. (Note: this article originally appeared on sasCommunity.org, written by Victor Popovich.)
If you prefer to learn by watching (while listening to some funky original music), check out this tutorial about SAS Intervals from @derekmor54 (Mr. Dates and Times):
For more information on the INTCK and INTNX functions, see INTCK and INTNX: Two essential functions for computing intervals between dates in SAS, an article by @Rick_SAS.
Difference between INTNX and INTCK functions
These two functions complement each other: INTCK computes the difference between two dates, while INTNX enables you to add time units to a date value.
| INTCK function |
Computes the number of time units between two date (or datetime) values. For the time unit, you can choose years, months, weeks, days, and more. |
| INTNX function |
Computes a SAS date (or datetime) that is a specified number of time units away from a specified date (or datetime) value. |
Trick to remember:
- INTCK - INTerval ChecK
- INTNX - INTerval NeXt
INTNX Function Examples
According to SAS documentation this function Increments a date, time, or datetime value by a given interval or intervals, and returns a date, time, or datetime value.
Example 1. Some simple examples of using INTNX function:
format day week month_ year date9.;
day=intnx('day', '01FEB2010'd, 7); /* +7 days */
week=intnx('week', '01FEB2010'd, 1); /* 01 of Feb 2010 is Monday*/
month_=intnx('month', '01FEB2010'd, 2); /* +2 month */
year=intnx('year', '01FEB2010'd, 1); /* +1 year */
The result values will be:
- day = 08FEB2010 (+7 days)
- week = 07FEB2010 (next Sunday)
- month_ = 01APR2010 (next 1st day of 2nd next month)
- year = 01JAN2011 (next 1st day of next year)
Example 2. Multiplying and shifting intervals.
You may change the first argument (interval) by adding digits as in the example below.
format day week year date9.;
day=intnx('day2', '01FEB2010'd, 2); /* two of 2-days intervals */
week=intnx('week1.3', '01FEB2010'd, 1); /* 01 of Feb 2010 is Sunday. 3rd day of the week is Tuesday */
year=intnx('year1.3', '01FEB2010'd, 1); /* next year | third month | 1st day*/
The result values will be:
- day = 05FEB2010 (+2 days twice)
- week = 02FEB2010 (Next Tuesday)
- year = 01MAR2011 (Next Year's third month (March) 1st day)
You are free to combine intervals by multiplying and shifting it. However, please be careful when contructing complex interval specifications.
Example 3. Datetime and time formats.
The INTNX function may cope with datetime, time and date formats. The above examples are based on date format. The example below uses datetime and time formats.
format seconds minutes hours days weeks datetime20.;
format t_seconds t_minutes t_hours time9.;
seconds=intnx('second', '01FEB2010:00:00:00'dt, 1);
minutes=intnx('minute', '01FEB2010:00:00:00'dt, 1);
hours=intnx('hour', '01FEB2010:00:00:00'dt, 1);
days=intnx('dtDay', '01FEB2010:00:00:00'dt, 1);
weeks=intnx('dtWeek', '01FEB2010:00:00:00'dt, 1);
t_seconds=intnx('second', '00:00:00't, 1);
t_minutes=intnx('minute', '00:00:00't, 1);
t_hours=intnx('hour', '00:00:00't, 1);
The result values will be:
- seconds= 01FEB2010:00:00:01 (next second)
- minutes= 01FEB2010:00:01:00 (next minute)
- hours= 01FEB2010:01:00:00 (next hour)
- days= 02FEB2010:00:00:00 (next day)
- weeks= 07FEB2010:00:00:00 (next Sunday)
- t_seconds = 00:00:01 (next second)
- t_minutes = 00:01:00 (next minute)
- t_hours = 01:00:00 (next hour)
and so on.
Example 4. Alignment within the interval.
format beginning middle end sameday date9.;
beginning=intnx('month', '05FEB2010'd, 1, 'b');
middle=intnx('month', '05FEB2010'd, 1, 'm');
end=intnx('month', '05FEB2010'd, 1, 'e');
sameday=intnx('month', '05FEB2010'd, 1, 's');
The result values will be:
- beginning = 01MAR2010 (1st day of the next month)
- middle= 16MAR2010 (middle day (16 of 31) of the next month)
- end= 31MAR2010 (last day (31) of the next month)
- sameday = 05MAR2010 (the same day (5) of the next month)
You might find it helpful to use the INTCK function along with the INTNX.
INTCK Function Examples
Everyone knows that the INTCK function returns the integer count of the number of interval boundaries between two dates, two times, or two datetime values. In the following Example1 you see how this function works within some basic intervals:
Example 1. Date arguments.
years=intck('year','01jan2009'd,'01jan2010'd);
SEMIYEAR=intck('SEMIYEAR','01jan2009'd,'01jan2010'd);
quarters=intck('qtr','01jan2009'd,'01jan2010'd);
months=intck('month','01jan2009'd,'01jan2010'd);
weeks=intck('week','01jan2009'd,'01jan2010'd);
days=intck('day','01jan2009'd,'01jan2010'd);
In the example above we count the difference between two dates: 01-Jan-2009 and 01-Jan-2010 (one year). The result values will be:
- years = 1
- semiyears = 2
- quarters= 4
- months = 12
- weeks = 52
- days = 365
Example 2. Datetime arguments.
hours=intck('hour','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt);
minutes=intck('minute','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt);
seconds=intck('second','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt);
The result values will be:
- hours= 8760
- minutes= 525600
- seconds= 31536000
Example 3. Time arguments.
hours=intck('hour','00:00:00't,'12:00:00't);
minutes=intck('minute','00:00:00't,'12:00:00't);
seconds=intck('second','00:00:00't,'12:00:00't);
In the example3 (above) we count the difference between two time values: 00:00:00 (midnight) and 12:00:00 (midday). The result values will be:
- hours= 12
- minutes= 720
- seconds= 43200
Important notice! The INTCK functions does not count the number of complete intervals between two values. The example below shows how it calculates the 'YEAR' difference between 31-Dec and 1-Jan:
Example 4. Boundaries.
years=intck('year','31dec2009'd,'01jan2010'd);
The result values will be:
Since you know that it is just ONE DAY and it is not a YEAR the results could be mistakenly considered as incorrect. Please be aware that it returns the integer count of the number of interval boundaries between two dates. In the example above the boundary is the value of '01JAN2010'd that is included (just once) into the interval.
To solve for this use the 'CONTINUOUS' method (shortened to 'C'):
wholeyears=intck('year','31dec2009'd,'01jan2010'd,'C');
You can use a combination of the 'CONTINUOUS' option of the INTCK function and the 'SAME' option of the INTNX function to count time the way we count chronological age.
data a(keep=start end wholeyears wholemonths wholedays);
start = '31dec2009'd;
end = '01jan2010'd;
wholeyears=intck('year','31dec2009'd,'01jan2010'd,'C');
start2 = INTNX('year',start,wholeyears,'S');
wholemonths=INTCK('month',start2,end,'C');
start3 = INTNX('month',start2,wholemonths,'S');
wholedays=INTCK('day',start3,end,'C');
format start end DATE.;
run;
proc print;run;
The result will be:
Obs start end wholeyears wholemonths wholedays
1 31DEC09 01JAN10 0 0 1
Some operations with changing formats are also helpful when you deal with the INTCK function. Please take a look at the next examples. I believe it is quite easy to understand how it works.
Example 5. Datepart() Timepart() functions.
format a1 b1 date9.;
a0='01jan2009:00:00:00'dt;
b0='01jan2010:00:00:00'dt;
a1=datepart(a0);
b1=datepart(b0);
days=intck('day',a1,b1);
The result value will be:
format a1 b1 date9.;
a0='01jan2009:00:00:00'dt;
b0='01jan2010:12:00:00'dt;
a1=timepart(a0);
b1=timepart(b0);
hour=intck('hour',a1,b1);
The result value will be:
Thanks to Victor Popovich for providing this help on sascommunity.org!
