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
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
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):
<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>
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.