The SAS Output Delivery System and reporting techniques

Export Graph to Excel by ODS

Posts: 47

Export Graph to Excel by ODS

Anyone who can give me an ODS example that exports gif/jpg producted by SAS/Graph to Excel.
each gif/jgp each worksheet! gif/jpg and also datatable same in one worksheet will be better!

Thanks in Adv!
Posts: 8,744

Re: Export Graph to Excel by ODS

There's good news and bad news about what you want to do.

The good news is that ODS Tagsets.ExcelXP automatically creates multiple worksheet workbooks in Excel. The bad news is that by Microsoft design/specification Spreadsheet ML (Excel XML) does NOT support graphs. If you try to create graphs in an ExcelXP output file, you get this message in the log:
Excel XML does not support output from Proc:Gchart
Output will not be created.

There -is- still good news. If you use ODS HTML or ODS MSOFFICE2K, you can create multiple, separate HTML files, each with a graph on/in it. Then, using a different approach, you can LINK the files together. Microsoft will allow each separate HTML file to be a worksheet, however, ALL the files have to live in a subdirectory. So let's say that you want to call your workbook BigRept.html and it will be stored in c:\temp:


you will need a subdirectory called:

in the BigRept_files directory, you will have your ODS HTML files AND the graph images files and maybe a CSS file. Let's say the files are stored like this:

Then, you also need an "organizing" file...let's call that file orglist.xml and it, too is in the subdirectory: c:\temp\BigRept_files\orglist.xml
and it looks like this:
<xml xmlnsSmiley Surprised="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice">
<o:MainFile href="BigRept.html"/>
<o:File href="chart1.html"/>
<o:File href="chart2.html"/>
<o:File href="chart3.html"/>
<o:File href="Orglist.xml"/>

Now, back to your "top" file c:\temp\BigRept.html (or you could call it BigRept.xls), it has to contain ALL the related file names (like those shown in Orglist.xml), but in a different format:[pre]

xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"


Now, if you call this file BigRept.xls and IF you have your subdirectory created and place all the related file in the subdirectory, then when you double click on BigRept.xls, then Excel should open all the related files together.

If you call the file BigRept.html, then you have to Right Mouse click and either do an Open with Excel or you have to open Excel and manually open the HTML file. Either way, you will get a multi sheet workbook -including- graphics.

This approach is outined in this R&D presentation:

So, the good news is that you can do what you want, it just takes a bit more work than using Spreadsheet Markup Language.

Posts: 0

Re: Export Graph to Excel by ODS


How about using ODS tagsets.excelxp to do a graph after the data is already in excel? My request can be satisfied very easily. Either A) use ODS to create/overwrite certain sheets in an excel workbook having one page be the the presentation page that links to the data sheets or B) use said tagset to create a graph using some other method after the data is in excel such as tagattr?

It's a serious problem for my firm b/c everyone is starting to move to R since it's graphing capabilities are easier to use. I'm a SAS guy, but I can't argue with them on that one.

Posts: 8,744

Re: Export Graph to Excel by ODS

TAGSETS.EXCELXP has to abide by Microsoft's rules. Once the file is created by ODS TAGSETS.EXCELXP -- it is an ASCII text, Excel 2003 Spreadsheet Markup Language XML file. Excel might allow you to make a graph in that file -- using Excel's drop-down menus. However, if you then try to save the file as Excel 2003 XML, you will get the following message -in Excel-. In fact, if I just open Excel to a blank workbook (without having SAS involved) and then I type some data and build a graph, if I then save the file as Excel 2003 XML, I get the same message -- from Excel:

Book1.xls may contain features that are not compatible with XML Spreadsheet 2003. do you want to keep the workbook in this format?

and then if you click Help, you get to Microsoft Help, which says that:

This XML Spreadsheet 2003 file format (.xml) does not retain the following features:

--Auditing tracer arrows
--Chart and other graphic objects
--Chart sheets, macro sheets, dialog sheets
--Custom views
--Data consolidation references
--Drawing object layers
--Outlining and grouping features
--Password-protected worksheet data
--User-defined function categories
--VBA projects

So even if ODS -could- send some kind of commands to Excel to make a graph when the file is opened, the Excel XML format just does NOT have any commands in XML related to building graphs. But, ODS can only send XML that's been defined (such as XML for changing orientation, column-width, print headers, etc), so since Microsoft did not even build graphing capabilities into Excel 2003 XML, there's nothing that SAS can do on that front.

A lot of my students report that they use Excel macros or VB Scripts to save the ODS-generated XML to binary Excel format. In that case, they can use any Excel commands or macros or scripts to insert Excel-based graphics into the file.

However, to specifically address your questions/suggestions:
A) ODS cannot overwrite a single sheet in a workbook -- when ODS creates a file, it is creating an ASCII text file (either HTML or XML or CSV) that Excel knows how to open and render -- ODS cannot write to an existing Excel workbook.

B) the ODS tagset MUST conform to a specification -- an HTML tagset must conform to the HTML 4.0 or HTML 3.2 or Microsoft HTML specification -- in that specification, you point to an IMAGE with an &LT;IMG&GT; tag. ODS does that.

With the TAGSETS.EXCELXP tagset, Microsoft did not define a way, in the specification, to create a graph using the XML tags. After the ODS output is rendered in Excel, SAS is out of the picture -- it is no longer able to "send" anything to Excel. Technically, the output that you create with ODS is not in native Excel format until you SAVE the file to a native Excel format. The only way to create true, binay Excel files in native Excel format is to use PROC EXPORT or the SAS Libname engine for Excel.

The other way to send data from SAS to Excel is to use DDE or ODBC or OLE-DB. None of these methods, however, use ODS. ODS creates an ASCII text file -- either ODS CSV or ODS HTML or ODS MSOFFICE2K or ODS TAGSETS.EXCELXP -- they all create ASCII text files in one form or another that Excel knows how to open and render.

In fact, when you create an ASCII text file and the ASCII file is RENDERED in Excel -- it is rendered for you, using Excel as the "viewer", but then Excel will try to save the file to the same format it opened, unless you specifically RESAVE the ASCII file as one of the Excel formats (either .xlsx or .xls) which represents a native Excel format.

Note that, the example below (run in SAS 9.2) does create an HTML file with an image that can be opened in Excel. Each HTML file that you create would represent one worksheet in one workbook. If you wanted to link multiple HTML files together into a multi-sheet workbook, then you would use the techniques that I outlined in the posting above. (Note that the file extension of .XLS is being used to force the output to open in Excel when double-clicked. Otherwise, with an extension of .HTML, the output would open in a browser when double-clicked. ODS, in the code below is creating an ASCII text HTML file that can be opened and rendered in Excel.)

ods html path='c:\temp' (url=none)
file='Excel_with_graph.xls' style=sasweb;

goptions device=jpeg hsize=12in vsize=7in;

proc gchart;
title 'Average Sales of Shoe Products';
vbar product / sumvar=sales type=mean;

ods html close;
Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation