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 🙂 :
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.
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.
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.;
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
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.