The SAS Output Delivery System and reporting techniques

Multiple Excel Worksheets

Reply
N/A
Posts: 0

Multiple Excel Worksheets

I am using ods tagsets.ExcelXP and have been able to generate a report in excel with data on multiple tabs. The problem that I am runing into is that the user that I send it to is unable to pull up the report. Do you have to have SAS installed inorder to view a report using this? If so is there another way to output data onto multiple tabs without having to have SAS installed on your PC?
SAS Super FREQ
Posts: 8,744

Re: Multiple Excel Worksheets

Hi, Michelle:
TAGSETS.EXCELXP is a Microsoft Office Spreadsheet XML formatted file. It is just an ASCII text file. You can open the file with Notepad and if you make your SAS title something like "The Wombat Title" you should be able to search for the title in Notepad and see the markup tags that ODS uses for the title:
[pre]
<x:Header ssSmiley FrustratedtyleID="systemtitle" Data="The Wombat Title"/>
[/pre]

Then, just a bit below the title, you should start to see other tags that look familiar. For example, if I did a PROC PRINT on SASHELP.CLASS, the first row of data would be for Alfred and here's his row of table info marked up in Spreadsheet Markup Language:
[pre]
<Row ss:AutoFitHeight="1" ss:Height="15">
<Cell ssSmiley FrustratedtyleID="rowheader__r" ss:Index="1"><Data ss:Type="Number">1</Data></Cell>
<Cell ssSmiley FrustratedtyleID="data__l" ss:Index="2"><Data ss:Type="String">Alfred</Data></Cell>
<Cell ssSmiley FrustratedtyleID="data__l" ss:Index="3"><Data ss:Type="String">M</Data></Cell>
<Cell ssSmiley FrustratedtyleID="data__r" ss:Index="4"><Data ss:Type="Number">14</Data></Cell>
<Cell ssSmiley FrustratedtyleID="data__r" ss:Index="5"><Data ss:Type="Number">69.0</Data></Cell>
<Cell ssSmiley FrustratedtyleID="data__r" ss:Index="6"><Data ss:Type="Number">112.5</Data></Cell>
</Row>
[/pre]

You do NOT need SAS on the "opening" PC in order to open the file. However, you DO need Office/Excel 2002/2003 or HIGHER to open the file. If you have Office 97 or Office 2000, then you will NOT be able to open the file, or, if Excel does open it, you are likely to see just the Markup tags, as shown above. There's a chance that Office 2000 might open the file and show you some of the numbers, but if you put a lot of bells and whistles into the file, they may not work in Office 2000 -- since the Spreadsheet Markup Language XML was not "cooked" until Office 2002.

If you have Office 2007, you should be able to open the TAGSETS.EXCELXP file with no problems. You might get a security warning, especially if you named the file with a .XLS file extension -- because Office 2007 checks to see that the "inside" of the file matches the file extension. And, since this is technically an XML file, Excel gives you a warning on the use of .XLS as the extension. You have to respond yes to the warning in order to open the file with Excel 2007. That security check is a "feature" of Excel 2007and has nothing to do with SAS.

If you're emailing the file, some email servers don't like .XML files going back and forth, but again, that has nothing to do with SAS and more to do with security at the mail server level.

I'd recommend double checking the level of Excel that your person is using to open the file. He/she does NOT need SAS on their PC to open the file, as created by TAGSETS.EXCELXP.

cynthia
N/A
Posts: 0

Re: Multiple Excel Worksheets

Thank you for your help Cynthia!! That is a lot of good information! I checked with my user and she does have 2000. Does that mean that a report with multiple worksheets is impossible? Is there a different way to do this without tagsets?
SAS Super FREQ
Posts: 8,744

Re: Multiple Excel Worksheets

Michelle:
What would be nice would be if Microsoft had something that allowed Office 2000 to open SpreadsheetML files. But I don't believe they ever released anything like that. I know they've issued some compatibility tools so Office 2003 folks can read Office 2007 files (including the new Excel OpenXML format), but nothing seems to go back to Office 2000.

Here's the issue...yes...there is a way to make multi-worksheet files for Office 2000

1) use Proc Export or the SAS Libname engine for Excel to make multi-worksheet files (this requires SAS/Access to PC file formats) -- this also results in very plain output -- no colors, no fonts, none of the autofilter or column width features of TAGSETS.EXCELXP.

or

2) make a series of separate HTML files (one HTML file for every worksheet) and then link those HTML files together in the structure required by Office 2000. For example:
--a) make a directory called c:\temp\main_report_files
--b) make an HTML file called c:\temp\main_reports.html (one level ABOVE the subdir in #1)
--c) put sheet1.html, sheet2.html, sheet3.html, sheet4.html, etc into the c:\reports\main_report_files directory
--d) also make a filelist.xml file that goes into the c:\reports\main_report_files directory
--e) and of course, when you go to send this, you have to send the main_report file and the sub-directory with ALL of its files and when the person receives the batch of files, they have to maintain the same structure when they save the files onto their system. This is much easier to do if you're just writing the files in this structure on a shared drive.

THEN, when you open main_reports.html -- with Excel, Excel treats each of the sheet1.html...sheetn.html as separate worksheet files.

It's do-able, but klunky. I suppose another thing to try would be for you to build the files with TAGSETS.EXCELXP, but then before you transmit them, run a VB script, that opens the files and then resaves them in Office 2000 format.

You might also check with Tech Support to see whether they know of any other workarounds. There's a new tagset called TAGSETS.TABLEEDITOR that creates HTML files which can be loaded into Excel. I don't know what level of Excel is expected for this tagset. Some info about it is here:
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html
http://www2.sas.com/proceedings/forum2008/258-2008.pdf

I don't suppose there's any chance you can use this as an opportunity have your person upgrade???

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 288 views
  • 0 likes
  • 2 in conversation