When using Nested INTNX to add a Month and a Day to a Date variable, the outputs differ in a way that is unexpected.
These two scenarios result in different Date Values:
(1). Take a Date Value and then Subtract a Month from it and then Add 9 days to it.
(2). Take the same Date Value and then Add 9 days to it and then Subtract a Month from it.
Please explain why there is a difference between the results of the 2 above cases using the specific code provided below.
This is the code I am struggling with:
Data Test;
birthd='20180901';
date_new = input(birthd, yymmdd8.);
date_new2 = INTNX('MONTH' ,date_new, -1);
date_new3 = INTNX('DAY', INTNX('MONTH' ,date_new, -1), 9) ;
date_new4 = INTNX('DAY' ,date_new, 10);
date_new5 = INTNX('MONTH', INTNX('DAY' ,date_new, 9), -1) ;
format date_new yymmdd10.;
format date_new2 yymmdd10.;
format date_new3 yymmdd10.;
format date_new4 yymmdd10.;
format date_new5 yymmdd10.;
Run;
OUTPUT:
============
birthd date_new date_new2 date_new3 date_new4 date_new5
20180901 2018-09-01 2018-08-01 2018-08-10 2018-09-11 2018-08-01
The fact that the Variables date_new3 and date_new5 have different values is perplexing.
** Using Version 7.15 of SAS Enterprise Guide
*** I come from a programming background experienced in C#, JAVA, C, VB.NET, JavaScript, VBA etc. But I am very new to the nuances of the Base SAS programming language Syntax which is SO VERY DIFFERENT!
Please Help!!!
There is an alignment optional parameter in INTNX which defaults to the beginning of the month. Since you don't specify his alignment parameter, you get the beginning of the month. Try specifiying the alignment parameter to be "S" for same day of the month.
@manojmenon088 wrote:
When using Nested INTNX to add a Month and a Day to a Date variable, the outputs differ in a way that is unexpected.
These two scenarios result in different Date Values:
(1). Take a Date Value and then Subtract a Month from it and then Add 9 days to it.
(2). Take the same Date Value and then Add 9 days to it and then Subtract a Month from it.
Please explain why there is a difference between the results of the 2 above cases using the specific code provided below.
This is the code I am struggling with:
Data Test;
birthd='20180901';
date_new = input(birthd, yymmdd8.);
date_new2 = INTNX('MONTH' ,date_new, -1);
date_new3 = INTNX('DAY', INTNX('MONTH' ,date_new, -1), 9) ;
date_new4 = INTNX('DAY' ,date_new, 10);
date_new5 = INTNX('MONTH', INTNX('DAY' ,date_new, 9), -1) ;
format date_new yymmdd10.;
format date_new2 yymmdd10.;
format date_new3 yymmdd10.;
format date_new4 yymmdd10.;
format date_new5 yymmdd10.;Run;
OUTPUT:
============
birthd date_new date_new2 date_new3 date_new4 date_new5
20180901 2018-09-01 2018-08-01 2018-08-10 2018-09-11 2018-08-01
The fact that the Variables date_new3 and date_new5 have different values is perplexing.
** Using Version 7.15 of SAS Enterprise Guide
*** I come from a programming background experienced in C#, JAVA, C, VB.NET, JavaScript, VBA etc. But I am very new to the nuances of the Base SAS programming language Syntax which is SO VERY DIFFERENT!
Please Help!!!
You might also describe what you were attempting to do when you discovered this.
Sometimes people for other programming backgrounds will try to force one function to do something one way when use of the options or different syntax would be the better approach.
For instance the use of interval multiples or shifts such as intnx('year2', to use two-year intervals, or shift-index such as intnx('Year.3' where the 'year' now is considered to start in March (month being the shift interval for year) and the combinations there of
As Paige said, add option 'S' .
Data Test;
birthd='20180901';
date_new = input(birthd, yymmdd8.);
date_new2 = INTNX('MONTH' ,date_new, -1,'s');
date_new3 = INTNX('DAY', INTNX('MONTH' ,date_new, -1), 9) ;
date_new4 = INTNX('DAY' ,date_new, 9);
date_new5 = INTNX('MONTH', INTNX('DAY' ,date_new, 9), -1,'s') ;
format date_new yymmdd10.;
format date_new2 yymmdd10.;
format date_new3 yymmdd10.;
format date_new4 yymmdd10.;
format date_new5 yymmdd10.;
Run;
If you don't tell INTNX() where in the interval you want to move to it moves to the beginning. Use the last parameter to adjust that.
All months don't have the same number of days. So the order you do the operations will make a difference.
Note there is no need to use INTNX() to move by days. Dates are just stored as the number of days since 1960 so you can use simple addition or subtraction to move by days.
data test;
do date='01MAR2019'd to '01APR2019'd;
date_minus_month=intnx('month',date,-1,'same');
date_plus9=date+9;
date1=intnx('month',date,-1,'same')+9;
date2=intnx('month',date+9,-1,'same');
output;
end;
format date: date9.;
run;
proc print;
where date1 ne date2;
run;
date_ minus_ date_ Obs date month plus9 date1 date2 20 20MAR2019 20FEB2019 29MAR2019 01MAR2019 28FEB2019 21 21MAR2019 21FEB2019 30MAR2019 02MAR2019 28FEB2019 22 22MAR2019 22FEB2019 31MAR2019 03MAR2019 28FEB2019 23 23MAR2019 23FEB2019 01APR2019 04MAR2019 01MAR2019 24 24MAR2019 24FEB2019 02APR2019 05MAR2019 02MAR2019 25 25MAR2019 25FEB2019 03APR2019 06MAR2019 03MAR2019 26 26MAR2019 26FEB2019 04APR2019 07MAR2019 04MAR2019 27 27MAR2019 27FEB2019 05APR2019 08MAR2019 05MAR2019 28 28MAR2019 28FEB2019 06APR2019 09MAR2019 06MAR2019 29 29MAR2019 28FEB2019 07APR2019 09MAR2019 07MAR2019 30 30MAR2019 28FEB2019 08APR2019 09MAR2019 08MAR2019
So for the first example where the two methods give different results you see that adding 9 days moves to MAR29. When you then ask INTNX to move back to the same day in Feb there is no FEB29 so you end up with FEB28. But if you first move back to FEB20 and add 9 days you end up on MAR01.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.