Call INTNX FROM %SYSFUNC issue

[Raitag]

Good evening everyone ;

I m having a trouble while callin the INTNX function from %sysfunc which i don't understand

hope you guys help me

Well i have this date  date_arr = 30/06/2017 and i want the 3 earlier month date which is the 31/03/2017

so here is my code 

%global arrete ; 
%global date_arr ; 
%global date_hr ; 

%let arrete = 20170630 ; 
%let date_arr = %sysfunc(mdy(%substr(&arrete,5,2),%substr(&arrete,7,2),%substr(&arrete,1,4)), ddmmyy10.);

%put &date_arr. ; 

%let date_hr = %sysfunc(intnx(month, &date_arr, - 3  ,e), ddmmyy10.);

%put  &date_hr. ;

Everything works fine , i don't get any errors or warnings but the problem is the value i get for date_hr is 31/10/1959

i share my log too

 %global arrete ;
118  %global date_arr ;
119  %global date_hr ;
120
121  %let arrete = 20170630 ;
122  %let date_arr = %sysfunc(mdy(%substr(&arrete,5,2),%substr(&arrete,7,2),%substr(&arrete,1,4)),
122! ddmmyy10.);
123
124  %put &date_arr. ;
30/06/2017
125
126  %let date_hr = %sysfunc(intnx(month, &date_arr, - 3  ,e), ddmmyy10.);
127
128  %put la valeur de &date_hr. ;
 31/10/1959

thank you 🙂 :

[Kurt_Bremser · Accepted solution]

Very simple (once you know where to look, of course 😉 😞 &date_arr is not a SAS date value, but a representation of a SAS date value.

Omit the format when creating it:

%let arrete = 20170630 ; 
%let date_arr = %sysfunc(mdy(%substr(&arrete,5,2),%substr(&arrete,7,2),%substr(&arrete,1,4)));

%put &date_arr. ; 

%let date_hr = %sysfunc(intnx(month, &date_arr, - 3  ,e), ddmmyy10.);

%put  &date_hr. ;

And you'll see that the end result looks fine.

Moral: look at my Maxim 28. Don't use formats when values are needed.

[ballardw]

And perhaps don't use functions when an INFORMAT may work:

%let arrete = 20170630 ; 
%let date_arr = %sysfunc(mdy(%substr(&arrete,5,2),%substr(&arrete,7,2),%substr(&arrete,1,4)));

%put Date_arr is &date_arr. ; 


%let date_arr2 = %sysfunc(inputn(&arrete.,yymmdd8.));
%put Date_arr2 is &date_arr2.;

[Tom]

Dates are the number of days since beginning of 1960.  Your macro variable looks like this: 

30/06/2017

So you are asking SAS to divide 30 by 6 and then again by 2017. This is a number close to zero.  So you are asking INTNX() it to go back 3 months from Jan 1960.

Either pass the INTNX() function actual raw numbers of days or date literals.

Try this little bit of code using your example date.

%let arrete = 20170630 ; 
%let date_arr_raw = %sysfunc(inputn(&arrete,yymmdd8));
%let date_arr_lit = "%sysfunc(inputn(&arrete,yymmdd8),date9)"d;
%let date_arr_ymd = %sysfunc(inputn(&arrete,yymmdd8),yymmdd10);
%let date_arr_dmy = %sysfunc(inputn(&arrete,yymmdd8),ddmmyy10);
%let date_arr_mdy = %sysfunc(inputn(&arrete,yymmdd8),mmddyy10);

%put &=arrete
     &=date_arr_raw
     &=date_arr_lit
     &=date_arr_ymd
     &=date_arr_dmy
     &=date_arr_mdy
;

%let date_hr = %sysfunc(intnx(month, &date_arr_raw, - 3  ,e), ddmmyy10.);
%put  &=date_hr. ;

%let date_hr = %sysfunc(intnx(month, &date_arr_lit, - 3  ,e), ddmmyy10.);
%put  &=date_hr. ;

 Results:

ARRETE=20170630      
DATE_ARR_RAW=21000      
DATE_ARR_LIT="30JUN2017"d      
DATE_ARR_YMD=2017-06-30
DATE_ARR_DMY=30/06/2017      
DATE_ARR_MDY=06/30/2017

DATE_HR=31/03/2017

DATE_HR=31/03/2017

[Raitag]

Hi ,
Thank you guys for your help .
All i had to do is to omit the format as KurtBremser said .