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