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: Batch Macro Variable Assignment by Class and Application in Dynamic Labeling

Set up “Class” table for Proc Tabulation.  “Class” table can force missing value in the table so the layout of the combination of class variables will not change due to 0 counts of the frequency of the variables.

data class;
do rank= 1, 2, 3;
do area = '0', '1';
do gender = 'F', 'M';
output;
end;
end;
end;
run;

Output Proc Tabulate summary to data set using “Class” layout. “Layout” data set contains frequency counts by rank by area and by gender.

proc tabulate  data = data out = layout missing classdata = class;
class rank area gender ;
var count;
table rank*(area="" all), (gender all)*count*(sum="");
run;

Add customary titles by rank by area in the “Layout” data set. Assign counts and titles to macro variables.

data layout;
set layout;
length varname $11 sum $2 title1 $60 title2 $60 vartitle1 $12 vartitle2 $12;
if count_sum = . then count_sum = 0;
sum = put(count_sum, 2.);
drop _TYPE_ _PAGE_ _TABLE_;
if gender = '' then gender = 'N';
if rank = 1 then title1 = "2017 Assistant Professors/Lecturers";
if rank=2 then title1 = "2017 Associate Professors/Lecturers";
if rank =3 then title1 = "2017 Fall Professors/Senior Lecturers";
if area = 0 then title2 = "General Arts"; 
if area = 1 then title2 = "Engineering";
if area = '' then do;
title2 = "All Areas of Specialization";
varname ="rank"||trim(left(rank_cd))||gender;
vartitle1 = "rank"||trim(left(rank_cd))||'t1';
vartitle2 = "rank"||trim(left(rank_cd))||'t2';
end;
else do;
varname = "rank"||trim(left(rank_cd))||"area"||trim(left(area))||gender;
vartitle1 = "rank"||trim(left(rank_cd))||"area"||trim(left(area))||'t1';
vartitle2 = "rank"||trim(left(rank_cd))||"area"||trim(left(area))||'t2';
end;
call symput(varname, sum);
call symput (vartitle1, title1);
call symput (vartitle2, title2);
run;
options symbolgen;

Application
Print the regression line by rank by area with corresponding titles and insert counts information of total observation and by gender on the respective chart. The “Inset” statement will translate the macro variables into format like N=75(46F/29M) and put into a text box inside of the axes of the plot.

ods pdf file="c:\test.pdf";
%macro plots;
%do j = 1 %to 3;
%do i = 0 %to 1;
title1 height=14pt "&&rank&j.area&i.t1";
title2 height=12pt "&&rank&j.area&i.t2";
proc sgplot data=glm_out (where=(rank=&j. and area = "&i.")) ;
scatter x=experience y=salary / group=gender grouporder=ascending  name='plot' markerattrs=(symbol=circlefilled) ;
series x=experience y=yhat / name='predict' legendlabel='ln(Predicted Sal)' lineattrs=(color=blue ) transparency = 0.5 ;
inset "N=&&rank&j.area&i.N (&&rank&j.area&i.f.F/&&rank&j.area&i.m.M)"  / position = bottomright  BORDER;
run;
quit;
%end;
%end;
%mend plots;
%plots
Ods pdf close;

SAS: Read files in folder and compile list of filenames

In a prvious post, “Filename: Create SAS dataset of folder hierarchical structure and file list“, I have described a way to extract filenames in a folder or subfolder to a file. Here is a different way of doing it.

Reference: Reading and Processing the Contents of a Directory by Ben Cochran, The Bedford Group, Raleigh, NC. https://www.mwsug.org/proceedings/2012/S1/MWSUG-2012-S128.pdf

The following SAS codes get the filenames from L:\DaraFiles directory and pass them to a macro variable called varlist.

%let varlist =;
data null;
rc =filename("mydir", "L:\DataFiles");
did = dopen("mydir");
if did > 0 then do;
num =dnum(did);
do i = 1 to num;
fname =dread (did, i );
put fname = ;
call symput('varlist', trim(resolve('&varlist'))||' '||trim(fname));
end;
end;
run;
%put &varlist.;

Key Functions:

• filename: assign the directory to a alias.
• dopen: open the directory and get the directory identifier;
• dnum: get the number of member in the directory;
• dread: get the filenname of the directory member;

SAS: Compile varlist and run report for each variable

Recently, I found Proc Sql can allow you to create macro variable with long string values separated by either space or other delimiters directly without extra step for table generation and null data step. Here is the modify codes.

proc sql;
select distinct varname into :varlist separated by ' ' from table;
quit;
%put &varlist.;

Sometime the macro variable is created within a macro, then the macro variable will be just local macro variable and it will not be available beyond the macro statement. To force the macro variable to be available in the global environment, using %Global.

%macro new;
proc sql;
select distinct varname into :varlist separated by ' ' from table;
quit;
%global varlist;
%mend new;
%put &varlist.;

If you would like the varlist items to be wrapped with quotation and separated with comma, then use the following code.

%macro new;
proc sql;
select distinct '"'||varname||'"' as temp into :varlist separated by ', ' from table;
quit;
%global varlist;
%mend new;
%put &varlist.;

If the same macro variable is defined or used in multiple macros, making sure the macro variable is set at the global level, because macro variables are local at default. You might get error message like below if the macro variable is not forced to be global variable.

ERROR: Attempt to %GLOBAL a name (NAME) which exists in a local environment.

Here is an example of use previously created macro variable in another macro.

%macro del;
%global varlist;
proc datasets nolist;
delete &varlist;
run;
%mend del;

previous reference: http://www2.sas.com/proceedings/sugi30/028-30.pdf

proc sql;
create table varlist as
select distinct varname from table;
quit;

%let varlist =;

data _null_;
set varlist;
call symput('varlist', trim(resolve('&varlist'))||' '||trim(varname));
run;

null is a special data set name, although sas doesn’t create a data set called “_null”. It allows sas to carry out commands in the data steps.  There is no output data set.

Omitting value in the %let syntax will produce a null value for the macro variable.

resolve function: resolve the value of macro variable in data step.

CALL SYMPUT  assigns value produced in a DATA step to macro variable(S).