andersen audit firm SAS : INTNX Function with Examples – ListenData

  • Previous Page
  • |
  • Next Page

INTNX Function

Increments a date, time, or datetime value by a
given time interval, and returns a date, time, or datetime value.

Category:Date and Time

Syntax

INTNX(custom-interval, start-from, increment <,
alignment‘>
)

Required Arguments

interval

specifies a character
constant, variable, or expression that contains a time interval such
as WEEK, SEMIYEAR, QTR, or HOUR. Interval can
appear in uppercase or lowercase. The possible values of interval are
listed in the “Intervals Used with Date and Time Functions”
table in SAS Language Reference: Concepts.

Tip
The type of interval (date,
datetime, or time) must match the type of value in start-from.

Multipliers and shift
indexes can be used with the basic interval names to construct more
complex interval specifications. The general form of an interval name
is as follows:

interval<multiple.shift-index>

The three parts of
the interval name are listed below:

interval

specifies the name
of the basic interval type. For example, YEAR specifies yearly 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.

SeeIncrementing Dates and Times by Using Multipliers and by Shifting Intervals for more information.

shift-index

specifies an optional
shift index that shifts the interval to start at a specified subperiod
starting point. For example, YEAR.3 specifies yearly periods shifted
to start on the first of March of each calendar year and to end in
February of the following year.

RestrictionsThe shift index cannot be greater than the number of
subperiods in the whole interval. For example, you could use YEAR2.24,
but YEAR2.25 would be an error because there is no 25th month in a
two-year interval.

If the default shift period is the same as the interval
type, then only multiperiod intervals can be shifted with the optional
shift index. For example, MONTH type intervals shift by MONTH subperiods
by default. Thus, monthly intervals cannot be shifted with the shift
index. However, bimonthly intervals can be shifted with the shift
index because there are two MONTH intervals in each MONTH2 interval.
The interval name MONTH2.2, for example, specifies bimonthly periods
starting on the first day of even-numbered months.

SeeIncrementing Dates and Times by Using Multipliers and by Shifting Intervals for more information.

start-from

specifies a SAS expression
that represents a SAS date, time, or datetime value that identifies
a starting point.

increment

specifies a negative,
positive, or zero integer that represents the number of date, time,
or datetime intervals. Increment is
the number of intervals to shift the value of start-from.

Optional Arguments

alignment

controls the position
of SAS dates within the interval. You must enclose alignment in
quotation marks. Alignment can
be one of these values:

BEGINNING

specifies that the
returned date or datetime value is aligned to the beginning of the
interval.

AliasB

MIDDLE

specifies that the
returned date or datetime value is aligned to the midpoint of the
interval, which is the average of the beginning and ending alignment
values.

AliasM

END

specifies that the
returned date or datetime value is aligned to the end of the interval.

AliasE

SAME

specifies that the
date that is returned has the same alignment as the input date.

AliasesS
SAMEDAY
SeeSAME Alignment for more information.

DefaultBEGINNING
SeeAligning SAS Date Output within Its Intervals for more information.

custom-interval

specifies an interval
that you define.

Details

The Basics

The INTNX function increments
a date, time, or datetime value by intervals such as DAY, WEEK, QTR,
and MINUTE, or a custom interval that you define. The increment is
based on a starting date, time, or datetime value, and on the number
of time intervals that you specify.

The INTNX function returns
the SAS date value for the beginning date, time, or datetime value
of the interval that you specify in the start–from argument.
(To convert the SAS date value to a calendar date, use any valid SAS
date format, such as the DATE9. format.) The following example shows
how to determine the date of the start of the week that is six weeks
from the week of October 17, 2003.
x=intnx(‘week’, ’17oct03’d, 6);
put x date9.;

INTNX returns the value
23NOV2003.

For more information
about working with date and time intervals, see Date and Time Intervals.

Date and Datetime Intervals

The intervals that you
need to use with SAS datetime values are SAS datetime intervals. Datetime
intervals are formed by adding the prefix “DT” to any
date interval. For example, MONTH is a SAS date interval, and DTMONTH
is a SAS datetime interval. Similarly, YEAR is a SAS date interval,
and DTYEAR is a SAS datetime interval.

To ensure correct results
with interval functions, use date intervals with date values and datetime
intervals with datetime values. SAS does not return an error message
if you use a date value with a datetime interval, but the results
are incorrect:

