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.
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.
proc sort data=sashelp.shoes out=shoes;
where region in ('Asia', 'Canada', 'Pacific');
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.
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.
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=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:
So MINIMAL gets you the most savings and the rest are about the same.
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.
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)
------------------------------ End of Technical Information ------------------------------
Message was edited by: SamuelG.
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.