Help using Base SAS procedures

Exporting Multiple Worksheets to One Excel Workbook

Reply
Super Contributor
Posts: 279

Exporting Multiple Worksheets to One Excel Workbook

Can anyone recommend some documentation on how to export multiple sheets to a workbook? I want to create the same data set for 20 different schools and then export to Excel so that I have one workbook with 20 worksheets.
SAS Super FREQ
Posts: 8,866

Re: Exporting Multiple Worksheets to One Excel Workbook

Hi:
Your basic choices are:
1) Proc EXPORT
2) Libname Engine for Excel
3) ODS TAGSETS.EXCELXP

The difference is that #1 and #2 will create true binary Excel workbooks, without any formatting or font or color control. #3 method will create a Microsoft Spreadsheet Markup Language XML file that describes a workbook according to the Excel 2003 XML specification. One advantage of the #3 method is that you can use ODS styles to have some color and font control in your workbook.

#1 and #2 methods create files that can be opened with Excel 97 or later. #3 method creates a file that can only be opened with Excel 2002/2003 or later.

There have been many previous forum postings on these subjects and you can search at support.sas.com for examples. A few examples of the #3 method for ExcelXP are shown below to get you started. The first example will create a new sheet for every BY group. The second example will create a new sheet for every procedure step.

cynthia

[pre]

proc sort data=sashelp.shoes out=shoes;
where region in ('Asia', 'Canada', 'Pacific');
by region;
run;

ods tagsets.excelxp file='multsheet1.xml' style=sasweb
options(doc='Help');

proc print data=shoes;
by region;
run;
ods tagsets.excelxp close;

ods tagsets.excelxp file='multsheet2.xml' style=sasweb
options(doc='Help');
proc print data=sashelp.class(obs=5);
run;

proc print data=sashelp.shoes(obs=5);
run;

proc print data=sashelp.prdsale(obs=5);
run;

proc print data=sashelp.heart(obs=5);
run;
ods tagsets.excelxp close;

[/pre]
N/A
Posts: 0

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to Cynthia_sas
Cynthia,

I like the flexibility of the tagset approach, but NOT the size of the files it creates. What is the best style to use to minimize the size of the file created?

Thanks!
Contributor
Posts: 33

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to deleted_user
I use the tagset a lot and sometimes do get a file that is way too big as an xml file, but once it "finally" opens and I "save as" excel, it shrinks it down quite a bit. I haven't figured out how to adjust the formatting (or remove the formatting) for outputting, but I made myself a macro in Excel that will remove the shading, resize fonts, etc. etc. etc. for the type of file I most often export with the tagset.
N/A
Posts: 0

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to stateworker
Thanks. I just tried that (saving as a standard Excel file) and it does shrink down quite a bit. Although that is very useful to know, it is a bit disappointing that you have to go through such an extra step.
SAS Super FREQ
Posts: 8,866

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to deleted_user
Hi:
Well, XML is verbose and Microsoft XML for Spreadsheets is -very- verbose. So if you do resave as a binary Excel file or an Excel 97-2003 file, you should see some shrinkage -- after all, a binary file will be smaller than a plain ASCII text file.

Some styles you can try that are fairly plain:
style=minimal
style=journal
style=printer (although this has headers that are shaded in gray)

But, I just ran a test using the above styles on 100 obs from SASHELP.SHOES using PROC PRINT to make 1 worksheet in 1 workbook and the sizes are as follows:
SASWEB: 131KB
MINIMAL: 82KB
JOURNAL: 124KB
PRINTER: 126KB

So MINIMAL gets you the most savings and the rest are about the same.

cynthia
Super Contributor
Posts: 279

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to Cynthia_sas
thank you for your help.
N/A
Posts: 0

Re: Exporting Multiple Worksheets to One Excel Workbook

I've recently started to use the Libname method.
The nature of my work, I tend to end up with several datasets that have very few columns ( usually 5 or less), and with the Libname method you can export different datasets to the same worksheet. So, for example I could use Proc Export for 10 datasets and have 10 individual tabs in Excel, or I can use Excel Libnames and put 5 datasets on 2 Excel tabs.
Here is a quick sample of code. My only hangup is you have to "prepare" the Excel file by defining named ranges, but that is only a one time set.

data data1;
INPUT Month $ Loans Success_pct Avgprod Goal_pct ;
DATALINES;
Team1 120 .8921 2.45 1.032
Team2 91 .6734 1.12 .78422
Team3 187 .7454 2.01 .99456
;
run;

libname exbk excel 'C:\Temp\ExportExample.xls' ;
proc datasets lib=exbk;
delete Excel1;
quit;
data exbk.Excel1;
set work.data1; * Named Range you created in Excel;
run;
libname exbk clear;
N/A
Posts: 0

Re: Exporting Multiple Worksheets to One Excel Workbook

Hello,

I can't see the result as there's an error accuring. Here is the message I'm getting :

--------------------------------------------------------

Unable to read SAS Report file.

--------------------------------------------------------

Workbook

------------------------------ Technical Information Follows ------------------------------

[SAS.Report.Models]

Workbook

----------------------------------------------------

at SAS.Report.Models.XML.XMLParse.CreateDOM(Stream file, String fragment, Hashtable customObjectMap)
at SAS.Report.Models.XML.XML.ReadReport(Stream inputStream, Hashtable customObjectMap)
at SAS.Report.Models.XML.XML.ReadReport(FileInfo file, Hashtable customObjectMap)
at SAS.Report.Models.XML.XML.ReadReport(String filename, Hashtable customObjectMap)
at SAS.Report.Viewer.SASReportView.ReadReport(String reportFileName)
at SAS.EG.ProjectElementViews.SasReportView.OpenDocument()Workbook

------------------------------ End of Technical Information ------------------------------ Message was edited by: SamuelG.
SAS Super FREQ
Posts: 8,866

Re: Exporting Multiple Worksheets to One Excel Workbook

Posted in reply to deleted_user
Hi:
If you are using SAS Enterprise Guide to create your TAGSETS.EXCELXP output, the files will be created correctly. HOWEVER, Enterprise Guide wants to treat ALL XML files as though they are SASReport XML -- which is the wrong behavior for a TAGSETS.EXCELXP XML file. This behavior will happen no matter what you name your file.

This Tech Support Note explains the workaround:
http://support.sas.com/kb/32/924.html (essentially navigate to the file with Windows Explorer and open from there or open from Excel).

cynthia
Ask a Question
Discussion stats
  • 9 replies
  • 870 views
  • 0 likes
  • 4 in conversation