Hi all,
I’m using ods tagsets.excelxp to create a formatted excel report. Right now the number of times I have to run the report is based off of a variable called Test_Number (numeric values 1, 2, 3, 4….). I have a macro variable Test which I have to hard code for each pass. What I would like to have is a macro that runs through all the possible values in test and outputs to a multisheet workbook; So, each sheet is a test in the workbook . I have used ods tagsets.excelxp to produce multisheet workbooks before but I can’t figure out how to write a macro which produces the number of sheets based on the values in a variable. Below is my program.
Any assistance will be greatly appreciated. Thanks!
/***Choose Test Number*****/
%let Test= 1 ;run;
ods listing close;
options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in;
ods tagsets.excelxp file="C:\Users\Desktop\Test Table.xml"
style=minimal
options(sheet_interval='none' ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' PAGE_ORDER_ACROSS='yes');
proc report data= title nowd ;
column Test_Title;
define Test_Title / display
style(column)={ just=right font_size=10pt cellwidth=1.7in}
style(header)={ just=center foreground=white background=white};
where Test_Number=&Test;
run;
proc report data=Label nowd
style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}
Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
column dum Control Test;
define dum / display
style(column)={ just=right font_size=10pt cellwidth=1.4in}
style(header)={ just=center foreground=white background=white};
where Test_Number=&Test;
run;
proc report data=Final nowd
style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}
Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
column
KPI Control Test lift prob Statistically_Significant_95 percent_Significant;
define KPI / display
style(column)={ just=right font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD cellwidth=1.6in}
style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Control / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in background=#FFCC99}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Test / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define lift / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define prob / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Statistically_Significant_95 / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=1.in FONT_WEIGHT =BOLD}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Percent_Significant/ display
style(header)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' background=cxcccccc}
style(column)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' cellwidth=.9in};
where Test_Number=&Test;
run;
title;
ods _all_ close;
See the second example in the Call Execute documentation:
SAS(R) 9.2 Macro Language: Reference
Make sure to include your ODS Open/Close before/after your call execute data step.
Thanks Rezza! I checked your example and I'm still confused with the syntax. I'm getting this error
SYMBOLGEN: Macro variable TEST resolves to
MPRINT(TESTOUT): where Test_Number=;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(TESTOUT): run;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.
Also, I use multiple datasets in this program - title , Label and Final. I'm not sure how to incoperate all 3 into the macro.
Ant additional feedback will be greatly appreciated. Thank you!!!
This is what I tried:
%macro testout(Test);
options
rightmargin=.1in
leftmargin=.1in
Bottommargin=.1in
topmargin=.1in;
ods tagsets.excelxp file="C:\Users\Desktop\Test Table.xml"
style=minimal
options(sheet_interval='none' ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' PAGE_ORDER_ACROSS='yes');
proc report data= title nowd ;
column Test_Title;
define Test_Title / display
style(column)={ just=right font_size=10pt cellwidth=1.7in}
style(header)={ just=center foreground=white background=white};
where Test_Number=&Test;
run;
proc report data=Label nowd
style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}
Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
column dum Control Test;
define dum / display
style(column)={ just=right font_size=10pt cellwidth=1.4in}
style(header)={ just=center foreground=white background=white};
where Test_Number=&Test;
run;
proc report data=Final nowd
style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}
Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
column
KPI Control Test lift prob Statistically_Significant_95 percent_Significant;
define KPI / display
style(column)={ just=right font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD cellwidth=1.6in}
style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Control / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in background=#FFCC99}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Test / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define lift / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define prob / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Statistically_Significant_95 / display
style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=1.in FONT_WEIGHT =BOLD}
style(header)={just=center font_size=10 pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=cxcccccc};
define Percent_Significant/ display
style(header)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' background=cxcccccc}
style(column)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' cellwidth=.9in};
where Test_Number=&Test;
run;
title;
%mend testout;run;
ods listing close;
data _null_;
set Final;
call execute(%testout);
run;
ods _all_ close;
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.