SAS: Tagset.Excelxp Double loop with multiple sheets output depend on dynamic parameter table

A. Purpose

The use of this SAS program is to produce excel files for each product, and within each excel product file, generate multiple sheets with different conditions, sheets names, titles.

B. Code

• Set up parameter table

options symbolgen;
data parmlst;
infile datalines delimiter = '!';
length  parm1 $60. parm2 $30. parm3 $40.;
input parm1 $ parm2 $  parm3 $ ;
call symput('parm1_'||left(_n_), parm1);
call symput('parm2_'||left(_n_), parm2);
call symput('parm3_'||left(_n_), parm3);
datalines;
engineSize le 3.5  ! EngineSize le 3.5! (Engine Size = $40,000)
;
run;

• Set up tagsets excel output macro

/* [S=] is [S=<parent>] */
%macro exlout (varname=, con=);
%let  i=1;
%do %while (%scan(&con., &i, ' ') ne );
%let dsn=%scan(&con., &i, ' ');
data &dsn.;
set sashelp.cars;
if &varname. = "&dsn."; 
count = 1;
run;
ods tagsets.Excelxp path= "X:\SAS\" file="Car_Profile_&varname._&dsn..xls" style=journal
options (embedded_titles='yes' embedded_footnotes='yes'  absolute_column_width='10,10,10,10' auto_height="no" );
%do j=1 %to 2;
%put &j;
ods tagsets.excelxp options (sheet_name="&&parm2_&j." sheet_interval='none');
title1 justify = left  height = 22pt bold "Car Profile (&varname. = &dsn.) by Specifics";
title2 justify = left  height = 20pt bold "Car Segment: &&parm3_&j. ";
proc tabulate data = &dsn.  S=[background=palg];
where &&parm1_&j.; 
class Type Make Origin engineSize Cylinders / S=[background=palg];
classlev Type Make Origin engineSize Cylinders / S=[background=palg];
var Horsepower MPG_City MPG_Highway Count/ S=[background=palg];
table Type all="Total"*[S=]
Make all="Total"*[S=]
Origin all="Total"*[S=]
EngineSize="EngineSize" all="Total"*[S=]
Cylinders all="Total"*[S=],
(Horsepower*Mean="" MPG_City="MPG City"*Mean="" MPG_Highway="MPG City"*Mean="" Count="N"*SUM="" )
/ box = [S=[background=palg]] ;
keyword all / S=[background=lilg];
run;
quit;
%end;
ods tagsets.excelxp close;
%let i = %eval (&i +1);
%end;
ods tagsets.excelxp close;
run;
%mend exlout;

• run macro

%exlout (varname=Origin, con=USA Europe);
%exlout (varname=Type, con=Sedan SUV);

C. Output

Car_Profile_Origin_Europe.xls

D. Key Coding Elements

• loop: %let i=1; %do %while (%scan(&con. &i, ‘ ‘) ne );
• loop: %do j= 1 %to 2;
• Colouring: S=[background=palg]; palg and lilg are different shades of green.
• macro variable assignment: call symput (‘parm1_’||left(_n_), parm1);
• Customization of excel sheets: data parmlst;

SAS: Input Raw data with different length of character variable

data map;
infile datalines delimiter=',';
length faculty $2 progname $90. facdesc $25.;
input  faculty $ progname $ facdesc $;
datalines;
AB, AB-Digital Media, , Faculty AB
AB, AB-Interdisc. Fine Arts, Faculty AB
CD,SC-Biology,Science, Faculty CD
;
run;

NOTE:

• Can’t use Input statement to set length for the variable.  Input statement only identify the character variables by putting $ after the variable name.
• Use ‘,’ to separate the value of the variable in the datalines; no need to put ‘;’ to end each dataline, just end the whole datalines section with one ‘;’.
• Put Length statement before the Input statement to set the desired variable length for the data needed input.

BENEFIT:

• No need to use “” for character values that contain spaces.
• No need to align the variables in datalines.