DATA Step, Macro, Functions and more

Call INTNX FROM %SYSFUNC issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Call INTNX FROM %SYSFUNC issue

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 Smiley Happy :


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,928

Re: Call INTNX FROM %SYSFUNC issue

[ Edited ]

Very simple (once you know where to look, of course Smiley Wink ): &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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 6,928

Re: Call INTNX FROM %SYSFUNC issue

[ Edited ]

Very simple (once you know where to look, of course Smiley Wink ): &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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,466

Re: Call INTNX FROM %SYSFUNC issue

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.;
Super User
Super User
Posts: 6,495

Re: Call INTNX FROM %SYSFUNC issue

[ Edited ]

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
Occasional Contributor
Posts: 17

Re: Call INTNX FROM %SYSFUNC issue

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 106 views
  • 1 like
  • 4 in conversation