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

%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); ... View more

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

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. ... View more

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 ... View more

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. ... View more

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

Thanks! I will make this change. ... View more

Hi. Today's date: %let todaysdate = %sysfunc( today() ); I will add a %put line as I didn't do that. ... View more