topic Re: Exporting SAS Graph to Excel in ODS and Base Reporting

Exporting SAS Graph to Excel

Bill — Fri, 04 May 2007 16:35:35 GMT

SAS text can easily be exported to excel via ods html. Is there a trick as simple as what I'm trying to do below for SAS Graph? (Obviously this one doesn't work!)

Thanks,
Bill

ods html file="\\MOFtp\MainFrameFtp\EsaDown\TestGrf.xls";

goptions dev=jpeg;
proc gtestit picture=1;
run;
quit;

ods html close; spelling correction by Bill - 07May2007

Message was edited by: Bill

Re: Exporting SAS Graph to Excel

Chevell_sas — Fri, 04 May 2007 17:06:19 GMT

Try using the MSOffice2K destination if you are running SAS 9.

ods msoffice2k file="c:\temp.xls";

proc gchart data=sashelp.class;
vbar height;
run;
quit;

ods msoffice2k close;

Re: Exporting SAS Graph to Excel

Cynthia_sas — Fri, 04 May 2007 18:54:07 GMT

You may also discover if you are trying to write to a web server directly, that you need to use some form of PATH/GPATH/URL= options:

For example, let's say that your web server is www.wombat.com and your
reports are stored in the reptdir directory:
www.wombat.com/reptdir/

You may want to make a "mirror" directory structure on your local machine and do this:
[pre]
ods msoffice2k path='c:\reptdir' (url=none)
gpath='c:\reptdir' (url=none)
file="temp.xls";

proc gchart data=sashelp.class;
vbar height;
run;
quit;

ods msoffice2k close;
[/pre]

if you are writing directly to a web server or mapped drive, you may have to play around with the URL= option for the graph and gpath to get the location for the <IMG> tag to be the right web server URL.

for example, you may want something like this for the <IMG> tag:
[pre]
ods msoffice2k path='c:\reptdir' (url=none)
gpath='c:\reptdir' (url="http://www.wombat.com/reptdir/")
file="temp.xls";
[/pre]
In which case, the <IMG> tag would be built with the following SRC=
[pre]
src="http://www.wombat.com/reptdir/gchart.gif"
[/pre]

You can generally tell that you need to use some combo of PATH/GPATH if you see a red 'X' in the browser or in Excel.

cynthia

Re: Exporting SAS Graph to Excel

Bill — Fri, 04 May 2007 20:31:12 GMT

The second method by Cynthia works well. Can you direct me to any driver choices other than gif or jpeg?
I've been using sasprtc to pdf with good results. With this direct to excel export, I'd like to retain that level of resolution.

Thanks,
Bill

Re: Exporting SAS Graph to Excel

Cynthia_sas — Sat, 05 May 2007 03:44:33 GMT

Hi, Bill:
Personally, I like the look of device=actximg or device=png instead of GIF or JPEG. It's one of those "it depends" recommendations, though -- what looks good to me may not look as you want. I almost always start with actximg and then try the others. You can find the list of device drivers in the SAS/Graph documentation.
cynthia

ps...I have to add this comment...you said "With this direct to excel export..." and as an instructor, I'm wired to point out that this method is NOT an export in the PROC EXPORT/Libname engine meaning of "export".

Instead, the MSOFFICE2K destination produces an HTML file that Excel "knows" how to open because Microsoft has allowed Word and Excel to read HTML files since Office97, when you could read HTML 3.2 files with Office. Then, starting in Office 2000, Microsoft came up with their own "flavor" of HTML. That's why the name of the tagset template that writes the HTML is MSOFFICE2K.

If you open your ODS result file in Notepad, you will see the HTML tags that ODS has written around your procedure output.

Re: Exporting SAS Graph to Excel

Bill — Mon, 07 May 2007 18:46:45 GMT

Thanks Cynthia - esp for the teacher admonition!

one more question on this one ... I'd like title2 below to look like the link that it is in the excel sheet. I think I need to surround it with some html code. The way it shows up now I need to click on it a few times in excel and then it gets the link look. This is in sas v8.

Thanks, Bill

filename xl ftp "/MainFrameFtp/EsaDown/SlabInvRpt.xls"
host='xxxxx'
user='xxxx'
pass='xxx'
debug
recfm=s;

ods html file=xl trantab=ASCII style=mytheme;

title2 h=1 "\\MOFtp\MainFrameFtp\EsaDown\SlabInvGrf.pdf"; Bill

Message was edited by: Bill

Re: Exporting SAS Graph to Excel

Cynthia_sas — Mon, 07 May 2007 19:20:24 GMT

Bill:
I no longer have SAS 8 in order to test, but try this. It works for me in SAS 9 and I thought it would work in 8, as well.

[pre]
ods tagsets.msoffice2k file='c:\temp\testlink.xls';
title1 'the title';
title2 "<div><a href='Demo02.pdf'>Click This For Printable PDF</a></div>";
proc print data=sashelp.class;
run;
ods tagsets.msoffice2k close;
[/pre]
I already had a file called Demo02.pdf in my c:\temp directory, but you get the idea. In my Excel (2003), the text "Click This For Printable PDF" showed up underlined like a hyperlink and then when I hit CTRL and hovered over the link, a CTRL + click loaded the PDF into Acrobat Reader. Since title2 is effectively turned into a hyperlink, in my Excel, it no longer has the title font characteristics, but instead has the link characteristics from Excel (I think).

I coded a complete, syntactically correct HTML anchor tag. You may find that you need file:// or http:// in the href attribute -- and I can never remember whether a machine-name/network-path like the one you show is OK in an HREF. I think it is OK, but the link will only work if that network path is available or something like that.

I think Tech Support may be the only folks who still have SAS 8 for testing. If you still run into problems with the hyperlink in the title, you'll need to enlist their help.

Good luck,
cynthia