data _null_;

date1=intnx(‘dtday’,’01aug11:00:10:48’dt,1);

date2=intnx(‘dtday’,’01aug11’d,1);
date3=intnx(‘dtday’,’01aug11:00:10:48’d,1);
put ‘Correct Datetime Value ‘ date1= datetime19. /
‘Incorrect Datetime Value ‘ date2= datetime19. /
‘Incorrect Datetime Value ‘ date3= datetime19.;
run;

SAS writes the following
output to the log:

Correct Datetime Value date1=02AUG2011:00:00:00
Incorrect Datetime Value date2=02JAN1960:00:00:00
Incorrect Datetime Value date3=02JAN1960:00:00:00

Aligning SAS Date Output within Its Intervals

SAS date values are
typically aligned with the beginning of the time interval that is
specified with the interval argument.

You can use the optional alignment argument
to specify the alignment of the date that is returned. The values
BEGINNING, MIDDLE, or END align the date to the beginning, middle,
or end of the interval, respectively.

SAME Alignment

If you use the SAME
value of the alignment argument,
then INTNX returns the same calendar date after computing the interval
increment that you specified. The same calendar date is aligned based
on the interval’s shift period, not the interval. To view the valid
shift periods, see Intervals Used with Date and Time Functions in SAS Language Reference: Concepts.

Most of the values of
the shift period are equal to their corresponding intervals. The exceptions
are the intervals WEEK, WEEKDAY, QTR, SEMIYEAR, YEAR, and their DT
counterparts. WEEK and WEEKDAY intervals have a shift period of DAYS;
and QTR, SEMIYEAR, and YEAR intervals have a shift period of MONTH.
When you use SAME alignment with YEAR, for example, the result is
same-day alignment based on MONTH, the interval’s shift period. The
result is not aligned to the same day of the YEAR interval. If you
specify a multiple interval, then the default shift interval is based
on the interval, and not on the multiple interval.

When you use SAME alignment
for QTR, SEMIYEAR, and YEAR intervals, the computed date is the same
number of months from the beginning of the interval as the input date.
The day of the month matches as closely as possible. Because not all
months have the same number of days, it is not always possible to
match the day of the month.

For more information
about shift periods, see Intervals Used with Date and Time Functions in SAS Language Reference: Concepts.

Alignment Intervals

Use the SAME value of
the alignment argument if you
want to base the alignment of the computed date on the alignment of
the input date:

intnx(‘week’, ’15mar2000’d, 1, ‘same’); returns 22MAR2000
intnx(‘dtweek’, ’15mar2000:8:45’dt, 1, ‘same’); returns 22MAR00:08:45:00
intnx(‘year’, ’15mar2000’d, 5, ‘same’); returns 15MAR2005

Adjusting Dates

The INTNX function automatically
adjusts for the date if the date in the interval that is incremented
does not exist. For example:
intnx(‘month’, ’15mar2000’d, 5, ‘same’); returns 15AUG2000
intnx(‘year’, ’29feb2000’d, 2, ‘same’); returns 28FEB2002
intnx(‘month’, ’31aug2001’d, 1, ‘same’); returns 30SEP2001
intnx(‘year’, ’01mar1999’d, 1, ‘same’); returns 01MAR2000 (the first day of the
third month of the year)

In the example intnx(‘year’,
’29feb2000’d, 2);
, the INTNX function returns the value
01JAN2002, which is the beginning of the year two years from the starting
date (2000).

In the example intnx(‘year’,
’29feb2000’d, 2, ‘same’);
, the INTNX function returns
the value 28FEB2002. In this case, the starting date begins in the
year 2000, the year is two years later (2002), the month is the same
(February), and the date is the 28th, because that is the closest
date to the 29th in February 2002.

Custom Intervals

A custom interval is
defined by a SAS data set. The data set must contain the begin variable,
and it can also contain the end and season variables.
Each observation represents one interval with the begin variable
containing the start of the interval, and the end variable,
if present, containing the end of the interval. The intervals must
be listed in ascending order. You cannot have gaps between intervals,
and intervals cannot overlap.

The SAS system option
INTERVALDS= is used to define custom intervals and associate interval
data sets with new interval names. The following example shows how
to specify the INTERVALDS= system option:
options intervalds=(interval=libref.dataset-name);

Argument

interval

specifies the name
of an interval. The value of interval is
the data set that is named in libref.dataset-name.

