topic Re: ODS Output to Excel of GPLOT in ODS and Base Reporting

ODS Output to Excel of GPLOT

deleted_user — Mon, 22 Sep 2008 03:01:56 GMT

goptions reset=global gunit=pct border cback=white
colors=(black blue green red)
ftitle=swissb ftext=swiss htitle=6 htext=4;

data jobs;
length eng $5;
input eng dollars num;
datalines;
Civil 27308 73273
Aero 29844 70192
Elec 22920 89382
Mech 32816 19601
Chem 28116 25541
Petro 18444 34833
;

title1 'Member Profile';
title2 'Salaries and Number of Member Engineers';
footnote h=3 j=r 'GPLBUBL1 ';

axis1 offset=(5,5);

ods html file='c:\tstgplot.html';
*ods pdf body = 'c:\tstgplot.pdf';

Proc gplot data=jobs;
format dollars dollar9.;
bubble dollars*eng=num / haxis=axis1;
run;
quit;

*ods pdf close;
ods html close;
--------------------------------------
I have tried the above code to give the GPLOT output into Excel, While cheking the Excel file, it doesn't contain the output. While the same code works with ODS to PDF .

Can anyone help on me how to export the GPLOT to Excel.

Re: ODS Output to Excel of GPLOT

Olivier — Tue, 23 Sep 2008 08:49:33 GMT

Hi.
The problem is, the way HTML handles image files is a problem here : when you see a picture in a Web page (any time), it is just a link to a separate image file. In Excel, the link from the HTML file to the image does not work very well to display (at least in my experience).
In contrast, PDF includes both text & picture. That's why it works so fine in your example.
What you can do is change the HTML destination for TAGSETS.EXCELXP, provided you run your programs with SAS version 9.1.3 at least, and read the files with Excel 2002 at least. It is designed to build REAL (almost) Excel files, which can include pictures.

Regards.
Olivier

Re: ODS Output to Excel of GPLOT

Cynthia_sas — Tue, 23 Sep 2008 08:56:20 GMT

Hi:
The reason this works for PDF is that your image is converted to internal PDF format when the PDF file is created.
For HTML, you have to deal with 2 files being created:
1) the HTML file itself
2) the <IMG> SRC= file which contains the SAS/Graph-created image.

Generally, when you create SAS/Graph output to ODS HTML, you should consider using the PATH= and GPATH= options. The PATH= option determines the physical location of the HTML file (#1) and the GPATH= option determines the physical location of the IMG file (#2). What's probably happening is that your HTML file is going to your C:\ root directory, but your IMG file is going to your SAS working directory (which is usually in c:\Documents and Settings....)

Try this:
[pre]
ods html path='c:\' (url=none)
gpath='c:\' (url=none)
file='gplot.html';

Proc gplot data=jobs;
format dollars dollar9.;
bubble dollars*eng=num / haxis=axis1;
run;
quit;

ods html close;

[/pre]

The URL=NONE suboption tells ODS not to use the physical 'c:\' location in any URLs that it builds internally -- this is generally what you want with your IMG tags -- which means that the relative reference that is built will work whereever you move both files, as long as they are stored in the same directory on your web server or other machine.

Also, if you are running in SAS 9.1.3, you may find that ODS MSOFFICE2K produces HTML output that is more compatible with Microsoft products than standard HTML -- just because MSOFFICE2K produces the kind of HTML tags that Office is happier with. ODS HTML in SAS 9 produces HTML 4.0 compliant HTML tags & Microsoft didn't like HTML 4 as much as it likes its own HTML tags.

cynthia

Re: ODS Output to Excel of GPLOT

deleted_user — Mon, 13 Oct 2008 03:03:28 GMT

Thanks for the reply and support.

We found some material on following link
http://support.sas.com/rnd/base/ods/templateFAQ/excelexamples.html#multiwork

depending on the material available we were able to achieve the required result.

Am giving the script which we had written to achieve the result.
goptions reset=global gunit=pct border cback=white
colors=(black blue green red)
ftitle=swissb ftext=swiss htitle=6 htext=4;

data jobs;
length eng $5;
input eng dollars num;
datalines;
Civil 27308 73273
Aero 29844 70192
Elec 22920 89382
Mech 32816 19601
Chem 28116 25541
Petro 18444 34833
;

proc template;
define tagset tagsets.test;
parent=tagsets.phtml;
define event doc_body;
start:
put " put " onunload=""shutdown()""";
put " bgproperties=""Fixed""" / if exist( WATERMARK );
trigger style_inline;
put ">" NL;
put "" NL;
put NL;
finish:
put "" NL;
end;
define event image;
put " put " src=""";
put BASENAME / if !exist( NOBASE );
put URL;
put """";
putq " border=" BORDERWIDTH;
put " usemap=""#" @CLIENTMAP;
put """" NL / if exist(BORDERWIDTH,@CLIENTMAP);
putq " class=" HTMLCLASS;
putq " id=" HTMLID;
putq "alt=""this is a test""";
PUTQ "HEIGHT=400 WIDTH=500";
put ">" NL;
end;
end;
run;

ods listing close;
ods markup body='test.xls' path='c:\test' tagset=tagsets.test;

proc print data=sashelp.class;
run;

Proc gplot data=jobs;
format dollars dollar9.;
bubble dollars*eng=num / haxis=axis1;
run;

ods markup close;
ods listing;

Re: ODS Output to Excel of GPLOT

deleted_user — Mon, 13 Oct 2008 03:18:05 GMT

Part 2 of the script

put " onunload=""shutdown()""";
put " bgproperties=""Fixed""" / if exist( WATERMARK );
trigger style_inline;
put ">" NL;
put "" NL;
put NL;
finish:
put "" NL;
end;
define event image;
put "

Re: ODS Output to Excel of GPLOT

deleted_user — Mon, 13 Oct 2008 03:18:41 GMT

Part 3 of the scrip

put " src=""";
put BASENAME / if !exist( NOBASE );
put URL;
put """";
putq " border=" BORDERWIDTH;
put " usemap=""#" @CLIENTMAP;
put """" NL / if exist(BORDERWIDTH,@CLIENTMAP);
putq " class=" HTMLCLASS;
putq " id=" HTMLID;
putq "alt=""this is a test""";
PUTQ "HEIGHT=400 WIDTH=500";
put ">" NL;
end;
end;
run;

ods listing close;
ods markup body='test.xls' path='c:\test' tagset=tagsets.test;

proc print data=sashelp.class;
run;

Proc gplot data=jobs;
format dollars dollar9.;
bubble dollars*eng=num / haxis=axis1;
run;

ods markup close;
ods listing;