Report and Graph simultaneously in excel

sreenu_reddy — Fri, 20 Mar 2009 10:37:36 GMT

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

Re: Report and Graph simultaneously in excel

Cynthia_sas — Fri, 20 Mar 2009 16:13:24 GMT

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 xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office: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">

</head>
</html>
[/pre]

Then, your filelist.xml file might look like this:
[pre]
<xml xmlns:o="urn:schemas-microsoft-com:office:office">
<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

topic Report and Graph simultaneously in excel in ODS and Base Reporting

Report and Graph simultaneously in excel

Re: Report and Graph simultaneously in excel