Data visualization with SAS programming

excelXP tagsets with Graphs

Reply
N/A
Posts: 0

excelXP tagsets with Graphs

Hi,
2 issues, not sure if both sit on this forum. I am in the process of putting together a regular report that needs to be outputted in excel (unfortunately). I have almost got the styles sorted, but what i need to do is output 4-6 graphs per sheet and possibly put some tables aswell. i'm not really sure where to start with the layout. Also i need to use a company logo on each page of my report and i can get it to display in sas, but it wont go into excel.
Any help greatfully recieved, also don't worry about telling me to move it to another forum if thats better

Thanks
SAS Super FREQ
Posts: 8,721

Re: excelXP tagsets with Graphs

Hi:
There are several methods, using ODS to create files that Excel can open:
1) CSV files - create with ODS CSV or ODS CSVALL --- graphs are not supported in this output file type (this output is for tabular results);
2) HTML files - create with ODS HTML or ODS MSOFFICE2K -- these destinations create HTML files that Excel can open and render -- graphs are supported in this output type;
3) Spreadsheet Markup Language XML files - create with TAGSETS.EXCELXP -- this destination does not support the use of graphs. This is by Microsoft design and ODS can't do anything to bypass this designed behavior.

For example, if you open Excel (without SAS) and just put a logo into your file and make a graph using some typed data points -- if you then try to save the workbook as XML Spreadsheet 2003 XML (the type of XML created by ODS), Excel will give you this warning message: "Book1 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 discover that (at the end of the Help topic):
This XML Spreadsheet 2003 file format (.xml) does not retain the following features:
--Chart and other graphic objects

(among the list of unretained features)

Besides the graph issue, there's the issue of multiple sheets in one workbook:
If you use ODS CSV or ODS HTML methods to create a file that Excel can open, you can only create single sheet workbooks. For example, if you have 2 proc steps, then you will get 1 sheet when Excel opens these files and ALL the output will be in 1 sheet. So with these methods, there is only 1 "page" to your report (assuming each sheet to be a page).

If you use the ODS TAGSETS.EXCELXP method to create a file that Excel can open, you can create multi-sheet workbooks, however, you can't insert SAS/GRAPH created output into these files. (by Microsoft design, as described above)

You could use the ODS HTMLPANEL destination to create panelled output -- where your 4-6 graphs could be arranged in a layout of rows and columns, however, in the past, I have not experienced much success when the HTMLPANEL output file is opened in Excel -- generally, the graphs tend to plop over each other.

You might wish to work with Tech Support on this issue. It is possible to create multiple HTML files which can be linked together into one workbook when opened with Excel, however, it does require some manual programming of custom HTML in order to make Excel recognize that the multiple HTML sheets are linked together.

Another option is to investigate TAGSETS.TABLEEDITOR -- that is an HTML-based destination which has some embedded JavaScript to load the HTML file into Excel -- I believe it supports graphs and I -think- that it supports multiple worksheets (but I am not 100% sure of this fact).

cynthia
N/A
Posts: 0

Re: excelXP tagsets with Graphs

Thanks for your very detailed response Cynthia. To be honest I hate having to touch excel anyway.
What this makes me think is that I would be better to produce the report as multi page html document using the panel method you mentioned.
Do you know of any documentation to help when producing a report like this and does it mention how to make it look nice??
Thanks again

Peter
SAS Super FREQ
Posts: 8,721

Re: excelXP tagsets with Graphs

Hi, Peter:
The HTMLPANEL documentation (including a zip file of examples) is here:
http://support.sas.com/rnd/base/ods/odsmarkup/htmlpanel.html

Depending on what you mean by making it look nice...that's a different question. If you are using SAS 9.2, you can improve the appearance of the SAS/GRAPH and the HTML text-based output by using a simple STYLE= option in your ODS invocation statement:
[pre]
ods html file='xxx.html' style=watercolor;
ods html file='yyy.html' style=analysis;
ods htmlpanel file='zzz.html' style=statistical;
[/pre]

If that is not nice enough, then you can modify the style template (WATERCOLOR, ANALYSIS or STATISTICAL) and use your modified template.

