BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Raitag
Fluorite | Level 6

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 🙂 :

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Super User

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
Super User Tom
Super User

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
Fluorite | Level 6
Hi ,
Thank you guys for your help .
All i had to do is to omit the format as KurtBremser said .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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