@ballardw can you look at my code and tell me if it looks correct? I included the original report that uses the macro and then my additional report. I just want to make sure I am assigning the macro correctly.
proc sql;
create table TOH_Data as
select distinct
a.*
from medicaresalesagencies3 a
where toh_agency is not null
and toh_agency not in ('K022');
quit;
/*Code will drop the table on SO_SHARE*/
/*proc sql;
create table SO_SHARE.TOH_Data as
select distinct
a.*
from medicaresalesagencies3 a
where toh_agency is not null
and toh_agency not in ('K022');
quit;*/
/* create a list of the broker manager's name to use in the loop macro to create sub reports */
proc sql;
select distinct
compress(toh_agency,"-") into : toh_agency1 separated " "
from TOH_Data
where toh_agency like ('S%') or toh_agency like ('K%') and toh_agency is not missing
;
quit;
/* This is to pull in the FMO status report information to create the pre-status appointments */
proc sql;
create table summary_outputII as
select distinct
a.*,
b.'LEVEL 2'n as 'Level_Down 2'n,
b.'LEVEL 3'n as 'Level_Down 3'n,
b.'LEVEL 4'n as 'Level_Down 4'n,
case
when b.TOH_Agency is null then aor
else b.TOH_Agency end as 'TOH_Agency'n
from summary_output a
left join Hierarchy_final b
on a.aor = b.'level 4'n;
quit;
/*proc sql;*/
/* select distinct*/
/* compress(toh_agency," -") into : toh_agency2 separated " "*/
/* from summary_outputII*/
/* where toh_agency like ('S%') or toh_agency like ('K%') and toh_agency is not missing*/
/* ;*/
/*quit;*/
%macro hex2(n);
%local digits n1 n2;
%let digits = 0123456789ABCDEF;
%let n1 = %substr(&digits, &n / 16 + 1, 1);
%let n2 = %substr(&digits, &n - &n / 16 * 16 + 1, 1);
&n1&n2
%mend hex2;
%macro RGB(r,g,b);
%cmpres(CX%hex2(&r)%hex2(&g)%hex2(&b))
%mend RGB;
/* this macro will look through each TOH agency and pull the data associated then create a rollup report */
%macro list8;
%local i rep ;
%do i=1 %to %sysfunc(countw(&toh_agency1));
%let rep = %scan(&toh_agency1, &i);
%put &toh_agency1.;
%put &rep.;
%let font2 = wh;
%let fonttype = Arial;
proc sql;
create table summary_outputIII as
select distinct
* from summary_outputII
where 'toh_agency'n = "&rep." ;
ods excel file="/u/&sysuserid./Medicare Sales Agency Report Rollup_&rep..xlsx" options (autofilter='all' sheet_name="Pre-Appointment Status");
/* this is the proc report where you define the variables to create the sub reports */
proc report data = summary_outputIII headskip missing /*style=monochromeprinter*/ split='|'
style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];
cols
'AOR'n
'Agency_nm'n
'Level_Down 3'n
'Level_Down 2'n
'TOH_Agency'n
'WAITING FOR APPLICATION'n
'BACKGROUND REQUESTED'n
'BGC COMPLETED'n
'CPM TEAM WORKING ON IT'n
'FMO AGENT APPOINTMENT RECEIVED'n
'APPOINTMENT COMPLETED'n
'NOT PROCESSED'n
'Total'n;
define 'AOR'n / left "AGENCY CODE" style(column) = [tagattr='Format:@'];
define 'Agency_nm'n / left "AGENCY|NAME" style(column)={cellwidth=5in tagattr='wraptext:no' width=50%};
define 'Level_Down 3'n / left "Level 3" style(column) = [cellwidth=2in tagattr='Format:@'];
define 'Level_Down 2'n / left "Level 2" style(column) = [cellwidth=2in tagattr='Format:@'];
define 'TOH_Agency'n / left "TOH AGENCY" style(column) = [cellwidth=2in tagattr='Format:@'];
define 'WAITING FOR APPLICATION'n / right "WAITING FOR|APPLICATION" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define 'BACKGROUND REQUESTED'n / right "BACKGROUND |REQUESTED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define 'BGC COMPLETED'n / right "BACKGROUND |COMPLETED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define 'CPM TEAM WORKING ON IT'n / right "CPM TEAM|WORKING ON IT" format=comma8. style(column)=[cellwidth=3in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define 'FMO AGENT APPOINTMENT RECEIVED'n / right "FMO AGENT|APPOINTMENT |RECEIVED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define 'APPOINTMENT COMPLETED'n / right "APPOINTMENT|COMPLETED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'] ;
define 'NOT PROCESSED'n / right "NOT |PROCESSED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
define Total / right "Total" format=comma8. style(column)=[cellwidth=1.5in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
rbreak after/summarize style=[fontweight=bold];
compute after;
'AOR'n = 'Total';
call define(_row_,'style','style=[cellwidth=1in fontweight=bold bordercolor=lightgray backgroundcolor=lightgray foreground=black]');
endcomp;
run;
proc sql;
create table AHIPNeeded2 as
select distinct
* from AHIPNeeded
where 'toh_agency'n = "&rep.";
ods excel options(sheet_name="AHIP Needed" autofilter='all')
proc report data = AHIPNeeded2 headskip missing /*style=monochromeprinter*/ split='|'
style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];
columns 'Agent Name'N toh_agency assigned_broker_manager SALS_AGET_CD 'Agency Name'n ;
define 'Agent Name'N / left "Agent Name" style(column)={cellwidth=2in};
define 'TOH_Agency'n / left "TOH Agency" style(column)={cellwidth=2in};
define 'assigned_broker_manager'n / left "Broker Manager" style(column)={cellwidth=2in};
define 'agent_individual_writing_number'n / left "Agent Writing Number" style(column)={cellwidth=2in};
define 'Agency Name'N / left "Agency Name" style(column)={cellwidth=2in};
proc sql;
create table READYTOTRAIN2 as
select distinct
* from READYTOTRAIN
where 'toh_agency'n = "&rep.";
ods excel options(sheet_name="Ready To Train" autofilter='all')
proc report data = READYTOTRAIN2 headskip missing /*style=monochromeprinter*/ split='|'
style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];
columns 'Agent Name'N toh_agency assigned_broker_manager SALS_AGET_CD 'Agency Name'n ;
define 'Agent Name'N / left "Agent Name" style(column)={cellwidth=2in};
define 'TOH_Agency'n / left "TOH Agency" style(column)={cellwidth=2in};
define 'assigned_broker_manager'n / left "Broker Manager" style(column)={cellwidth=2in};
define 'agent_individual_writing_number'n / left "Agent Writing Number" style(column)={cellwidth=2in};
define 'Agency Name'N / left "Agency Name" style(column)={cellwidth=2in};
