Last week I showed a timeline of living US presidents. The number of living presidents is constant during the time interval between inaugurations and deaths of presidents. The data was taken from a Wikipedia table (shown below) that shows the number of years and days between events. This article shows how you can use the INTCK and INTNX functions in SAS to compute the time between events in this format. In particular, I use two little-known options to these functions that make this task easy.
Intervals between dates
If you are computing the interval between two dates (a start date and an end date) there are two SAS functions that you absolutely must know about.
- The INTCK function returns the number of time units between two dates. For the time unit, you can choose years, months, weeks, days, and more. For example, in my previous article I used the INTCK function to determine the number of days between two dates.
- The INTNX function returns a SAS date that is a specified number of time units away from a specified date. For example, you can use the INTNX function to compute the date that is 308 days in the future from a given date.
These two functions complement each other: one computes the difference between two dates, the other enables you to add time units to a date value.
By default, these 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.
Options to compute anniversary dates
Both functions support many options to modify the default behavior. If you want to count full year intervals, instead of the number of times people celebrated New Year's Eve, these function support options (as of SAS 9.2) 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 = intck('year', '30APR1789'd, '04MAR1797'd, 'continuous');
returns the value 7 because there are 7 full years (anniversaries of 30APR) between those two dates. Without the 'CONTINUOUS' option, the function returns 8 because 01JAN occurs 8 times between those dates. - The statement
Anniv = intnx('year', '30APR1789'd, 7, 'same');
returns the 7th anniversary of the date 30APR1789. In other words, it returns the date value for 30APR1796.
The beauty of these functions is that they automatically handle leap years! If you request the number of days between two dates, the INTCK function includes leap days in the result. If an event occurs on a leap day, and you ask the INTNX function for the next anniversary of that event, you will get 28FEB of the next year, which is the most common convention for handling anniversaries of a leap day.
An algorithm to compute years and days between events
The following algorithm computes the number of years and days between dates in SAS:
- Use the INTCK function with the 'CONTINUOUS' option to compute the number of complete years between the two dates.
- Use the INTNX function to find a third date (the anniversary date) which is the same month and day as the start date, but occurs less than one year prior to the end date. (The anniversary of a leap days is either 28FEB or 29FEB, depending on whether the anniversary occurs in a leap year.)
- Use the INTCK function to compute the number of days between the anniversary date and the end date.
The following DATA step computes the time interval in years and days between the first few US presidential inaugurations and deaths. The resulting Year and Day variables contain the same information as is displayed in the Wikipedia table.
data YearDays; format Date prevDate anniv Date9.; input @1 Date anydtdte12. @13 Event $26.; prevDate = lag(Date); if _N_=1 then do; /* when _N_=1, lag(Date)=. */ Years=.; Days=.; return; /* set years & days, go to next obs */ end; Years = intck('year', prevDate, Date, 'continuous'); /* num complete years */ Anniv = intnx('year', prevDate, Years, 'same'); /* most recent anniv */ Days = intck('day', anniv, Date); /* days since anniv */ datalines; Apr 30, 1789 Washington Inaug Mar 4, 1797 J Adams Inaug Dec 14, 1799 Washington Death Mar 4, 1801 Jefferson Inaug Mar 4, 1809 Madison Inaug Mar 4, 1817 Monroe Inaug Mar 4, 1825 JQ Adams Inaug Jul 4, 1826 Jefferson Death Jul 4, 1826 J Adams Death run; proc print data=YearDays; var Event prevDate Date Anniv Years Days; run; |
Summary and references
In summary, the INTCK and INTNX functions are essential for computing intervals between dates. In this article, I emphasized two little-known options: the 'CONTINUOUS' option in INTCK and the 'SAME' option in INTNX. By using these options, you can to compute the number of anniversaries between dates and the most recent anniversary. Thus you can compute the years and days between two dates.
There have been countless articles and papers written about SAS dates and finding intervals between dates. I recommend the following articles:
- A brief introduction to SAS date and time functions is Andrew Karp (2003) "Working with SAS Date and Time Functions." Unfortunately, this paper was written before the 'CONTINUOUS' and 'SAME' options were added.
- I learned about the ‘SAME’ from a short paper by Bruce Gilsen (2006) "Improve Your Dating: The INTNX Function Alignment Value SAMEDAY."
- A more advanced paper with many examples, including examples of the 'CONTINUOUS' and 'SAME' options, is Derek Morgan (2015) "Demystifying Date and Time Intervals." Derek also wrote the book The Essential Guide to SAS Dates and Times (Second Edition, 2014)
Lastly, do you know what the acronyms INTCK and INTNX stand for? Obviously the 'INT' part refers to INTervals. The general consensus is that 'INTCK' stands for 'Interval Check' and 'INTNX' stands for "Interval Next."
WANT MORE GREAT INSIGHTS MONTHLY? | SUBSCRIBE TO THE SAS TECH REPORT
28 Comments
LOVE LOVE LOVE the INTCK and INTNX functions! I've found them extremely useful for financial year calculations, specifying the shift-index option to shift the start of the calendar year to the fiscal year http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p0syn64amroombn14vrdzksh459w.htm#n1wbckrlffgb8jn15eebgjpqm23c And as a reminder if you are wanting to calculate an age, then the YRDIF function with the "AGE" option is good to use as outlined in Chris Hemedingers post, http://blogs.sas.com/content/sasdummy/2011/07/12/computing-age-in-sas-9-3/
And with regards to what CK and NX stand for... I've always thought what you've suggested Check and Next. Curious to know if it is something else.
I share your affection. And thanks for mentioning YRDIF. You can also use YRDIF to solve this problem, but you need to use extra logic to handle leap years, whereas INTCK handles them automatically.
From the documentation, it seems that using the "AGE" option in YRDIF accounts for leap years http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1pmmr2dtec32an1vbsqmm3abil5.htm However, as discussed in Chris' blog post comments it depends on the type of question being asked.
Yes, the INTEGER portion of YRDIF is fine. The special handling becomes necessary when you want to work with the FRACTIONAL portion to find the next anniversary date. Try it, you'll see.
Hi Rick,
If I wanted to count the number of rows between intervals of weeks starting from a point in time until today, what would be the best method of doing so? I've been looking all over the internet, but can only seem to find articles on the number of units between days, weeks, months, etc.
For examples, if I wanted to count the number of bananas purchased in fortnightly intervals from six months ago and draw a table of this, what would be the best way to go about it?
If you could help me out with trying to resolve this issue, it'd would be much appreciated!
This sort of question gets asked and answered frequently on the SAS Support Communities. Search that site or post your own question along with some sample data.
This is the first time I have heard what INTCK and INTNX stand for. Of course, I have guessed, but it seems that you guessed also. Because our guesses agree, it must be so!
Hi,
Is it possible to use intck with a 'user calendar', a dataset calendar in which you have a flag field that says if is working day or nor for every date??
Thanks
Juan,
Yes. You can create your own calendar. In this code, I create a custom interval of working days. These working days just so happen to coincide with the weekday interval WEEKDAY167W. This describes a week where business in not conducted on Sunday(1), Friday(6), or Saturday(7). I did this for testing purposes. If this was your actual calendar, then you should use WEEKDAY167W. However, custom intervals can accommodate any calendar, no matter how irregular. Using the custom interval 'MyBusDay', INTNX is able to calculate 3 business days into the future.
data mybusinessdays;
do BEGIN = '01JAN2016'D to '31DEC2020'D;
if ( WEEKDAY(BEGIN) GE 2 and WEEKDAY(BEGIN) LE 5 ) then output;
end;
format BEGIN DATE.;
run;
options intervalds=(MyBusDay=mybusinessdays);
data ship;
do date = '01FEB2017'D to '28FEB2017'D;
ship_date1 = INTNX('MyBusDay',date,3);
ship_date2 = INTNX('WEEKDAY167W',date,3);
output;
end;
format date ship_date1 ship_date2 DATE.;
run;
title 'Shipping Days for 3 day shipping';
proc print;run;
title 'Error Check';
proc print data=ship(where=(ship_date1 NE ship_date2);
run;
Thanks for your help Tammy, very useful
hi guys,
i have an xls file, imported by sas successfully, i have formatted the dates and also was successful, however i tryed many ways to calculate number of days between the two dates given and was not successful ..it gives sometimes missing values to the dates...i need to compute days between two dates..my program is as follows
PROC IMPORT OUT= WORK.DATE123
DATAFILE= "C:\Users\Administrator\Desktop\IFAD\DATAANALYSES\
datenew2.xls" out= dayno
DBMS=EXCEL5 REPLACE;
GETNAMES=YES;
RUN;
looking for help...
proc print data =dayno;
format mdy1 mdy2 date9.;
run;
You can post questions like this to the SAS Support Community. The site enables you to post code, images, attachments, and more.
the output file of sas is as follows:
260 FAY 3 5 18 09AUG2000 08AUG2001
261 FAY 3 5 16.1 02FEB2001 01FEB2002
262 FAY 3 5 17.9 09DEC2000 08DEC2001
263 FAY 3 5 16.8 30DEC2000 .
264 FAY 3 5 16.3 09AUG2000 08AUG2001
265 FAY 3 5 18.5 02FEB2001 01FEB2002
266 FAY 3 5 16.4 09DEC2000 08DEC2001
267 FAY 3 5 15.8 02FEB2001 01FEB2002
268 FAY 3 5 17.5 09DEC2000 08DEC2001
269 FAY 3 5 17 30DEC2000 29DEC2001
I am confident that the experts on the SAS Support Community will be able to give you an answer quickly. Good luck!
I have a question, if my dataset contains only year variable (like manufacturing year) and I want to calculate the difference between manufacturing year and a year in which the data was calculated, then how should I go about it. is there any function I can apply for the same,
If only the year is known (not a day within the year), then just subtract the years:
Diff = ManufactYear - DataYear;
If you have day information, use INTCK.
can anyone suggest code of SAS for Balaam Design to get 90% Confidence interval for Test vs reference and Ref Vs Reference and Test vs Test
Thanks in advance..
You can ask SAS programming questions at communities.sas.com.
Can someone help on this,
I would like to create week number starting Dec 30 to Jan 5 as week#1, Jan 6 to 12 as week#2 and so on.... for my variable called 'admit_date',
What SAS code should I use?
You can ask SAS programming questions on the SAS Support Communities. The experts might suggest that you look at the WEEK function.
I need help please,
I have multiple dates in my data.
My first query is selecting the MAX date for each person/account, the next query should jump one month back and the next 3 month back and so on.
How I can define the number of months to jump?
For example, this is my first query:
proc sql;
select count(ACCT_ID) as Total_count, SEG, TM_ID, PRD_CD
from CUST_DATA
where OBSVTN_DT=(select max(OBSVTN_DT) from CUST_DATA)
group by TM_ID, SEG, PRD_CD
order by PRD_CD, SEG;
quit;
You can ask SAS programming questions, post data, and share code on the SAS Support Communities. There are many experts there who can help with questions like this one.
This is very helpful. Thanks.
I have a question though. I am trying to use the INTNX function to get the first and last days of the previous month. How best can this be written? I need to use this in a where clause on DI Studio.
The SQL code is "where fac.VALID_FROM_DTTM between DATEADD(m,-1,DATEADD(m, DATEDIFF(m,0,GETDATE()),0)) and EOMONTH(dateadd(month,-1,getdate()))"
The first day of the current month is
FirstDayMonth = intnx('month', Date, 0);
Therefore, the last day of the previous month is
LastDayPrevMonth = intnx('month', Date, 0) - 1;
Similarly, the first day of the previous month is
FirstDayPrevMonth = intnx('month', Date, -1);
Pingback: Compute the first or last day of a month or year - The DO Loop
Thank you blogs.sas for giving me wonderful information
Pingback: Computing a date from the past (or future) with SAS - The SAS Dummy
Pingback: How to use FIRST.variable and LAST.variable in a BY-group analysis in SAS - The DO Loop