11-01-2014 07:58 PM
Consider a scenario where SAS 9 isn't available - yes some companies unfortunately don't have access to the new stuff:-(
Is it possible to create a ODS excel output with multiple worksheets in the same file. I am trying to create one sheet for proc reg results and want the graphs to appear in another worksheet in the same file?
11-01-2014 10:44 PM
when you use ODS, you are creating ASCII text files (either HTML or XML) that Excel knows how to open and render. The issue is that for the HTML standard, I HTML file = 1 sheet in 1 workbook. It doesn't matter if the HTML file contains 5 different tables or 1 table -- everything in the HTML files goes into 1 sheet in an Excel file.
ODS HTML was available with SAS 8; ODS MSOFFICE2K was available with SAS 9. What version of SAS do you have? That will make a difference. There is a way to create multiple different HTML files -- so for example, put your PROC REG output in one file and put your Graph output in another HTML file. Then you need to make a separate HTML file that references the other 2 files. This is done with a bit of Microsoft HTML/XML hybrid HTML definition. Like this:
-Main.HTML has the HTML tags necessary to point to the other files
----REG.HTML Proc REG output
----GRAF.HTML Graph output
Then, assuming you have Excel open MAIN.HTML (Excel 2000 or higher), you will get a multi-sheet workbook created from the 2 separate HTML files.
This is why it is so much easier to use TAGSETS.EXCELXP -- the multiple HTML file technique is not quite so graceful.
11-02-2014 06:37 AM
Thanks for the detailed explanation. I know understand how ods works with excel. I will try using tag sets. The only issue i have is that some of my team still have SAS 8 installed and hence they won't be able to use the code.
I had another thought - is it possible to control the spacing between different procs in ods excel. I know that tag sets has a spacing and skip line option but is there something remotely similar in SAS 8 where:
- I can delete rows
- Control the fact that between proc print and proc reg there is only 1 line and a row break (line) that doesn't appear
Thanks a lot
11-02-2014 11:37 PM
I don't understand what you mean by "delete rows" -- ODS only takes what you send it as output and creates an output file. Your procedure of choice would be deleting rows. As far as I understand ODS, I really can't envision how you would ever delete rows. So this is not something that is specific to ODS.
ODS HTML, in SAS 8 created "vanilla" HTML 3.2 tags; ODS HTML, in SAS 9 creates "vanilla" HTML 4.0 tags. Microsoft Excel really likes HTML 3.2 tags and really doesn't like HTML 4.0 tags. That's why/how the MSOFFICE2K destination/tagset got created. When Microsoft invented their own form of HTML tags in Office 2000, ODS developers created the MSOFFICE2K destination/tagset in order to create Microsoft 2000 HTML tags. Then when Microsoft moved to XML in Office 2003, the TAGSETS.EXCELXP destination was created to generate that type of markup language (Office 2003 Spreadsheet Markup Language).
I am confused by your new request to control the number of "lines"/rows between 2 procedure outputs. I thought the whole point of your original posting was that you wanted multiple worksheets in 1 workbook. So that would mean the number of "lines" between PROC PRINT and PROC REG would be irrelevant because they each would be put into a separate sheet. However, to answer your question, I believe that TAGSETS.EXCELXP has more suboptions for controlling the number of linesrows between output reports than the regular HTML-based destinations.
Of course, one difference is that if you get automatic graphics from PROC REG, the output WOULD go to HTML destinations, but would NOT go to TAGSETS.EXCELXP, since that destination does not support graphic images.
The bottom line is that the kind of HTML written by ODS in SAS 8 is different than the type of HTML written in SAS 9.