libref.dataset-name

specifies the libref
and data set name of the file that contains user-supplied holidays.

For more information,
see
Custom Time Intervals.

Retail Calendar Intervals

The retail industry
often accounts for its data by dividing the yearly calendar into four
13-week periods, based on one of the following formats: 4-4-5, 4-5-4,
or 5-4-4. The first, second, and third numbers specify the number
of weeks in the first, second, and third month of each period, respectively.
For more information, see Retail Calendar Intervals: ISO 8601 Compliant in SAS Language Reference: Concepts.

Examples

Example 1

The following SAS statements produce these results.

SAS Statement

Result

yr=intnx(‘year’,’05feb94’d,3);
put yr / yr date7.;

13515
01JAN97

x=intnx(‘month’,’05jan95’d,0);
put x / x date7.;

12784
01JAN95

next=intnx(‘semiyear’,’01jan97’d,1);
put next / next date7.;

13696
01JUL97

past=intnx(‘month2’,’01aug96’d,-1);
put past / past date7.;

13270
01MAY96

sm=intnx(‘semimonth2.2’,’01apr97’d,4);
put sm / sm date7.;

13711
16JUL97

x=’month’;
date=’1jun1990’d;
nextmon=intnx(x,date,1);
put nextmon / nextmon date7.;

11139
01JUL90

x1=’month ‘;
x2=trim(x1);
date=’1jun1990’d – 100;
nextmonth=intnx(x2,date,1);
put nextmonth / nextmonth date7.;

11017
01MAR90

The following examples
show the results of advancing a date by using the optional alignment argument.

SAS Statement

Result

date1=intnx(‘month’,’01jan95’d,5,’beginning’);
put date1 / date1 date7.;


12935
01JUN95

date2=intnx(‘month’,’01jan95’d,5,’middle’);
put date2 / date2 date7.;


12949
15JUN95

date3=intnx(‘month’,’01jan95’d,5,’end’);
put date3 / date3 date7.;


12964
30JUN95

date4=intnx(‘month’,’01jan95’d,5,’sameday’);
put date4 / date4 date7.;


12935
01JUN95

date5=intnx(‘month’,’15mar2000’d,5,’same’);
put date5 / date5 date9.;


14837
15AUG2000

interval=’month’;
date=’1sep2001’d;
align=’m’;
date4=intnx(interval,date,2,align);
put date4 / date4 date7.;

15294
15NOV01

x1=’month ‘;
x2=trim(x1);
date=’1sep2001’d + 90;
date5=intnx(x2,date,2,’m’);
put date5 / date5 date7.;

15356
16JAN02

Example 2: Example of Using Custom Intervals

The following example
uses the custom-interval form
of the INTNX function to increment a date, time, or datetime value
by a given time interval.

options intervalds=(weekdaycust=dstest);
data dstest;
format begin end date9.;
begin=’01jan2008’d; end=’01jan2008’d; output;
begin=’02jan2008’d; end=’02jan2008’d; output;
begin=’03jan2008’d; end=’03jan2008’d; output;
begin=’04jan2008’d; end=’06jan2008’d; output;
begin=’07jan2008’d; end=’07jan2008’d; output;
begin=’08jan2008’d; end=’08jan2008’d; output;
begin=’09jan2008’d; end=’09jan2008’d; output;
begin=’10jan2008’d; end=’10jan2008’d; output;
begin=’11jan2008’d; end=’13jan2008’d; output;
begin=’14jan2008’d; end=’14jan2008’d; output;
begin=’15jan2008’d; end=’15jan2008’d; output;
run;

data _null_;
format start date9. endcustom date9.;
start=’01jan2008’d;
do i=0 to 9;
endcustom=intnx(‘weekdaycust’, start, i);
put endcustom;
end;
run;

SAS writes the following
output to the log:
01JAN2008
02JAN2008
03JAN2008
04JAN2008
07JAN2008
08JAN2008
09JAN2008
10JAN2008
11JAN2008
14JAN2008

See Also

System Options:

INTERVALDS= System Option in SAS System Options: Reference

Copyright © SAS Institute Inc. All rights reserved.

  • Previous Page
  • |
  • Next Page
  • |
  • Top of Page








SAS : INTNX Function with Examples

This tutorial explains how SAS INTNX function works. It includes explanation of INTNX function with practical examples which would help you to understand it.
SAS INTNX : Introduction

