This is the code
%let borderfmt = %nrstr( if CATEGORY IN ('CAT1') then Call Define (_COL_,'STYLE','STYLE=[borderbottomcolor=black ]'); else if CATEGORY IN ('CAT') then Call Define (_COL_,'STYLE','STYLE=[bordertopcolor=black ]'); );
%macro report_page1( inTable );
proc report data=&inTable split='#' spanrows
style(report)=[outputwidth = 100% cellspacing=0 cellpadding=2 frame = hsides rules=groups font=(Arial, 8pt, bold)]
style(header)=[foreground=white background=CX0066a4/*00669A*/ font=(Arial, 9pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white NOBREAKSPACE=ON ]
style(column)=[font=(Arial, 8pt) foreground=black background=CXFFFFFF VJUST=C bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ];
Columns
CATEGORY
METRIC
("Previous Weeks"
P6W
P5W
P4W
P3W
P2W
PW
CW
)
(
"WTD"
WTD
)
("WOW"
WOW
)
("MONTHLY"
P3M
P2M
PM
)
("MTD"
MTD
)
("YTD"
YTD
)
;
Define CATEGORY / order=data "CATEGORY";
Define METRIC / order=data "METRIC";
Define P6W / CENTER "&P6W" format = comma12.;
Define P5W / CENTER "&P5W" format = comma12.;
Define P4W / CENTER "&P4W";
Define P3W / CENTER "&P3W";
Define P2W / CENTER "&P2W";
Define PW / CENTER "&PW";
Define CW / CENTER "&CW";
Define WOW / CENTER "WOW PCT";
Define P3M / CENTER "&P3M";
Define P2M / CENTER "&P2M";
Define PM / CENTER "&PM";
Define MTD / CENTER "&CM";
Define YTD / CENTER "YTD";
Compute METRIC; &color; &fontfmt; &borderfmt; endcomp;
Compute CATEGORY; &colorcat; &catfontfmt; &borderfmt; endcomp;
Compute WOW; &borderfmt; endcomp;
Compute CW; &borderfmt; endcomp;
Compute WTD; &borderfmt; endcomp;
Compute PW; &borderfmt; endcomp;
Compute P2W; &borderfmt; endcomp;
Compute P3W; &borderfmt; endcomp;
Compute P4W; &borderfmt; endcomp;
Compute P5W; &borderfmt; endcomp;
Compute P6W; &borderfmt; endcomp;
Compute PM; &borderfmt; endcomp;
Compute P2M; &borderfmt; endcomp;
Compute P3M; &borderfmt; endcomp;
Compute MTD; &borderfmt; endcomp;
Compute YTD; &borderfmt; endcomp;
run;
%mend report_page1;
/*CREATE EXCEL PACKAGE TO GO OUT*/ /*%let report_dir = %sysfunc(pathname(work));*/
DATA _NULL_; ENAME_DT = PUT(DATE(),DATE9.); ENAME = STRIP(("REPORT_" ||ENAME_DT||".xls")); CALL SYMPUT("ENAME", ENAME); RUN; %LET OUTPUT_PATH = /data/bi/data/inbox/;
ODS LISTING CLOSE; ods tagsets.ExcelXP style=sasweb path= "&OUTPUT_PATH" file= "&ENAME" Style=Printer; ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='10,20,5,5,5,5,5,5,5,5,7,7,7,7,7'); %report_page1(BO_WEEKLY_RESULTS); ods tagsets.ExcelXP close;
... View more
