hi all:
Please advise this trivia 😊, it seems the output of excel sheet will be overwritten by the new proc report excel output.
Goal: Need to output multiple sheets to One excel file.
1. I need to first run multiple macros for different data sets (%mm) and then Output multiple final datasets (A,C) to one excel file- test.xlsx
2. &&var&i and d_&&var&i came from the %mm , which are used to identify difference for each variables between old and new version of the data set. (this &&var&i will resolve to different value based on %mm)
3.%sqlobs is the total number of variables in each dataset (for example A: has 22 variables)
Thank you,
purple
%mm(A, &_va1.,term,test);
/*-----------------------------------------------------------------------------------------------------* Step6. Output
*-----------------------------------------------------------------------------------------------------*/
title;
footnote;
ods results off;
ods listing close;
ods escapechar="^";
ods excel file="C:\Users\test.xlsx"
style=excel
options
(row_repeat="header"
frozen_headers="ON"
frozen_rowheaders="OFF"
ROWBREAKS_INTERVAL= "OUTPUT"
sheet_label=" "
embedded_titles="YES"
FitToPage="ON"
orientation="landscape"
flow="ROWHEADERS");
%macro rpt;
%let sqlobs=22;
ods excel options (sheet_name = "A");
proc report data=A nowd split='~' missing style(header)=[just=left]
style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]
style(column)=[font=(Arial, 9pt) bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt cellwidth=1in vjust=m]
style(header)=[font=(Arial, 9pt) bordercolor=#f0f0f0 borderstyle=solid borderwidth=1pt font_weight=bold foreground=white background=#ADD8E6 vjust=m];
%IF &num. > 0 %THEN %DO;
column subject recno cat %do i=1 %to &sqlobs.; &&var&i %end; %do i=1 %to &sqlobs.; d_&&var&i %end; ;
define subject / display "Subject ID" style(column)=[cellwidth=0.7in];
define recno /display order=data noprint;
define cat / display "Category ";
define a / display " " style(column) = [just = l cellwidth = 2.2in];
define b / display "" style(column) = [just = l cellwidth = .8in];
define c / display "";
%do i=1 %to &sqlobs. ;
define D_&&var&i / order=data noprint;
%end; ;
%do i=1 %to &sqlobs;
compute &&var&i;
if cat="New Record" then do;
call define("cat", "style", "style=[background=lightgreen]");
call define("&&var&i", "style", "style=[background=lightgreen]");
end;
else if cat="Deleted Record" then do;
call define("cat", "style", "style=[background=lightred]");
call define("&&var&i", "style", "style=[background=lightred]");
end;
endcomp;
compute D_&&var&i;
if D_&&var&i="Yes" then do;
call define("cat", "style", "style=[background=yellow]");
call define("&&var&i", "style", "style=[background=yellow]");
end;
endcomp;
%end;
run;
%END;
%ELSE %DO;
column x;
define x / display "";
run;
%END;
%mend;
%rpt;
ods excel close;
/*-----------------c-----------------*/
%mm(C, &_va3.,term,test);
title;
footnote;
ods results off;
ods listing close;
ods escapechar="^";
ods excel file="C:\Users\test.xlsx"
style=excel
options
(row_repeat="header"
frozen_headers="ON"
frozen_rowheaders="OFF"
ROWBREAKS_INTERVAL= "OUTPUT"
sheet_label=" "
embedded_titles="YES"
FitToPage="ON"
orientation="landscape"
flow="ROWHEADERS");
%macro rpt;
%let sqlobs=27;
ods excel options(autofilter="ALL" sheet_name="C");
proc report data=C nowd split="~" missing
style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]
style(column)=[font=(Arial, 9pt) bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt cellwidth=0.6in vjust=m]
style(header)=[font=(Arial, 9pt) background=#ADD8E6 bordercolor=#f0f0f0 borderstyle=solid borderwidth=1pt font_weight=bold foreground=black vjust=m];
%IF &num. > 0 %THEN %DO;
column subject recno cat %do i=1 %to &sqlobs.; &&var&i %end; %do i=1 %to &sqlobs.; D_&&var&i %end; ;
define subject / display "Subject ID" style(column)=[cellwidth=0.7in];
define co /display order=data noprint;
define seq / display noprint style(column)=[cellwidth=1.5in];
define et / display "" style(column)=[cellwidth=1.5in];
define rv / display "";
define es / display "";
%do i=1 %to &sqlobs. ;
define D_&&var&i / order=data noprint;
%end;
%do i=1 %to &sqlobs;
compute &&var&i;
if cat="New Record" then do;
call define("cat", "style", "style=[background=lightgreen]");
call define("&&var&i", "style", "style=[background=lightgreen]");
end;
else if cat="Deleted Record" then do;
call define("cat", "style", "style=[background=lightred]");
call define("&&var&i", "style", "style=[background=lightred]");
end;
endcomp;
compute D_&&var&i;
if D_&&var&i="Yes" then do;
call define("cat", "style", "style=[background=yellow]");
call define("&&var&i", "style", "style=[background=yellow]");
end;
endcomp;
%end;
run;
%End;
%Else %Do;
column x;
define x / display "";
run;
%End;
%mend;
%rpt;
ods excel close;
ods results on;
ods listing;
... View more
