Nesting INTNX function yields different unexpected results

[manojmenon088]

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!!!

[PaigeMiller]

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.

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p...

[ballardw]

---

@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

[Ksharp]

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;

[Tom]

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.