topic Re: ods excel graph (sgplot) title missing in ODS and Base Reporting

ods excel graph (sgplot) title missing

alandool — Thu, 29 Jun 2017 10:30:25 GMT

Hi All,

I am attempting what seems simple and straight-forward, but alas ... I kid myself ..

Code below is a simplified version of what I'm trying to do which is simply to use ODS EXCEL to create a graph with a title (or even ODS TEXT), but having no luck. The graph is produced, but no title / text can be found. I've tried many different ways. Thanks for any help you can offer.

(using EG 7.1 - Platform Version 9.4)

Alan

ods excel file="C:\wherever\temp.xlsx"
options(sheet_interval="none" sheet_name="sheet 1" embedded_titles='yes');
ods graphics / height=800 width=1200 noborder;

TITLE "test graph title";
proc sgplot data=sashelp.cars;
histogram msrp;
run;

ods excel close;

Re: ods excel graph (sgplot) title missing

RW9 — Thu, 29 Jun 2017 10:48:19 GMT

Well, its the gtitle and gfootnote optins that need to be enabled. Think this should work:

ods graphics / height=800 width=1200 noborder gtitle gfootnote;

Haven't got anything to otry it on though.

Re: ods excel graph (sgplot) title missing

alandool — Thu, 29 Jun 2017 11:02:30 GMT

Thank you, RW9. It doesn't like the gtitle gfootnote options. Of all the options listed in the error msg, don't see any that looks like it ties to a title or footnote.

Alan

134 ods graphics / height=800 width=1200 noborder gtitle gfootnote;
______
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, ANTIALIAS, ANTIALIASMAX, ATTRPRIORITY, BORDER, BYLINE, DATASKINMAX,
DISCRETEMAX, DRILLTARGET, GROUPMAX, HEIGHT, IMAGEMAP, IMAGENAME, LABELMAX, LABELPLACEMENT, LEGENDAREAMAX,
LOESSMAXOBS, LOESSOBSMAX, MAXLEGENDAREA, NOANTIALIAS, NOBORDER, NOIMAGEMAP, NOSCALE, NOSCALEMARKERS, NOSUBPIXEL,
OUTPUTFMT, PANELCELLMAX, POP, PUSH, RESET, SCALE, SCALEMARKERS, SHOW, STACKDEPTHMAX, SUBPIXEL, TIPMAX, WIDTH.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: ods excel graph (sgplot) title missing

RW9 — Thu, 29 Jun 2017 11:18:44 GMT

Yep, sorry, should be on the ods excel line per:

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04zeur27rv.htm

ods excel file="C:\wherever\temp.xlsx"
options(sheet_interval="none" sheet_name="sheet 1" embedded_titles='yes') gtitle gfootnote;
ods graphics / height=800 width=1200 noborder;

Re: ods excel graph (sgplot) title missing

alandool — Thu, 29 Jun 2017 11:55:12 GMT

Tried, but didn't work. I added the gtitle gfootnote options but still only get a graph, no titles or wording anywhere in the spreadsheet. I had also read thru that documentation that you linked. The title works if it isn't a graph.

Alan

ods excel file="C:\wherever\temp2.xlsx"
options(sheet_interval="none" sheet_name="sheet 1" embedded_titles='yes') gtitle gfootnote;
ods graphics / height=800 width=1200 noborder;

TITLE "test graph title";
proc sgplot data=sashelp.cars;
histogram msrp;
run;

ods excel close;

Re: ods excel graph (sgplot) title missing

Cynthia_sas — Thu, 29 Jun 2017 12:13:02 GMT

Hi:

You might want to try again. I tried your code as you specified, without any changes and it worked for me as shown below:

With GTITLE GFOOTNOTE, as you specified (title is INSIDE graph image):

With NOGTITLE, NOGFOOTNOTE (title is OUTSIDE graph image):

cynthia

Re: ods excel graph (sgplot) title missing

Reeza — Thu, 29 Jun 2017 14:54:38 GMT

What exact version of SAS are you using ie SAS 9.4 TS1M4?

ODS EXCEL isn't production until SAS 9.4 TS1M3.

Re: ods excel graph (sgplot) title missing

alandool — Thu, 29 Jun 2017 19:17:54 GMT

Thank you, Cynthia_sas, interesting to see that, but for whatever reasons, can't get it to work for me.

Alan

Re: ods excel graph (sgplot) title missing

alandool — Thu, 29 Jun 2017 19:24:08 GMT

Hi Reeza,

Thought we were on 9.4, but when I run a proc setinit, this is what comes back. So, now I'm a little confused about whether we are or not.

Current version: 9.04.01M3P062415

Alan

Re: ods excel graph (sgplot) title missing

Reeza — Thu, 29 Jun 2017 19:43:30 GMT

That translates to SAS 9.4 TS1M3 - ODS Excel is production so it should work fine for you.

Re: ods excel graph (sgplot) title missing

ChrisHemedinger — Thu, 29 Jun 2017 20:46:50 GMT

Make sure that there isn't another ODS destination active. If running this in EG, you might want to change the properties of your program to close the other destinations (see this article for how), or issue ODS CLOSE _ALL_ before your code.

Re: ods excel graph (sgplot) title missing

alandool — Fri, 30 Jun 2017 09:21:19 GMT

Awesomeness, Chris! That worked and EG runs much quicker (info on properties helpful !). Final code below.

Thanks, EVERYONE for your help and input. Great help and support here.

Alan

ODS _ALL_ CLOSE;

ods excel file="C:\wherever\temp2.xlsx"
options(sheet_interval="none" sheet_name="sheet 1" embedded_titles='yes') gtitle gfootnote;
ods graphics / height=800 width=1200 noborder;

TITLE "test graph title";
proc sgplot data=sashelp.cars;
histogram msrp;
run;

ods excel close;