01-24-2012 09:28 AM
Hi, I'm trying to send over the output form 4 proc tabulates into the same Excel file, but into 4 separate worksheets. Using ODS html body. Any way on how to do this?
Options Missing = '0';
ods html body = C:\Excel file.xls' style=minimal;
options nodate nocenter pageno=1;
01-24-2012 02:45 PM
When you use ODS HTML (or any of the HTML-based destinations) it is the default for Excel to render your single HTML file as a single worksheet. This is the Microsoft rule for rendering HTML. If you use ODS MSOFFICE2K_X destination, you would need to create 4 separate HTML files, one for each TABULATE and then pass the names of those 4 files into your ODS MSOFFICE2K_X invocation in order to create a multi-sheet workbook using HTML-based methods.
On the other hand, if you used TAGSETS.EXCELXP as your destination you would automatically get a separate worksheet for each TABULATE.
The instructions for using ODS MSOFFICE2K_X destination can be found here:
When you use ODS MSOFFICE2K, ODS MSOFFICE2K_X or ODS HTML, you are not creating "true, binary" Excel (xls) files. You are merely creating HTML files that Excel knows how to open and render. Naming the file with a .xls extension merely "fools" the Windows registry into launching Excel when you click on the file icon. You can prove this to yourself by looking inside your file from ODS using NOTEPAD or a text editor. Then you will see the HTML tags inside the file. Excel is not very happy to render HTML4 tags, (which is the "flavor" of HTML created by ODS HTML)...it is better to use ODS MSOFFICE2K, ODS MSOFFICE2K_X or ODS TAGSETS.EXCELXP -- all of these destinations either create HTML files or XML files that conform to the Excel specification.