Hi,
I am trying to compare accounts in the current month to those of the previous month (in order to determine, new accounts, increases and decreases) and I would like to do this for the last 13 months.
I attempted the following, although no errors came up in the log, it appears to have gotten stuck in the loop (code was running for about 4 hours).
Any ideas as to where I am going wrong?
%MACRO DATA(beg, end, beg_1, end_1);
%LOCAL start start1 i j prvmth dt;
%let start=%sysfunc(inputn(&beg,yymmn6));
%let start1=%sysfunc(inputn(&beg_1,yymmn6));
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,yymmn6))));
%do j=0 %to %sysfunc(intck(month,&start1,%sysfunc(inputn(&end_1,yymmn6))));
%let prvmth=%sysfunc(intnx(month,&start1,&j),yymmn6);
%let dt=%sysfunc(intnx(month,&start,&i),yymmn6);
%END;
%END;
%MEND;
%let dt = %sysfunc( intnx( month, &todaysdate, -1, e), yymmn6. ); *TO CALC END OF 13MTH REPORTING PERIOD;
%put dt is &dt;
%let prevdt = %sysfunc( intnx( month, &todaysdate, -13, e), yymmn6. ); *TO CALC START OF 13MTH REPORTING PERIOD;
%put prevdt is &prevdt;
%let prvmth = %sysfunc( intnx( month, &todaysdate, -2, e), yymmn6. ); *TO CALC MTH PRIOR TO CURRENT MTH OF CURRENT YR;
%put prvmth is &prvmth;
%let prevyrmth = %sysfunc( intnx( month, &todaysdate, -14, e), yymmn6. ); *TO CALC MTH PRIOR TO START OF 13 MTH REPORTING;
%put prevyrmth is &prevyrmth;
%DATA(&PREVDT,&DT,&PREVYRMTH,&PRVMTH);
It looks like the third argument of your call to the DATA macro should be &PREVYRMTH (but you have &PRVYRMTH).
Let's start at the beginning ... what is the value of &todaysdate?
Use:
%put &=todaysdate;
When I run your macro, it loops 13 times through the first loop (&I going 0 to 12) and another 13 times through the second loop (&J going 0 to 12). So I'm not able to say why when you run it takes 4 hours.
To help you further debug your own situation, you can print information to the log about each iteration, for example (partial code)
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,yymmn6))));
%put &=i;
%do j=0 %to %sysfunc(intck(month,&start1,%sysfunc(inputn(&end_1,yymmn6))));
%put &=j;
%let prvmth=%sysfunc(intnx(month,&start1,&j),yymmn6);
Lastly, you don't have to constantly format and unformat and format and unformat your variables. Make your life and your code simple: leave the macro variables as unformatted all the way through the code.
If you want the %DO loop to count backwards you have to tell it that.
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,yymmn6)))) %by -1;
%let dt=%sysfunc(intnx(month,&start,&i),yymmn6);
%do j=0 %to %sysfunc(intck(month,&start1,%sysfunc(inputn(&end_1,yymmn6)))) %by -1;
%let prvmth=%sysfunc(intnx(month,&start1,&j),yymmn6);
@Len18 wrote:
Thanks, this produces an error :' The data set list ... does not contain any members'
Your macro as posted CAN NOT create this ERROR, as it does not contain any non-macro code necessary for this.
If you want us to diagnose your code, you need to show it (and the log from it) as is.
Don't jump right into the middle and write a big macro with lots of steps and lots of macro code.
Start with the Base SAS code that does what you want for a single, defined instance. Then replace the elements that define the instance with macro variables and retest. Only then start to define those elements dynamically through macro code. That way you make sure that you don't have a mistake in the Base SAS code that is masked by all the macro processing around it. By working in small steps with tests you also catch a mistake as soon as it is introduced.
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.