topic Re: Graph Template Language and Output using ODS Excel in ODS and Base Reporting

Graph Template Language and Output using ODS Excel

rschneider — Tue, 12 Dec 2017 21:09:01 GMT

I'm using SAS Enterprise Guide 7.1, and I'm trying to create a very wide bar chart using graph template language and then outputting to excel using ODS EXCEL. The issue I'm having is that the graph size is not outputting the way I am expecting from my code. It's much smaller than I am intending. Strangely, it seems that if I make a horizontal bar chart, the sizing works appropriately, but I need the graph to be a vertical bar chart. Any ideas? Thank you!

proc template;

define statgraph q1;

begingraph / designwidth=20in designheight=8in subpixel=ON

backgroundcolor=white borderattrs=(color=black);

entrytitle "Ease of Appointment" /

textattrs=(family="Calibri" size=10 weight=bold) haligncenter=graph;

entrytitle "% Somewhat Satisfied/Very Satisfied" /

textattrs=(family="Calibri" size=10 weight=bold) haligncenter=graph;

layout overlay /

xaxisopts = (label = "Providers With Respondents Somewhat Satisfied/Very Satistfied"

labelattrs= (size=9 family="Calibri" weight=bold)

tickvalueattrs=(size=9 family="Calibri" weight=bold))

yaxisopts = (label = "Percent"

labelattrs= (size=9 family="Calibri" weight=bold)

tickvalueattrs=(size=9 family="Calibri" weight=bold)) ;

barchartparm x = graphname

y = q1_percent / dataskin = pressed

fillattrs=(color = &icpblue.)

orient=vertical;

referenceline y=q1_icp_median_percent /

lineattrs=(color=red thickness=4) legendlabel="ICP Median" name='ref';

referenceline y=q1_icp_goal /

lineattrs=(color=green thickness=4) legendlabel="ICP Goal" name='ref2';

discretelegend 'ref' 'ref2';

endlayout;

endgraph;

end;

run;

ods noresults;

ods noproctitle;

options nodate nonumber orientation=landscape;

ods listing close;

ods excel file = "&cipath./survey/Ortho Graphs/Survey_prov.xlsx";

ods excel options(sheet_name="Ease of Apt");

proc sgrender data = allquestions2 template=q1;

run;

ods excel close;

Re: Graph Template Language and Output using ODS Excel

ballardw — Tue, 12 Dec 2017 22:14:52 GMT

Ods Graphics statement is the basic control for image size.

Ods graphics / height=8in width=6in;

would set the graphic display area to 6 inches wide by 8 inches high.

Not to Excel but I have used this with up to 30 inch dimensions.

Re: Graph Template Language and Output using ODS Excel

rschneider — Wed, 13 Dec 2017 15:09:38 GMT

Yes, I think this issue might be related to outputting to excel. When using ods graphics to define the image size, rather than defining the designheight and designwidth in the begingraph options, I get a warning in the log:

"WARNING: WIDTH exceeds available space for EXCEL destination. Setting WIDTH=1.09375in."

Do you know if there is any way around this or what the available space for the excel destination is?

Re: Graph Template Language and Output using ODS Excel

ballardw — Wed, 13 Dec 2017 17:27:45 GMT

@rschneider wrote:

Yes, I think this issue might be related to outputting to excel. When using ods graphics to define the image size, rather than defining the designheight and designwidth in the begingraph options, I get a warning in the log:

"WARNING: WIDTH exceeds available space for EXCEL destination. Setting WIDTH=1.09375in."

Do you know if there is any way around this or what the available space for the excel destination is?

So what was your setting on ODS Graphics???

from the documentation:

DESIGNWIDTH options of the BEGINGRAPH statement set the intended height and width, which are used to determine the scale factors when the graph is resized. The intended height and width are used unless overridden by the ODS Graphics statement HEIGHT or WIDTH options when the template is executed.

So if an ods graphics statement with height and width was used before the execution that is overriding your design parameters. Note that macros or example programs may have had an ods graphics statement you weren't aware of. So you might need to use and ods graphics that matches your design parameters.

Re: Graph Template Language and Output using ODS Excel

rschneider — Wed, 13 Dec 2017 17:53:56 GMT

Thank you for your reply ballardw! I was using the same width and height as my original code (height 8in and width 20in) when I tried using ods graphics; however, I was never using ods graphics and designheight/designwidth at the same time. When I did use designheight/designwidth, I turned off ods graphics. It sounds like you are suggesting using them together, is that right?

Re: Graph Template Language and Output using ODS Excel

ballardw — Wed, 13 Dec 2017 21:02:31 GMT

@rschneider wrote:

Thank you for your reply ballardw! I was using the same width and height as my original code (height 8in and width 20in) when I tried using ods graphics; however, I was never using ods graphics and designheight/designwidth at the same time. When I did use designheight/designwidth, I turned off ods graphics. It sounds like you are suggesting using them together, is that right?

Actually I am suggesting that at sometime in your current session something similar to:

Ods graphics / width=6in;

may have been run.

I do see that if I use

ods graphics / width=20 that I get a warning when running an SGPLOT graphic:

WARNING: WIDTH exceeds available space for EXCEL destination. Setting WIDTH=8in.

So that may well be the issue you are having.

I don't normally deal with sending graphics to Excel. I always think images are to print or view and either a graphic file such as PNG or GIF works better or ODS RTF and ODS PDF for printable and viewable documents seem to behave nicer.

Re: Graph Template Language and Output using ODS Excel

rschneider — Thu, 14 Dec 2017 20:56:09 GMT

I am specifically trying to use excel as part of a request for the end user. Just for reference, I did find a way to get the graphs to appear as wide as I would like. I ended up using the SAS system option "papersize=" to specify a wider paper area, and it worked! Thank you for your help!