The SAS Output Delivery System and reporting techniques

Report and Graph simultaneously in excel

Reply
N/A
Posts: 1

Report and Graph simultaneously in excel

Hi guys,
i have to prepare an MIS report which should consist of table and graph.like suppose i have table1 ,graph1,table2 graph2 ....etc.i have 12 variables for which i have plot tables and corresponding graph and each variable should be in a differe sheet of a single work book.
so i have to display it in excel as my table followed by corresponding graph.
and i would like to know is there any option for diverting my report to different sheets ina single work book.
please help me with this guys.
Thanks & Regards
srinivas
SAS Super FREQ
Posts: 8,868

Re: Report and Graph simultaneously in excel

Posted in reply to sreenu_reddy
Hi:
The only ways to create output from SAS that can be opened in Excel are (using SAS methods):
1) Export methods: PROC Export/Libname engine (only supports Tables)

2) ODS CSV: creates comma-separated files (only supports Tables)

3) ODS HTML: creates HTML files and links to external <IMG> tags -- HTML files CAN be opened and rendered in Excel. However ALL the output goes on 1 sheet in 1 workbook by default.

4) ODS TAGSETS.EXCELXP: creates Spreadsheet Markup Language files from SAS Tabular output -- also allows multiple worksheets automatically, but, due to a Microsoft restriction, Spreadsheet Markup Language files cannot contain graphic images of any kind.

5) Using the SAS Add-in for Microsoft Office (as part of the Enterprise Intelligence Platform, you could perform tasks or run stored processes to populate every sheet.

From the above list, using SAS methods, #3 creating an HTML file is the way that you could create your table and graph There are several ODS destinations to choose from: ODS HTML3, ODS HTML, ODS MSOFFICE2K, ODS PHTML, ODS CHTML, etc, etc. However, the basic limitation for HTML files is that 1 HTML file = 1 sheet in 1 workbook. So if even if you created multiple HTML files, each file with 1 table and 1 <IMG> tag for the graph, then Excel, by default, would open each separate HTML file into 1 separate workbook. But, Excel -does- have a method whereby you can link separate HTML files together so they open in one workbook, each HTML file as a separate sheet -- however, this is dependent on Microsoft's specification for how to link multiple HTML files together. And the Microsoft specification requires that all the files be physically located together in a specific directory/subdirectory structure.

However, SAS does not automatically create the structure that Microsoft wants in order to display multiple HTML files in one workbook -- each file on one worksheet. For example, Microsoft wants files to be in this structure:
c:\temp\Main_Report.html or c:\temp\Main_Report.xls
c:\temp\Main_Report_files\ <-- is a subdir under the main dir
c:\temp\Main_Report_files\filelist.xml
c:\temp\Main_Report_files\file1.html
c:\temp\Main_Report_files\file1.png
c:\temp\Main_Report_files\file2.html
c:\temp\Main_Report_files\file2.png
c:\temp\Main_Report_files\mystyle.css


If you are going to create LINKED HTML files -- so that each HTML file (for example) would populate 1 worksheet, then your Main_Report.html file might look like this (this is an actual example that I built -- filenames are different than those used above):
[pre]
<html xmlnsSmiley Surprised="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice"
xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta name="Excel Workbook Frameset">
<meta name="Generator" content="SAS Software, http://www.sas.com" sasversion="9.1">
<meta http-equiv="Content-type" content="text/html">
<meta name="ProgId" content="Excel.Sheet">
<link rel="stylesheet" type="text/css" href=".\Main_report_files\mystyle.css">
<link rel=File-List href=".\Main_report_files\filelist.xml">
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>By Region</x:Name>
<x:WorksheetSource HRef=".\Main_report_files\byreg.html"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>By Year</x:Name>
<x:WorksheetSource HRef=".\Main_report_files\byyear.html"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Detail</x:Name>
<x:WorksheetSource HRef=".\Main_report_files\details.html"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
</html>
[/pre]

Then, your filelist.xml file might look like this:
[pre]
<xml xmlnsSmiley Surprised="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice">
<o:MainFile href="Main_report.xls"/>
<o:File href="byreg.html"/>
<o:File href="byyear.html"/>
<o:File href="details.html"/>
<o:File href="mystyle.css"/>
<o:File href="filelist.xml"/>
</xml>
[/pre]

SAS does not automatically create the directory structure or these 2 files -- you might have to do that either manually or programmatically. But SAS will create all the separate HTML files and <IMG> files for you.

Alternately, you might investigate other techniques, such as DDE or OLE-DB or Excel Macros or VB scripts to do what you want. Some folks write their tables to multiple worksheets using SAS techniques, such as #1 or #4 and then open Excel and run an Excel macro or VB script to insert the graphs (using Excel) and then resave the file as an XLS file.

cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 141 views
  • 0 likes
  • 2 in conversation