TAGSET.EXCELXP and GRAPHS

[deleted_user]

Hi,

I am currently using tagsets.excelxp in order to output my proc tabulate reports to excel. However, the client is requesting for SAS to output SAS graphs that were generated by SAS EG to Excel. I tried tagsets.excelxp however everytime I select the generated .xls file, it somehow returns a message that the file cannot be read. Is there an option or code that allows tagsets.excelxp to output a graph generated by SAS.

[Cynthia_sas]

Hi:
When you use TAGSETS.EXCELXP, you are creating Microsoft-compliant Spreadsheet Markup Language XML, as designed for Excel 2002/2003.

When you use SpreadsheetML XML, it is by MIcrosoft design that images are not allowed in the spreadsheet. You can prove this to yourself by creating a spreadsheet in Excel (without SAS being involved) and then try to insert a picture (like a logo) into the spreadsheet. Everything will look OK. BUT, when you go to save the spreadsheet as Excel 2003 XML, you will get a message from Excel that says some of the spreadsheet components will not be saved. And, if you click for More Info, you will see that Microsoft explicitly does NOT allow images in Excel 2003 Spreadsheet ML XML.

If you want to put SAS-created Graphs into a file that can be opened with Excel, you can't use TAGSETS.EXCELXP. You will have to use one of the HTML-based destinations, such as ODS MSOFFICE2K or ODS HTML or ODS HTML3.

cynthia

PS...Technically speaking, when you use ODS HTML methods to reference SAS-created graphs, I need to be clear that SAS is not actually embedding an image into the HTML file. (my use of the word "put" in the above explanation could be misinterpreted as the image is being embedded).

In actuality, the HTML file generated by the HTML/MSOffic2K destinations will contain a reference to the image, not the image itself. For example, if you run code to create an HTML file:
[pre]
ods listing close;
ods tagsets.MSOffice2K path='C:\temp' file='class.htm' style=plateau;
goptions device=gif;
proc gchart data=sashelp.class;
pie3d age / discrete;
run; quit;
ods _all_ close;
[/pre]

if you look at the resulting HTML file with Notepad, you will see this <IMG> tag:

<img alt="Pie chart of Age" src="C:\temp\gchart1.gif" ... >

When you open the HTML file with Excel the image should render because Excel knows how to interpret the <IMG> tag and go to the specified location to pull the image into the spreadsheet. Message was edited by: Cynthia@sas