Multiple Multi-Sheet EXCEL Workbooks Using ODS
Wanda Moseley Mercer, SAS Consultant, Culver City, CA
3
ABSTRACT
This paper will provide a practical approach to the implementation of techniques as outlined in the SUGI 28 Paper 12-28 by Chevell Parker “Generating Custom Excel Spreadsheets using ODS “. Chevell’s paper details how to generate files that can be read with Microsoft Excel using the Output Delivery System. This presentation will show how we implemented and extended the techniques Chevell documented, including an Excel workbook containing over 60 worksheets generated entirely by SAS programming statements.
Microsoft EXCEL has become an almost universal language of data communication at all organizational levels and degrees of computer technical proficiency. It is particularly valued by non-technical consumers of data. In the last year, we have been able to provide our clients with multiple EXCEL workbooks containing custom-named tabs using this approach. This technique has proven to be an easy way to organize and present large quantities of preliminary and summary data in a format that gives the user an opportunity to work with, explore and re-present the data provided to the user. For example, if a worksheet contains tabular numbers, users can then graph these numbers in Excel.
The presentation will concentrate on a practical review of the SAS code that structures and generates Excel files.
We have implemented these systems using SAS Versions 8.2 and 9.1, the Microsoft 2000 and XP operating systems, and the Microsoft Office style properties that apply to Excel 2000 and higher. This presentation will be of interest to anyone who needs to deliver data directly from SAS to Excel, particularly if you were not able to attend Chevell’s SUGI presentation.
Introduction
In many business and research environments, it is often necessary to distribute timely analyses of data to multiple departments, service units, vendors, clients and the like. One of the most universal formats for reports containing extensive tables and charts is Microsoft EXCEL. This MS Office product has become, more or less, a universal operational reporting vehicle that is familiar and useful to any organization with the most basic automation capabilities.
In the Advanced Tutorial “Generating Custom Excel Spreadsheets using ODS” presented at SUGI 28, we were shown how to create multiple worksheet EXCEL workbooks automatically in the SAS environment. The processes necessary to produce these spreadsheets are:
1) The creation of HTML files containing the contents of each spreadsheet in the workbook using ODS, and
2) the creation of an XML tagset using PROC TEMPLATE to specify the order and label for each worksheet within the workbook.
I have taken this technique a step farther to include an approach for creating multiple workbooks containing the analyses of subsets of the data for distribution to individual business units.
working in the windows environment
The master directory for this project is symbolically named ReportLocation in this example. This directory is populated by as many subdirectories as necessary for the distribution of the reports. An individual subdirectory is symbolically named Subsetfolder and will define the current folder for a particular subset (in this case a hospital) as in the example given below. If necessary, these subdirectories can be created dynamically in a totally data-driven system.
The subdirectories will contain all of the pieces of the puzzle necessary to create a multi-sheet workbook. The content for each worksheet will be in the form of HTML files with their associated graphic files (if any). The small EXCEL file in this directory is initially just a tagset defined by the ODS MARKUP command. The tagset contains XML events that are coded in PROC TEMPLATE.
When the creation of the subfolder contents is complete and the markup file (in the form ___.xls) has been created, Microsoft EXCEL will do all of the rest of the work. Double click on the markup file and the completed workbook containing all of the worksheets with all of the generated content will be available. The only manual step in this process is to save the HTML version of the workbook as an EXCEL spreadsheet (FILE || SAVE AS).
Lets look at the SAS code necessary to implement these processes:
Using SAS/ODS for worksheet content
The HTML destination is used to produce individual files containing the content of each worksheet within the workbook. In the following example, the output from multiple PROC TABULATE steps has been sent to a single HTML file. The XML code (underlined) included in the title and footnote statements are necessary to center these lines within a 11 column span of the EXCEL worksheet we wish to create. XML code in the style specifications is necessary to format the numbers in the individual cells of the worksheet:
/* Define the HTML Destination */
%let TemplateUsed=SASWEB;
ods html file="Report Destination.htm" style=&TemplateUsed.;
/* Produce Tables for a Single Worksheet */
title ‘ ‘;
footnote ' ';
title1 "<td align=center colspan=11<font size=3<b>Report Title</b</font</td>";
proc tabulate data=Table1data missing order=formatted;
class Quarter Year ClassVar;
var SumVar;
table ClassVar=' ',
Year=' '*Quarter=' '*SumVar=' '*sum=' '*
[style={htmlstyle="mso-number-format:\#\,\#\#0" background=tblbk.}]
/box=”Table 1 Title”;
run;
title ' ';
proc tabulate data=Table2data missing order=formatted;
class Quarter Year ClassVar;
var SumVar;
table ClassVar=' ',
Year=' '*Quarter=' '*SumVar=' '*sum=' '*
[style={htmlstyle="mso-number-format:\#\,\#\#0" background=tblbk.}]
/box=”Table 2 Title”;
run;
footnote1 "<td align=center colspan=11<font size=2<b>Footnote</b</font</td>";
proc tabulate data=Table3data missing order=formatted;
class Quarter Year ClassVar;
var SumVar;
table ClassVar=' ',
Year=' '*Quarter=' '*SumVar=' '*sum=' '*
[style={htmlstyle="mso-number-format:\#\,\#\#0" background=tblbk.}]
/box=”Table 3 Title”;
run;
/* Close the HTML Destination */
ods html close;
ods listing;
Using xml to modify excel
XML code is generated using PROC TEMPLATE. This code specifies the structure of the EXCEL workbook that is to be created. The ODS MARKUP file produced is an XML tagset. A tagset is a collection of events that, in this case, uses the template to associate the HTML files with the individual EXCEL worksheets.
* Create a template for EXCEL that puts output into multiple sheets;
proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc;
start:
put '<html xmlns:o="urn:schemas-microsoft-com:office: office"' NL;
put 'xmlns:x="urn:schemas-microsoft-com:office:excel"' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put '<meta name="Excel Workbook Frameset">';
finish:
put "<!--[if gte mso 9]<xml>" NL;
put "<x:ExcelWorkbook>" NL;
put " <x:ExcelWorksheets>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>First Worksheet Label</x:Name>" NL;
put " <x:WorksheetSource
HRef='c:\Path to Workbook\First HTML File.htm'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Second Worksheet Label</x:Name>" NL;
put " <x:WorksheetSource
HRef='c:\Path to Workbook\Second HTML File.htm'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " <x:ExcelWorksheet>" NL;
put " <x:Name>Third Worksheet Label</x:Name>" NL;
put " <x:WorksheetSource
HRef='c:\Path to Workbook\Third HTML File.htm'/>" NL;
put " </x:ExcelWorksheet>" NL;
put " </x:ExcelWorksheets>" NL;
put "<x:WindowHeight>5000</x:WindowHeight>" NL;
put " <x:WindowWidth>10380</x:WindowWidth>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL;
put "<x:ActiveSheet>0</x:ActiveSheet>" NL;
put "</x:ExcelWorkbook>" NL;
put "</xml<![endif]-->" NL;
put "</head>" NL;
end;
end;
run;
ods markup file="c:\Path to Workbook\EXCEL File Name.xls"
tagset=tagsets.test;
ods markup close;
creating a multiple workbook system
We are going to assume that a Datamart has already been created and that the “GenerateSubsetHTMLn” Macros have been written to produce the individual HTML files (using ODS) which will be mapped onto the worksheets in the workbook.
The location of all report folders is specified:
%let ReportLocation = C:\Reports Folder\;
This macro processes a unique workbook for a subset of the datamart based on the SubsetID parameter. The location of the particular subset report is specified by translating the parameter into an existing folder name:
%macro ProcessSubset(SubsetID=);
%* Select data for the workbook;
data WORKBOOKDATA;
set DATAMART datamart(where=(SubsetID = &SubsetID. or &SubsetID. = 99));
%* Resolve the folder where the output will be stored;
SubsetFolder = trim(put(&SubsetID,SubsetID.))||'\';
CALL SYMPUT('SubsetFolder',SubsetFolder);
run;
%* Call macro to produce the contents of each worksheet ;
%GenerateHTML(Disease=ALL);
%GeneratetHTML(Disease =Diabetes);
%GenerateHTML(Disease =CHF);
%GenerateHTML(Disease =Hypertension);
%GenerateHTML(Disease =Asthma);
%* Create a template for EXCEL that organizes the output onto multiple worksheets;
proc template;
.
.
.
run;
%* Create a tagset file that contains the XML code;
ods markup file="&ReportLocation.&SubsetFolder.SubsetReport.xls"
tagset=tagsets.test;
ods markup close;
%mend ProcessSubset;
This is the structure of the macros that generate the HTML files:
%macro GenerateSubsetHTML(Disease=);
%* Resolve the HTML file location where the output will be stored;
ods listing close;
%let HTMLfile =&Disease..htm;
ods html file="&ReportLocation.&HospitalFolder.&HTMLfile "
style=&TemplateUsed.;
%* Select data for the worksheet;
data WORKSHEETDATA;
set WORKBOOKDATA(where=(Disease = &Disease. or &Disease. = ‘ALL’));
;
run;
%* Generate HTML files here;
.
.
.
ods html close;
ods listing;
%mend GenerateSubsetHTML;
* Execute the macro for each workbook;
%ProcessSubset(SubsetID=99);
results
Workbook example produced by sample code:
References
Parker, Chevell. “Generating Custom Excel Spreadsheets using ODS”
SAS Institute
http://www2.sas.com/proceedings/sugi28/012-28.pdf
“Microsoft Office HTML and XML Reference”
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffxml/html/ofxml2k.asp
ACKNOWLEDGMENTS
Deborah Testa, Ph.D.
Seven of Nine Systems
SAS® Programming & Development
http://www.sevenofninesystems.com/
Contact Information
/ Wanda Moseley MercerIndependent Consultant
Culver City, California
(310) 815-9628
http://www.WandaMM.com
(download sample code from this site)
3