BookmarkSubscribeRSS Feed
GreggB
Pyrite | Level 9
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.
9 REPLIES 9
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
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!
stateworker
Fluorite | Level 6
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.
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
GreggB
Pyrite | Level 9
thank you for your help.
deleted_user
Not applicable
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;
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1828 views
  • 0 likes
  • 4 in conversation