topic Re: Compare current month to previous month in a do loop macro in SAS Programming

Compare current month to previous month in a do loop macro

Len18 — Tue, 28 Apr 2020 20:38:11 GMT

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

Re: Compare current month to previous month in a do loop macro

mkeintz — Tue, 28 Apr 2020 19:44:05 GMT

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

Re: Compare current month to previous month in a do loop macro

PaigeMiller — Tue, 28 Apr 2020 19:47:40 GMT

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

Use:

%put &=todaysdate;

Re: Compare current month to previous month in a do loop macro

Len18 — Tue, 28 Apr 2020 20:38:47 GMT

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

Re: Compare current month to previous month in a do loop macro

Len18 — Tue, 28 Apr 2020 20:41:12 GMT

Hi. Today's date:

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

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

Re: Compare current month to previous month in a do loop macro

PaigeMiller — Tue, 28 Apr 2020 21:31:08 GMT

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.

Re: Compare current month to previous month in a do loop macro

Tom — Tue, 28 Apr 2020 21:28:49 GMT

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

Re: Compare current month to previous month in a do loop macro

Len18 — Wed, 29 Apr 2020 04:28:08 GMT

Thanks! I will make this change.

Re: Compare current month to previous month in a do loop macro

Len18 — Wed, 29 Apr 2020 05:31:48 GMT

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

Re: Compare current month to previous month in a do loop macro

Len18 — Wed, 29 Apr 2020 05:54:42 GMT

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.

Re: Compare current month to previous month in a do loop macro

Kurt_Bremser — Wed, 29 Apr 2020 06:41:08 GMT

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

Re: Compare current month to previous month in a do loop macro

Len18 — Wed, 29 Apr 2020 07:21:44 GMT

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

Re: Compare current month to previous month in a do loop macro

Kurt_Bremser — Wed, 29 Apr 2020 07:53:52 GMT

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.

Re: Compare current month to previous month in a do loop macro

Len18 — Thu, 30 Apr 2020 10:07:07 GMT

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.

Re: Compare current month to previous month in a do loop macro

Len18 — Thu, 30 Apr 2020 10:09:40 GMT

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

Re: Compare current month to previous month in a do loop macro

Len18 — Thu, 30 Apr 2020 11:37:50 GMT

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



PROC SQL;
	CREATE TABLE TEST_&DT AS 
		SELECT 
            PROD,
			CASE WHEN PROD IN (SELECT PROD FROM TEST2_&PRVMTH.)
				THEN 'OLD' 
				ELSE 'NEW' 
			END AS NEW 
		FROM IND_&DT							 
QUIT;

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

Re: Compare current month to previous month in a do loop macro

Kurt_Bremser — Thu, 30 Apr 2020 11:52:36 GMT

And what does the log from the macro say?

Re: Compare current month to previous month in a do loop macro

Len18 — Thu, 30 Apr 2020 11:55:57 GMT

Actually, I just changed the name of the field created by the case when statement and that fixed it. Phew! Thanks!