SAS: Report to Excel

Friday. January 17, 2020 - 3 mins

Overview

ODS: TAGSETS.EXCELXP VS. EXCEL
PROC REPORT

ODS

ODS (Output Delivery System): delivers output in a variety of formats, including HTML, Rich Text Format (RTF), PostScript (PS), Portable Document Format (PDF), and SAS data sets.

Traditional SAS output: designed for line–printer

Basic ODS syntax:

ODS TRACE ON </<options>>; 
	ODS <destination> <FILE=filename>; 
	ODS OUTPUT output-object-name=SAS-data-set-name; 
	ODS <destination> SELECT output-object-name|ALL|NONE; 
		... PROC ...
	ODS <destination> CLOSE;  
ODS TRACE OFF;

Destinations: ODS can be used to route quality presentation files to various destinations, including LISTING (default), HTML, RTF, PRINTER, and PDF.
Object: Output objects are created by ODS to store the formatted results of most SAS procedures.
Style: Styles define the presentation attributes of a report, such as font and color.

Very often, we report SAS tables to Excel. In this case we can utilize ODS TAGSETS.EXCELXP and ODS EXCEL.

1. ODS Excel

Starting in SAS 9.4 maintenance release 3, ODS Excel destination creates .xlsx files that can be used with Microsoft Office 2010 or later. ODS Excel Formats With SAS format, such as comma7..

Syntax:

ods _all_ close;
ods excel file="/myshare/sample.xlsx" style= <style name> options(<option1 option2>); /*Opens Excel*/
...
ods excel close; /*Closes Excel */

Style

You can view the list of styles that are available at your site by submitting the statements below:

proc template;
     list styles;
run; quit;

2. ODS TAGSETS.EXCELXP

ExcelXP tagset creates a Microsoft XML spreadsheet file, which can be used with Excel 2002 and later. ExcelXP tagset does not create a native Excel format (.xlsx). ExcelXP tagset Formats With TAGTTR, i.e. TAGATTR = <attribute>.

Syntax:

ODS TAGSETS.EXCELXP PATH="/path/" file="file.xls" style=<style> options(<option1> <option2>)
options(<option3>);

To see references to Suboptions, submit the following code:

ODS tagsets.excelxp file="test.xml" options(doc="help");

Example: TAGGATR Formats preserve comma’s & trailing zeroes

ods tagsets.excelxp path="/path/" file="excelxp.xls" style=printer options(header_data_associations="yes" autofit_height='yes')
options(embedded_titles="yes" embedded_footnotes='yes' absolute_column_width="37,9,9");
proc report data=section1 nowd spanrows split='|' style(header)=[fontweight=bold fontsize=10pt font=(Times, 8pt)];
column name ("Special Census" number pc);
	define name / "Subject" f=$table1f. font=(TimesNewRoman, 10pt)];
	define number / 'Number' style(column)=[tagattr="format:#,##0" font=(TimesNewRoman, 10pt)] ; 
	define pc / 'Percent' style(column)=[tagattr="format:##0.0" font=(TimesNewRoman, 10pt)] ;

Proc Report

To control the report in an Excel Workbook, we can utilize ODS combined with PROC REPORT. The SAS paper Unleash the Power of PROC REPORT with the ODS EXCEL Destination has very detailed examples. Here I list some common used demos.

Demo: Multiple Worksheets

ods tagsets.excelxp path="/path/" file="test.xls" style=<style> options(<option>)
ods tagsets.excelxp options(sheet_name = 'Sheet 1');
	proc report;
	...
	run;
	
ods tagsets.excelxp options(sheet_name = 'Sheet 2');
	proc report;
	...
	run;
ods tagsets.excelxp close;

Example: Cell Merge
SPANROWS option works on column that uses GROUP or ORDER.

ods excel file="&file" options(sheet_name="test");
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold vjust=m}~SAS Community";
ods text="~S={font_size=14pt font_weight=bold vjust=m}";

proc report data=sashelp.class nowd spanrows ;
     column ('~S={foreground=purple}Measures' weight height)  
            ('~S={foreground=green}Information' name age sex);
     define sex / order style(column)={vjust=c just=c};
run;

ods excel close;

Demo: Transpose

proc report data=&data nowd;
     column league team,captain;
     define league / group;
     define team / across;
     define captain / display;
run;

Reference

Extra:

Zhijian Liu

A foodaholic