Creating Graphs in Excel from SAS

[deleted_user]

Hi,
I have been attempting to figure out the best way to create an Excel graph in a completely automated fashion from SAS. I have discovered ways to do so by creating an Excel template and then updating the underlying data. However, this method is very restrictive. I also figured out a way to create an SAS graph as an emf image then import the picture into Excel, but the details are horrendous, and I can't zoom the size of the image.

I really would like to create a chart using the X4ML chart.wizard command, but haven't been able to get this to work. I know that some of the X4ML commands simply don't work, however, I don't know where to find out whether this command actually works to create a chart.

I have a feeling that it can only be used to manipulate an already created chart, but can't actually create one.

This is a sample of the code I was attempting.

%macro create_chart(long=true,
cells=r1c1:r1c1,
chart_type=7,
type_num=1,
plot_by=1);

filename sas2xl dde 'excel|system';
data _null_;
file sas2xl;
put '[error(false)]';
put %unquote(%bquote('[chart.wizard("&long","&cells",&chart_type,&type_num,&plot_by)]'));
run;
filename sas2xl clear;

%mend;


%create_chart(cells=r1c1:r10c2,
chart_type=3);


Any help, or just the knowledge that this X4ML command won't actually create a chart in Excel would be extremely helpful.....


Thanks,
Ryan

[Cynthia_sas]

Hi:
Possibly this user-written paper is what you are looking for:
http://www2.sas.com/proceedings/sugi31/154-31.pdf

I found it by Googling on the string
X4ML chart wizard SAS

...and this paper was the second hit on the list. There seem to be more.
cynthia

[deleted_user]

Cynthia,
I appreciate the prompt response, but I have already read this paper, as well as most of the others that show up on this topic, and while it gives a solution to a similar problem, it can't do the trick. There are several problems with this being a solution.
1) This requires the creation of a template in a point & click fashion. This is very far from an automated approach, and could require the user to maintain or create dozens of these templates.
2) This template requires that you know what type of chart you want ahead of time, and have a reasonable idea of exactly what the data is going to look like. The idea is to have a flexible and generic procedure that can create any type of Excel graph/chart from SAS in a completely automated fashion.

I would like to hear from anyone who knows whether chart.wizard is a functioning command or not.

Regards,
Ryan

[Cynthia_sas]

Ryan:
I'm not a big fan of DDE -- it's old technology -- dating back to Excel 4. I think that Microsoft has moved onto newer technology -- ODBC and OLE-DB and further into .NET and .COM and ADO.

This Microsoft site shows creating a chart using VB and VBA:
http://msdn2.microsoft.com/en-us/library/aa203725(office.11).aspx

When I create output from SAS for Excel, I sometimes use HTML files to send a graph and/or a graph and table to Excel in this fashion. (I also am showing the creation of an RTF file, so you can compare the look in Excel vs the look in Word):
[pre]
options nodate nonumber;

ods msoffice2k path='c:\temp' (url=none)
file='mshtml_ax.xls'
style=sasweb;
ods rtf file='c:\temp\rtf_ax.rtf' style=journal startpage=no ;
goptions reset=all border device=actximg;

proc gchart data=sashelp.shoes;
title 'Vertical Bar Chart';
where region in ('Asia', 'Canada', 'Western Europe');
vbar3d Region / shape=hexagon sumvar=sales;
run;
quit;
title;

proc report data=sashelp.shoes nowd;
title 'Product Information';
where region in ('Asia', 'Canada', 'Western Europe');
column region sales;
define region /group;
define sales /sum;
rbreak after /summarize;
run;
ods rtf close;
ods msoffice2k close;
ods listing;
[/pre]

Of course, we are not creating a "true binary" Excel file with the above code. If you look at the generated file with Notepad, you will see that I am creating an HTML file that Excel knows how to open and how to read the <IMG> tag that's being generated. We are "fooling" the Windows registry into launching Excel when you double click on the file name.

For more help with the design of what you're trying to do, you might consider contacting Tech Support.

cynthia