Date Examples With INTNX and INTCK Function
Working with dates in SAS is inevitable. It is very important to have a thorough understanding of how SAS interprets date values and how you should definitely not interpret them. In this post, I present the very basics and introduce two of the most important functions, the INTNX and INTCK functions, along with a few examples.
Dates are Numbers!
The first thing you must realize about dates in SAS is that they are merely formatted numbers. January 1st, 1960 is the base of dates in SAS. This means that applying eg the date9 format to the value zero will give you 01jan1960. Applying it to the value 1 gives you 02jan1960 and so forth. This small example shows you the numeric values -2, -1, 0, 1 and 2 and their corresponding formatted values.
data dates; do dt = -2 to 2; dt_formatted = dt; output; end; format dt_Formatted date9.; run;
To the right, you can see the actual date value and the exact same value with a format. The variables dt and formatted_dt represent the exact same numerical value. The only difference between them is that formatted_dt has a format over it. For a comprehensive list of date formats, I usually refer to the old SAS documentation here.
Two Important Functions
Since dates are merely numbers in SAS, you can perform arithmetic operations with them. For example, the number 21044 represents the date 13/08/2017. That means we can calculate the following date by simply adding 1 to 21044. Even though this is intuitive, I recommend that you familiarize yourself with the INTNX and the INTCK functions. In my opinion, these are the single two most important functions to perform date calculations in SAS.
INTNX function Examples
The INTNX increments or decrements and aligns date values by specified intervals. The syntax of the function is INTNX(interval, from, n, alignment). Thus, the function returns the date n intervals from the from the argument (which is a date value). Alignment is an optional argument, aligning the date value to the beginning, end, same day or middle of the interval. The INTNX function is best explained by examples. Here are three common requests, that the INTNX answers neatly:
I want to know the date exactly 21 days from December 24th, 2017
data _null_; date=intnx('day', '24dec2017'd, 21); format date date9.; put date; run;
The results in the log is 14jan2018.
I want to know the date of the first day of the month 3 months from today
data _null_; dt=intnx('month', today(), 3, 'b'); format dt date9.; put dt; run;
This example is a bit more complicated since it involves aligning. We add three months to today’s value and aligns it with the beginning of the interval (month) by specifying ‘b’ for beginning. The result in the log is 01nov2017.
I want to know the date of the first Thursday of next month
data _null_; dt=intnx('week.5', intnx('month',today(),0,'e'), 1, 'b'); format dt date9.; put dt; run;
Again, a bit more complicated before. Because here, we use a nested INTNX function. The inner function finds the last day of the current month by aligning the current month to the end with the ‘e’ alignment option. The outer function finds the following Thursday from the month returned from the inner function by aligning the following week5.5 interval (a week interval beginning Thursday) to the beginning using the ‘b’ option. The result is 07sep2017.
INTCK function Examples
The INTCK function returns the number of intervals between two date values. The syntax is very similar to the INTNX function, INTCK(interval, from, increment, alignment). Again, it is best described with a few examples.
I want the number of days until Christmas Eve
data _null_; days=intck('day',today(),'24dec2017'd); put days; run;
I want the number of days between next Tuesday and New Years Eve
data _null_; days=intck('day', intnx('week.3', today(), 1, 'b'), '31dec2017'd); put days; run;
This example makes use of the INTNX embedded inside the INTCK function. First, we find the date of next Tuesday. Then we calculate the number of days from that date until the 31st of December.
For more examples of the INTNX and INTCK functions, consult the INTNX and INTCK documentation and the example pages on the old SAScommunity.org on INTNX and INTCK. Also, I have written more examples in the Examples section here.
You can download the entire code to this post here.