%DO %TO Loop build dynamic table and report macro

[JeffGoldblum]

Taking over building an old report that had too many lines of code. I've condensed down most of the logic but having trouble at the %DO %TO where I would like to loop through and build a table displaying the data up to the value I and if that value doesn't exist, stop the loop and continue on. I keep getting the error where the next value of I doesn't yet exist.

From there, each table would populate a report to show each level with Level 4 holding everything at or above 4 CONSEC_MTHs with that corresponding REPORT_TITLE# (See Sample Report attached). I am not too concerned about the report portion yet as that is still in development for what exactly will be showing there, but any feedback there is always appreciated.

%MACRO LEAD(
		TIMESPAN = MONTH,
		MTH_AWY = xxxxx,
);



	PROC SQL;
		CREATE TABLE WORK.KCI AS
			SELECT
				CITY,
				TEAM,
				NAME_FL,
				EMP_ID,
				EMP_NO,
				YR_MTH
			FROM KCI
	;RUN;

	PROC SORT DATA=WORK.KCI2;
		BY EMP_ID YR_MTH;
	RUN;

	DATA WORK.KCI_LEAD ;
		SET WORK.KCI2;
		BY EMP_ID YR_MTH;
			IF FIRST.EMP_ID=0 AND YR_MTH-LAG(YR_MTH)=1
				THEN CONSEC_MTH+1;ELSE CONSEC_MTH=1;
			IF CONSEC_MTH>=2 
				THEN LEAD_SCORE=(CONSEC_MTH-1)*100;ELSE LEAD_SCORE=0;
	RUN;

	PROC SQL NOPRINT;
		SELECT (MAX(CONSEC_MTH))
		INTO :LEAD_MTH
		FROM WORK.KCI_LEAD;
	QUIT;

	%DO I=1 %TO &LEAD_MTH;
		PROC SQL;
			CREATE TABLE MONTH_&I AS
				SELECT *,
					CASE 
						WHEN &I = 1 
						THEN 1 
						ELSE 0 
					END AS FIRST,
					CASE 
						WHEN &I = 2 
						THEN 2 
						ELSE 0 
					END AS SECOND,
					CASE 
						WHEN &I = 3 
						THEN 3 
						ELSE 0 
					END AS THIRD,
					CASE 
						WHEN &I >= 4 
						THEN &I 
						ELSE 0 
					END AS OVER
				FROM KCI_LEAD
				WHERE CONSEC_MTH <= &ELITE_MTH
				ORDER BY
					TEAM,
					LEAD_SCORE DESCENDING
		;QUIT;
	%END;

	PROC SQL NOPRINT;
		SELECT
			CATS("Lead","-- Level 1 --",&I),
			CATS("Lead","-- Level 2 --",&I),
			CATS("Lead","-- Level 3 --",&I),
			CATS("Lead","-- Level 4 --",&I)
		INTO
			:REPORT_TITLE1,
			:REPORT_TITLE2,
			:REPORT_TITLE3,
			:REPORT_TITLE4
		FROM MONTH_&I
	;QUIT;


	PROC FORMAT;
		VALUE $BG_COLOR
		"EMP INFO" = CXB3B2BF
		"SCORE" = #90B0D9
		"ERROR" = #3883A8
		"ERROR DATE" = #4D7EBF;
	RUN;

	PROC REPORT
		DATA=MONTH_&I
		&REPORT_STYLE
		STYLE(HEADER)=[COLOR=BLACK BACKGROUND=$BG_COLOR.]
		SPLIT="|";

		COLUMNS
		(("&REPORT_TITLE&I"
		("EMPLOYEE"
		(TEAM NAME_FL EMP_ID EMP_NO CITY CONSEC_MTH))
		DEFINE TEAM/ DISPLAY "TEAM" STYLE(HEADER)={BACKGROUND=CXB3B2BF};
		DEFINE NAME_FL/ DISPLAY "NAME" STYLE(HEADER)={BACKGROUND=CXB3B2BF};
		DEFINE EMP_ID / DISPLAY "EMP_ID" STYLE(HEADER)={BACKGROUND=CXB3B2BF};
		DEFINE EMP_NO / DISPLAY "EMP_NO" STYLE(HEADER)={BACKGROUND=CXB3B2BF};
		DEFINE CITY/ DISPLAY "CITY" STYLE(HEADER)={BACKGROUND=CXB3B2BF};

		DEFINE CONSEC_MTH/ DISPLAY "MONTHS" STYLE(HEADER)={BACKGROUND=CXB3B2BF};
	ENDCOMP;

	RUN;


	%MEND LEAD;

SAMPLE DATA:

TEAM NAME_FL EMP_ID EMP_NO CITY YR_MTH
A GEORGE COSTANZA 1135GC 1135 ATLANTA 202101
A KOSMO KRAMER 1146KK 1146 ATLANTA 202104
A ELAINE BENES 1157EB 1157 ATLANTA 202105
B JERRY SEINFELD 1168JS 1168 NEW YORK 202108
B LLOYD BRAUN 1189LB 1189 NEW YORK 202112
A BOB SAKAMANO 1123BS 1123 ATLANTA 202102
A BOB SAKAMANO 1123BS 1123 ATLANTA 202103
A FRANK COSTANZA 1142FC 1142 ATLANTA 202105
A FRANK COSTANZA 1142FC 1142 ATLANTA 202106
B MORTY SEINFELD 1153MS 1153 NEW YORK 202103
B MORTY SEINFELD 1153MS 1153 NEW YORK 202104
C SUSAN ROSS 1163SR 1163 PHILADELPHIA 202109
C SUSAN ROSS 1163SR 1163 PHILADELPHIA 202110
A MATT WILHELM 1175MW 1175 ATLANTA 202109
A MATT WILHELM 1175MW 1175 ATLANTA 202110
A MATT WILHELM 1175MW 1175 ATLANTA 202111
B JACOPO PETERMAN 1186JP 1186 NEW YORK 202101
B JACOPO PETERMAN 1186JP 1186 NEW YORK 202102
B JACOPO PETERMAN 1186JP 1186 NEW YORK 202103
C DAVID PUTTY 1198DP 1198 PHILADELPHIA 202107
C DAVID PUTTY 1198DP 1198 PHILADELPHIA 202108
C DAVID PUTTY 1198DP 1198 PHILADELPHIA 202109
A MICKEY ABBOTT 1129MA 1129 ATLANTA 202108
A MICKEY ABBOTT 1129MA 1129 ATLANTA 202109
A MICKEY ABBOTT 1129MA 1129 ATLANTA 202110
A MICKEY ABBOTT 1129MA 1129 ATLANTA 202111
B JACKIE CHILES 1138JC 1138 NEW YORK 202103
B JACKIE CHILES 1138JC 1138 NEW YORK 202104
B JACKIE CHILES 1138JC 1138 NEW YORK 202105
B JACKIE CHILES 1138JC 1138 NEW YORK 202106
B JACKIE CHILES 1138JC 1138 NEW YORK 202107
B JACKIE CHILES 1138JC 1138 NEW YORK 202108
B JACKIE CHILES 1138JC 1138 NEW YORK 202109
B JACKIE CHILES 1138JC 1138 NEW YORK 202110
B TIM WHATLEY 1144TW 1144 NEW YORK 202102
B TIM WHATLEY 1144TW 1144 NEW YORK 202103
B TIM WHATLEY 1144TW 1144 NEW YORK 202104
B TIM WHATLEY 1144TW 1144 NEW YORK 202105
B TIM WHATLEY 1144TW 1144 NEW YORK 202106
B TIM WHATLEY 1144TW 1144 NEW YORK 202107
B TIM WHATLEY 1144TW 1144 NEW YORK 202108
B TIM WHATLEY 1144TW 1144 NEW YORK 202109
B TIM WHATLEY 1144TW 1144 NEW YORK 202110
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202101
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202102
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202103
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202104
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202105
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202106
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202107
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202108
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202109
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202110
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202111
C IZZY MANDELBAUM 1199IM 1199 PHILADELPHIA 202112

Sample Report:

Lead-- Level 1 --1
TEAM	NAME_FL	EMP_ID	EMP_NO	CITY	MONTHS
A	GEORGE COSTANZA	1135GC	1135	ATLANTA	1
A	KOSMO KRAMER	1146KK	1146	ATLANTA	1
A	ELAINE BENES	1157EB	1157	ATLANTA	1
B	JERRY SEINFELD	1168JS	1168	NEW YORK	1
B	LLOYD BRAUN	1189LB	1189	NEW YORK	1
Lead-- Level 2 --2
TEAM	NAME_FL	EMP_ID	EMP_NO	CITY	MONTHS
A	BOB SAKAMANO	1123BS	1123	ATLANTA	2
A	FRANK COSTANZA	1142FC	1142	ATLANTA	2
B	MORTY SEINFELD	1153MS	1153	NEW YORK	2
C	SUSAN ROSS	1163SR	1163	PHILADELPHIA	2
Lead-- Level 3 --3
TEAM	NAME_FL	EMP_ID	EMP_NO	CITY	MONTHS
A	MATT WILHELM	1175MW	1175	ATLANTA	3
B	JACOPO PETERMAN	1186JP	1186	NEW YORK	3
C	DAVID PUTTY	1198DP	1198	PHILADELPHIA	3
Lead-- Level 4 --4
TEAM	NAME_FL	EMP_ID	EMP_NO	CITY	MONTHS
A	MICKEY ABBOTT	1129MA	1129	ATLANTA	4
B	JACKIE CHILES	1138JC	1138	NEW YORK	8
B	TIM WHATLEY	1144TW	1144	NEW YORK	9
C	IZZY MANDELBAUM	1199IM	1199	PHILADELPHIA	12

Thank you in advance!

[PaigeMiller]

Most of us will not download Excel files as they are a security threat. Please show us the output via screen capture, or as text.

Can you explain what this means?

/*What is needed within here to stop looping/building if &I is not at 3 or >=4?*/

Since &i starts at 1, we should stop looping immediately???

Can you re-format your code with indentations to make it more readable and to help us visualize the different parts of the code, and then paste it into the box that appears when you click on the "little running man" icon?

[PaigeMiller]

Okay great, now you have shown us the output, but since you haven't shown us (a portion of) the input data, I don't think we can move forward yet. I think we need to see (a portion of) the input data as well. Data should be provided via these instructions, or similar. 

[pink_poodle]

And what if you take out that sql loop into another macro and feed the lead_month parameter to it? Can test parameter on this first - should certainly work:
%MACRO DO_LABEL(COUNT);
%DO I = 1 %TO &COUNT;
PAY&I = "Payment for visit &I"
%END;
%MEND DO_LABEL;

[ballardw]

When I something like

PROC SQL;
CREATE TABLE MONTH_&I AS
SELECT *,

and  then

PROC REPORT
DATA=MONTH_&I

I always start thinking this is an example where BY group processing would likely be in the solution.

What does your macro variable &I actually represent?

Have you verified that this data step is producing the correct output?

DATA WORK.KCI_LEAD ;
SET WORK.KCI2;
BY EMP_ID YR_MTH;
IF FIRST.EMP_ID=0 AND YR_MTH-LAG(YR_MTH)=1
THEN CONSEC_MTH+1;ELSE CONSEC_MTH=1;
IF CONSEC_MTH>=2 THEN LEAD_SCORE=(CONSEC_MTH-1)*100;ELSE LEAD_SCORE=0;
RUN;

The LAG function is notorious for not producing expected results in IF statements.

[JeffGoldblum]

Yes, the LAG does exactly what is needed. I built that and tested against 3 years of data to count consecutive months and restart after a gap. I then confirmed that it does indeed pull as expected through all values in the code.

When I get to the %DO I %TO Loop, I would like to have it build a table for all the 1-months; a table for 2-months, table for 3-months, and then one for all those > 4.

If I only have 1 month, stop after building that first table - here's how it was crudely explained:

2 months? build 2 tables (1 month & 2 month);

          - unless you only have 2 month data, then just build that. 

3 months? build 3 tables (1 month, 2 month, 3 month) - unless you only have 3 month data, then just build that.

          - unless you only have 1 month data, then just build that. 

          - unless you only have 2 month data, then just build (3 month and the other month).

4+ months? build 4 tables(1 month, 2, month, 3 month, and 4 month-including >4)

          - unless you only have 1 month data, then just build that.  

          - unless you only have 2 month data, then just build (4 month and the other months).

          - unless you only have 3 month data, then just build (4 month and the other months).

[Tom · Accepted solution]

I don't understand.

By TABLE do you mean the REPORT you showed?  You reports looks like ONE report with multiple sections.  Can't you just create a BY variable to produce the different sections?

Or do you mean a DATASET?  What is the difference between the datasets?  Can't you do what you want with one dataset and just apply different WHERE conditions when using it?

[JeffGoldblum]

Combining the new BY variable, capping as suggested by @pink_poodle, and adding a little more logic created the solution. Thank you so much for the assistance!

[Tom]

I have no idea whether what you are asking for is reasonable but it sounds like you want a loop like this.

You have some upper bound , not sure how you are calculating it but it could be 1 or 2 or 3 etc.  So let's call it N.

Now you want to run a loop from 1 to N.

%do i = 1 %to &N ;
  *do stuff ;
%end;

Is that what you wanted to know?

[pink_poodle]

I think the idea is to cap the &N at 4 when it is greater than 4. %If &N > 4 %then %do; %let n = 4; %end; However, we are not ignoring data for &N greater than 4. They need to get incorporated into table 4.