SAS function INTNX is used to increment SAS date by a specified number of intervals. It helps to answer the following questions.

Examples 
  1. When is next Monday?
  2. When was last Friday?
  3. What would be date after 21 weeks?
  4. Subtract 2 quarters from the current date

SAS INTNX Syntax
The first three parameters of the INTNX function is mandatory and the fourth one is optional.

INTNX(interval, start-from, increment,  [alignment])

  1. Interval is the unit of measurement. The intervals can be days, weeks, months, quarters, years.
  2. Start-from is a SAS date value which would be incremented.
  3. Increment is number of intervals by which date is incremented. It can be zero, positive or negative. Negative value refers to previous dates.
  4. Alignment [Optional Parameter] is where datevalue is aligned within interval prior to being incremented. The values you can specify – ‘beginning’, ‘middle’, ‘end’, ‘sameday’. Default value – ‘beginning’.
INTNX : Examples
1. Add 7 days to a specific date
In the following code, we are adding seven days to 02 January 2017.

data temp;
mydate = ’02JAN2017’d;
day=intnx(‘day’, mydate , 7);
format mydate day date9.;
run;



Result : day = 09JAN2017 

SAS INTNX
If you are wondering how INTNX is different to ‘simply adding 7 to mydate variable’ like code below. You would get answer to this question in the next example.

day = mydate + 7;



2. Find Next Sunday

In this case, we need to find answer of the question ‘when is next sunday?’. The 02January,2017 is Monday.

data temp;
mydate = ’02JAN2017’d;
nextsunday=intnx(‘week’, mydate , 1);
format mydate nextsunday date9.;
run;

Result : nextsunday = 08JAN2017
It returns 08JAN2017 as it aligns to the ‘beginning’ period. The ‘beginning’ alignment is default in INTNX function. In other words, if you change the mydate to ’04JAN2017’d, it still returns ’08JAN2017′ as the next sunday would be same within this week interval. 
If you want to add exactly 1 week to the date, you can use the ‘sameday’ in the fourth parameter of this function. See the statement below –

nextsunday=intnx(‘week’, mydate , 1, ‘sameday‘); returns 09JAN2017

3. Get First Date

Suppose you need to find out the first day of a specific day. For example, today is 09January, 2017 and the first day of this date is 01January,2017.

data temp;
set sashelp.citiday;
firstday=intnx(‘month’, date , 0);
format firstday date9.;
proc print data = temp;
var date firstday;
run;

SAS : Get First Day

By specifying 0 in the third parameter of INTNX function, we can calculate the first day of the dates.


4. When was Last Tuesday?
It is tricky to figure out the date when it was last tuesday. 13January,2017 is Friday. In real world dataset, we don’t have the exact days of a list of dates when we need to code to get the last tuesday.

Incorrect Method

data temp;
mydate = ’13JAN2017’d;
lasttuesday = intnx(‘week.3‘, mydate , 0);
format mydate lasttuesday date9.;
proc print;
run;

It returns 10JAN2017. In this case, week.3 refers to tuesday within week as a unit of measurement. Similarly, week.2 refers to monday.

It doesn’t work when input date is current tuesday. For example, run the above code with mydate = ’10JAN2017’d. 10JAN2017 is tuesday. In this case, it returns ’10JAN2017′ which is not a previous tuesday. It should have returned ’03JAN2017′.

Correct Method

data temp;
mydate = ’10JAN2017’d;
lasttuesday = intnx(‘week.4’, mydate , -1, ‘end’);
format mydate lasttuesday date9.;
proc print;
run;

It returns 03JAN2017 which is previous tuesday.  See the changes we have made in this program –

  1. -1 instead of 0 as increment value
  2. ‘end’ instead of ‘beginning’ as date alignment
  3. ‘week.4’ instead of ‘week.3’ to figure out the last tuesday
5. Adjustment within the Interval
This program explains how INTCK function adjusts / align dates within the interval specified.

data temp;
mydate = ’31JAN2017’d;
beginning=intnx(‘year ‘, mydate , 1, ‘b’);
middle=intnx(‘year ‘, mydate , 1, ‘m’);
end=intnx(‘year ‘, mydate , 1, ‘e’);
sameday=intnx(‘year ‘, mydate , 1, ‘s’);
format mydate beginning middle end sameday date9.;
proc print;
run;

