Tag: ods listing

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;