ODS Excel - Graph

[Pritish]

Hi,

I have few questions regarding SAS ODS option specifically regarding Excel and PDF output options. I am using SAS Enterprise Guide. Here's the scenario:

I have created a Macro in my program which has two SQL statements which will generate the result sets, I need to have. Now one of the SQL statement will give me a summary report and also a bar chart which I am generating using proc gchart procedures available in SAS. But due to some reason, I am not able to see the graph in the excel sheet. So my question is:
What should be the problem and can you suggest me a solution for that?

Secondly, as stated I am generating two reports (One will have all data and other report will has a summary report) which are generated in two separate excel sheet tab. (Tab 1: Has all data; Tab 2: Has Summary Report). But what I am trying to do is generate the summary report in the same tab(Tab 1). So my question is:

How can I provide a position for the summary output I am creating?

Lastly, in the future I might need to generate PDF for the same report so can I generate more than 3 bookmarks in a PDF file ? If yes how can I define a name for each bookmark?

Let me know if you need further information.

Thanks for all your help!

[Cynthia_sas]

Hi:
First without seeing code, it is impossible to do more than make general statements. So, here goes.

There are several ways that you could be sending your output from Enterprise Guide to Excel:
1) you could tell EG to send the results to Excel using point and click (from the Results viewer in EG, right click and choose Send To --> Excel) -- this method uses the Microsoft Jet Engine, I believe and I am not sure what happens to graphics in this scenario.

2) you could use code to create an outptu file that Excel can open. There is no "ODS EXCEL" destination. ODS methods by which you can create procedure results in a form that can be opened by Excel are:
a) CSV methods -- use ODS CSV and/or ODS CSVALL to create a comma separated file which Excel can open and render (I do not believe SAS/GRAPH output would work in a CSV file)
b) HTML methods -- use any ODS HTML destination (such as ODS HTML, ODS MSOFFICE2K, ODS TAGSETS.MSOFFICE2K_X, ODS PHTML, ODS CHTML, ODS HTMLCSS, etc) to create an ASCII text HTML file that Excel can open and render. In most of these HTML-based options, your ODS-created file will contain a standard HTML <IMG> tag which contains the SRC= attribute to point to the location of the image file created by SAS/GRAPH. If you are using ODS and HTML-based methods to create a file for Excel to open -and- you want to use SAS/GRAPH images, then you may need to specifically control the location of the image files by using the PATH= and/or GPATH= options in code.
c) XML methods -- use ODS TAGSETS.EXCELXP to create an Office 2003 Spreadsheet Markup Language file that Excel can open and render. SAS/GRAPH will not "work" with TAGSETS.EXCELXP because, by MICROSOFT design, images and logos are not allowed in this "flavor" of Office XML.

3) You could be creating automatic HTML output with EG, by changing the results options from the Tools menu. In this instance, I do not believe you have control over the location of where the results or the SAS/GRAPH images are created.

When you use any of the above methods (2a, 2b, 2c) you are NOT creating true, binary Excel files. You are merely creating ASCII text files that Excel knows how to open and render.

You asked how to get all your output into one tab or one worksheet in Excel. If you use HTML-based methods (2b), all the output goes to one worksheet. However, there is no automatic "placement option" You could not, for example, start the tabular report in C3 and then start the graph output in A54 (for example).

You wanted to know about PDF output -- PDF output all goes into 1 document. So if you have your table first, then your graph second, the graph will actually be translated into PDF internal image format and the image from SAS/GRAPH is embedded in the PDF file -- there is no external image file with ODS PDF.

I don't know what you mean by generating more than 3 bookmarks in a file. If you have 15 procedure steps within one ODS "sandwich", then, if all 15 procedures generate output for ODS, you would have at least 15 bookmarks. Of course, some procedures, like PROC REPORT and PROC TABULATE will generate more than one bookmark -- so 15 procedures could generate more than 15 bookmarks -- but that's not your question.

To change the highest level node or bookmark that you see in a PDF file, you would use the ODS PROCLABEL statement, as shown in the code below. For PROC PRINT, PROC REPORT and PROC TABULATE, to change secondary level nodes or bookmarks, you would use the CONTENTS= option.

Understanding more how you are actually getting output into Excel (which of the above 3 methods you are using) or seeing some actual code would be useful. Meanwhile, there are examples below of all the code-based methods of creating output with ODS that Excel can open. Personally, I would use ODS MSOFFICE2K to create HTML output for Excel to open because I like how the style information is used in MSOFFICE2K (Microsoft "flavor" of HTML) versus how the style is only sort of used in HTML 4.0 flavor of HTML as created by ODS HTML.

You will be able to see that the CSV and ExcelXP methods do not use any of your SAS/GRAPH images. You will also be able to see the impact of ODS PROCLABEL. If you are using point and click methods to create your output, then you might want to work with Tech Support on your questions.

cynthia
[pre]
ods listing close;
ods csv file='c:\temp\examp_comma.csv';
ods html path='c:\temp' (url=none)
file='examp_ht4.xls' style=sasweb;
ods msoffice2k path='c:\temp' (url=none)
file='examp_mso.xls' style=sasweb;
ods tagsets.excelxp path='c:\temp' (url=none)
file='examp_xp.xls' style=sasweb;
ods pdf file='c:\temp\examp.pdf';

ods proclabel 'First Procedure';
proc print data=sashelp.class(obs=5)
contents='Something Else';
title 'Proc Print Title';
run;

goptions hsize=6in vsize=4in;
ods proclabel 'Second Procedure';
proc gchart data=sashelp.class;
title 'Proc GCHART Title';
vbar age / discrete sumvar=height type=mean;
run;
quit;
title;

ods _all_ close;
[/pre]