The abbreviation ‘b’ refers to beginning, ‘m’ – middle,  ‘e’ – end, ‘s’ – sameday. The default value is ‘b’ if you don’t specify anything in the fourth parameter.
Result
  1. beginning = 01JAN2018
  2. middle = 02JUL2018
  3. end = 31DEC2018
  4. sameday = 31JAN2018
SAS INTNX Alignment
6. Datetime Formats
Like date formats, we can use time and datetime formats in INTNX function to increment time (seconds / minutes / hours).

data temp;
mydt = ’29JAN2017:08:34:00’dt;
seconds=intnx(‘second’, mydt , 1);
minutes=intnx(‘minute’, mydt , 1);
hours=intnx(‘hour’, mydt , 1);
days=intnx(‘dtDay’, mydt , 1);
weeks=intnx(‘dtWeek’, mydt , 1);
format mydt seconds minutes hours days weeks datetime20.;
proc print NOOBS;
run;

SAS Datetime Formats



SAS Tutorials :
100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective – Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don’t.

Let’s Get Connected: Email | LinkedIn

Related Posts:


11 Responses to “SAS : INTNX Function with Examples”

  1. Arun 3 February 2017 at 11:42

    the only issue with intnx and intck is that its hard to distinguish to make a short form as other function in sas can be remembered easily.

    Reply Delete

    Replies

      Reply
  2. Aditya Jain 25 March 2017 at 14:40

    Don’t you think that "4. When was Last Tuesday?" will give the current week’s Tuesday instead of "Last Tuesday", if you give "09JAN2017" in mydate

    Reply Delete

    Replies

    1. Deepanshu Bhalla 26 March 2017 at 01:40

      It works fine for ’09JAN2017′ which is monday. I guess you meant to say ’10JAN2017′. I updated the code. Thanks for pointing it out. Cheers!

      Delete

      Replies

        Reply
    2. Reply
  3. Rishabh Sharma 2 June 2017 at 02:22

    how to extract last 15 days transaction date wise using this.

    Reply Delete

    Replies

      Reply
  4. Anonymous 7 June 2017 at 18:43

    Hi Deepanshu,
    Under "Datetime Formats" heading , when we add hour to the time , it should have been 9:34:00.ideally. How do we achieve this ?

    Reply Delete

    Replies

    1. Kate Grafel 9 November 2018 at 08:34

      Adding ‘s’ as the 4th parameter will accomplish this.

      data test;
      datetime = ’29JAN2017:08:34:00’dt;
      plus1hour = intnx(‘hour’,datetime,1,’s’);
      format datetime plus1hour datetime20.;
      proc print data=test; run;

      Delete

      Replies

        Reply
    2. Reply
  5. Anonymous 26 July 2017 at 02:28

    Thank you for this high quality introduction to the INTNX function with great examples that are nicely explained and very readable :o)

    Reply Delete

    Replies

      Reply
  6. Ashish Arora 22 May 2018 at 23:12

    Hi deepanshu,
    Thanks a lot for this info.However i have a question , i am trying to run a code that gives me next tuesday ,For 18Mar 2018 is sunday and i want to get 20 March as my answer.I am using tues = intnx (‘week’,sunday,2); and i am getting 1 April as answer . Any Advise

    Reply Delete

    Replies

    1. Kate Grafel 9 November 2018 at 08:39

      This will do it:

      data test;
      sunday = ’18MAR2018’d;
      tues = intnx(‘week.3’,sunday,1);
      format sunday tues date9.;
      proc print data=test; run;

      Your current code finds the beginning of the week 2 weeks from now. The code I suggest gives you the beginning of the week 1 week from now, where each week begins on a Tuesday (day 3).

      Delete

      Replies

        Reply
    2. Reply
  7. Kate Grafel 9 November 2018 at 08:43

    This is a fantastic tutorial. Thank you for providing it! I am troubleshooting another developer’s code that contains this function, and this information has been very helpful. Hope it’s OK I answered a couple of questions above! I couldn’t resist distracting myself from my current work to try out this function. 🙂

    Reply Delete

    Replies

    1. Deepanshu Bhalla 9 November 2018 at 23:43

      Thank you for stopping by my blog and answering questions. Cheers!

      Delete

      Replies

        Reply
    2. Reply
Add comment
Load more…

Next →
← Prev


Newer Post


Older Post

Home