Compare current month to previous month in a do loop macro

[Len18]

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);

[mkeintz]

It looks like the third argument of your call to the DATA macro should be &PREVYRMTH (but you have &PRVYRMTH).

[Len18]

Thanks! That was a typo, I've made the correction to the post.

[PaigeMiller]

Let's start at the beginning ... what is the value of &todaysdate?

Use:

%put &=todaysdate;

[Len18]

Hi. Today's date:

%let todaysdate = %sysfunc( today() );


I will add a %put line as I didn't do that.

[PaigeMiller]

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.

[Len18]

Thanks! I will make this change.

[Len18]

From the log, it appears that it produced what I wanted and then restarted the loop. I assume this is because of the 2 do statements. What I was trying to do is get the code to run once but with 2 different tables in the code having 2 different start and end dates. For instance, for March if I am trying to find the number of new accounts I want the program to look at the accounts in March and compare them to those in February, then do this for 13 months.

[Tom]

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]

Thanks, this produces an error :' The data set list ... does not contain any members'

[Kurt_Bremser]

---

@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.

[Len18]

I know posting the code will help but I am a little nervous about doing so. I did however, remove most of the code and used just 2 tables when calling the macro and you are definitely correct. The macro is not the issue. I will review the code bit by bit to better determine what the cause of the problem is and create a new post accordingly. Thanks a lot though, now I know I am looking at the wrong thing

[Kurt_Bremser]

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.

[Len18]

Thanks. Just to give some background. There were several pieces of code written by different individuals providing output on various products. I was asked to take all of these pieces of code and consolidate into one program so that all of the product data can be viewed together. I was able to do so, the program runs perfectly when run for one month. I was also asked to automate and provide the output for the last 13 months, so no dates or other variables would have to be inputted. Just click run and get the results. So this is where I started having problems. For this particular category of products I broke up the code and ran the 13 month macros. Comparing the results along the way to the results for individual months (without macro). For some reason a portion of code producing correct results with no macro is somehow producing different results within the macro. I was further able to narrow it down to a case when statement. Case when var1 (current month) in (select var2 from tbl2 (previous month)) then 'old' else 'new'. I have been stuck here trying to answer the why.

[Len18]

I will post the macro with this portion of code shortly.