Generating excel tabs

deleted_user — Fri, 10 Aug 2007 19:46:48 GMT

Hi,
How do I generate separate ODS html outputs as different tabs in an excel file.

Example:
-------------
ODS html file = 'C:\abe\dee\xyz\123.xls';
proc print data=something;
run;
ods html close;

ODS html file = 'C:\abe\dee\xyz\789.xls';
proc print data=else;
run;
ods html close;

Now, instead of these ODS statements generating two separate xls files, I want both the outputs in different tabs of the same excel file.

Thanks in advance for your help
- Einnor

Re: Generating excel tabs

Cynthia_sas — Fri, 10 Aug 2007 22:51:28 GMT

Hi:
This is a good example of when you might consider a change to using the EXCELXP tagset:
[pre]
ODS tagsets.excelxp file = 'C:\temp\multsheet.xls';
proc print data=sashelp.class;
run;

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

proc freq data=sashelp.shoes;
tables region;
run;
ods tagsets.excelxp close;

[/pre]

That because when you use Spreadsheet Markup Language (Microsoft's XML specification for a Workbook defined in XML tags), there is one <Workbook> tag in the XML file and every worksheet is contained within separate <Worksheet> tags. The ODS developers followed the Microsoft specification -- so by default, when you switch to TAGSETS.EXCELXP to create your XML file, every procedure invocation that you have inside the ODS "sandwich" becomes a separate Excel worksheet tab in one workbook. ODS puts in the right combination of <Workbook> and <Worksheet> tags

On the other hand, by Microsoft design/spec., when you create an HTML file, every HTML file becomes a separate Workbook -- unless you do something extra to link the HTML files together and store them in the same physical directory and have a separate external XML file that describes the files that get linked together. This is a more complicated method to get multiple tabs, but it is what you used to have to do in SAS 8 before TAGSETS.EXCELXP was available. That method is described here:
http://support.sas.com/rnd/base/topics/excel/multisheet_excel_post.ppt

For more information on ODS MARKUP and TAGSETS.EXCELXP, refer to ODS MARKUP resources site:
http://support.sas.com/rnd/base/topics/odsmarkup/

and this paper has a good introduction to the topic:
http://support.sas.com/rnd/base/topics/odsmarkup/Paper_227-2007_ODS_Office.pdf

cynthia

topic Generating excel tabs in ODS and Base Reporting

Generating excel tabs

Re: Generating excel tabs