ods tagsets.excelxp Archives

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

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: Tagset.Excelxp title and footnote setup

A. Multiple Titles/Footnotes

title
title2
title3
footnote
footnote2
footnote3

B. Alignment: default is center.

justify=

C. Font

f=

D.Bold/Italic

bold italic

E. Font size

h=

F. Example

title1 f='Calibri' h=14pt 'Report Title';
title2 "Report Title1";
title3 "Report Title2";
footnote "Prepared by First Last on %sysfunc(date(),worddate.)";
footnote2 justify=left h=8pt bold "Note:";
footnote3 justify=left h=8pt italic "footnote3";

SAS: Output Table to Excel Using Tagsets.excelxp

Frequently we need to output SAS results to Excel. With tagsets.excelxp, we will have the flexibility to create multiple sheets; define worksheet name(s) and column width; contrl titles and footnotes on each sheet.

Here is the basic tageset.excel for the Excel workbook.

ods listing close;
ods tagsets.excelxp path = "" file = "" style =
options (embedded_titles='' embedded_footnotes='' orientation='' sheet_name ='' pages_fitwidth='' pages_fitheight='' autofit_height=''
width_fudge="" absolute_column_width= "");
ods tagsets.excelxp close;

If the individual worksheet has different setting, put the following statement before the corresponding SAS procedure that generates the content for the worksheet.

ods tagsets.excelxp options (sheet_name='RespHome');

Use sheet_name and sheet_interval to generate multiple sheets by groups defined in the procedure in the same Excel workbook.

... sheet_name='#byval1' sheet_interval="bygroup" ...

sample code:

ods listing close;
ods tagsets.excelxp path = "C:\SAS\" file = "sum.xls" style = journal
options (embedded_titles='yes' embedded_footnotes='yes' orientation='portrait' pages_fitwidth='1' pages_fitheight='1' autofit_height='yes'
width_fudge=".0625" absolute_column_width= "100");
title1 'Title1';
title2 "Title2";
footnote "Prepared by First Last @dept on %sysfunc(date(),worddate.)";
ods tagsets.excelxp options (sheet_name='homeresp');
proc tabulate data=count;
class home /order= formatted ;
class reponsible;
var count;
table (home="" all="Total"), (responsible="Responsible Faculty" all="Total")*(count="")*(sum="" )*f=10.3/ box = "Home Faculty" misstext = '0.000';
run;
ods tagsets.excelxp options (sheet_name='RespHome');
proc tabulate data=count;
class responsible /order= formatted ;
class home ;
var count;
table (responsible="" all="Total"), (home="Home Faculty" all="Total")*(count="")*(sum="" )*f=10.3/ box = "Resonsible Faculty" misstext = '0.000';
run;
ods tagsets.excelxp close;