If you are still using SAS 9.1.3, then either you have to
1) use the client drivers (such as ACTIVEX, ACTXIMG, JAVA or JAVAIMG) in order to have the style template info respected or
2) use internal SAS/GRAPH action statements, global statements and graphic options to impact the style of the graph particularly if you are using other device drivers, such as GIF or JPEG.

If this is not what you mean by "make it look nice", then you'll have to elaborate further.

cynthia
N/A
Posts: 0

Re: excelXP tagsets with Graphs

Thanks Again,
I am using 9.1.3 and by 'look nice' I want to be able to control the colors/fonts of the output and have a company logo on each page, and have a readable layout. basically I want to be able to run a monthly automated report end to end using SAS and avoiding any copy-pasting into excel etc..

Thanks

Peter
SAS Employee
Posts: 967

Re: excelXP tagsets with Graphs

Once you've upgraded to v9.2, you might want to create a custom style to control a lot of that.

But at v9.1.3, you can do it manually (which I prefer over styles anyway) via goptions such as: ftitle, ftext, ctext; symbol statements (for line colors); pattern statements (for bar & gmap colors); and for a company logo image you can use: iback, annotated image, or put something in your html header.

And some/all of these things, you might want to put in your autoexec.sas, or perhaps more flexible would be to put them in a central file which you (and your co-workers?) would use "include" to suck them into all your sas jobs.

Just some thoughts...
N/A
Posts: 0

Re: excelXP tagsets with Graphs

Thanks for the advice, i am going to try an implement it to produce the report using html panels. I'm sure i might need to ask some more questions later on.
I do think that the output side of SAS could do with being made more user friendly, as a SAS enthusiast i do begrudge using Excel for reports, but it is often the easier solution.
Thanks again for your help

Peter
Moderator
Posts: 233

Re: excelXP tagsets with Graphs

I'd like to output multiple graphs tiled in the one Excel tab. I've had a good trawl through various threads (and other blogs). Any suggestions?

Requirement: Static Excel file to be distributed/emailed. (i.e, no SAS/BI Office plug-in, etc)

For a simple example, a 2x2 tile of:
Cross-tab table, Bar chart
Line chart, Pie chart

Thanks.
SAS Employee
Posts: 967

Re: excelXP tagsets with Graphs

Just curious - why do you want to put the SAS graphs in an Excel spreadsheet? I assume the spreadsheet contains the data also, and you want to give that to the users in a format they can do other things with?

Browsing through the previous posts in this thread, it sounds like you want to show multiple graphs together, have them look nice, include graphic logos, etc. Here are some examples of how to do that, and the output is generally a single 'png' (or gif) file, generally created using SAS/Graph and 'proc greplay':

http://robslink.com/SAS/dashboards/aaaindex.htm

Once I have created one of these png files, I am able to (manually) go into Excel, and and "Insert -> Picture" to include it into a spreadsheet.
Moderator
Posts: 233

Re: excelXP tagsets with Graphs

I'm hoping to NOT have to manually "Insert Picture". I'm after scheduled end-to-end capability from data source through to deploying to an Excel spreadsheet that can be auto-emailed.

My requirements are two-fold: One sheet showing Excel graphing capability, another showing SAS graphing capability, and a third sheet containing the data.
SAS Employee
Posts: 967

Re: excelXP tagsets with Graphs

This (DDE) is a bit out of my area of expertise, but I think you could use DDE to insert the picture (in this case, the SAS/Graph png of the graph or dashboard) into the spreadsheet. For example, the following paper mentions...

"... call VBA macros through DDE. Those VBA macros can be pre-recorded and used to edit the report as you desire, for example, to insert/rotate pictures, to do basic calculations, etc."

http://support.sas.com/resources/papers/proceedings10/074-2010.pdf
Moderator
Posts: 233

Re: excelXP tagsets with Graphs

SAS produces excellent graphs, outputting to formats such as HTML. Why not Excel? I really like Robert Allison's SAS/Graph Dashboards - I just want (well, actually the business wants therefore I want) to produce the same output in Excel.
Ask a Question
Discussion stats
  • 11 replies
  • 1955 views
  • 1 like
  • 4 in conversation