topic Re: how to store sgplots into different worksheets in excel in Graphics Programming

how to store sgplots into different worksheets in excel

ak2 — Tue, 19 Apr 2016 13:05:40 GMT

Hi! I have 2 sgplots that I need to export into Excel, but into 2 different worksheets. The plots are based on the same numbers, they only display them differently. If I use ods tagsets.excelxp I get an error saying

ERROR: Cannot write image to SGPlot146.png. Please ensure that proper disk permissions are set.

I have never gotten this error if I export proc tabulates, for instance.

If I use ods excel I can not manage to put them into different worksheets... I don't have a proper by group that might have maybe solved the issue by saying sheet_invterval='bygroup'.

My code for ods tagsets.excelxp is something like this:

ods tagsets.excelxp file="/.../testing.xls";

ODS TAGSETS.EXCELXP options(sheet_name='a' sheet_interval="none" );

ods graphics on;

proc sgplot data=q1; /*this should go into worksheet 1*/

run;

ODS TAGSETS.EXCELXP options(sheet_name='b' sheet_interval="none" );

proc sgplot data=q1; /*this should go into worksheet 2*/

run;

ods tagsets.excelxp close;

ods graphics off;

My code for ods excel is something like this, but it only gives me both plots in the same worksheet:

filename f "/.../testing.xlsx";

ods excel file=f

options(sheet_name="sgplot 1"

sheet_interval='none'

)

;

ods graphics on;

ods text="this one into sheet 1";

proc sgplot data=q1;

run;

ods graphics on;

ods text="this one into sheet 2";

proc sgplot data=q1;

run;

ods excel close;

ods graphics off;

Re: how to store sgplots into different worksheets in excel

RW9 — Tue, 19 Apr 2016 13:18:59 GMT

Well, I just ran the below code, and when I open the file it works fine:

ods excel file="s:\temp\rob\test.xlsx" options(sheet_name="a");
ods graphics on;
proc sgplot data=sashelp.class;
  scatter x=age y=sex;
run;
ods excel options(sheet_name="b");
proc sgplot data=sashelp.class;
  scatter x=sex y=age;
run;
ods excel close;

So I am wondering if you have some other options or something which is causing problems. Try starting a new SAS 9.4 session, and run the exact code above - change the path of course. It should give the correct output.

Re: how to store sgplots into different worksheets in excel

ak2 — Tue, 19 Apr 2016 13:43:02 GMT

I added options(sheet_name="a"); before each sgplot, and the plots are now coming into different sheets! Nice! Just one thing, I also have ods text, and it ends up underneath the first plot, i.e. the text is not following the sgplot into the new worksheet. Here is my code:

filename f "/.../testing.xlsx";

ods excel file=f

options(embedded_titles='on'

sheet_name="plot 1"

sheet_interval='none'

gridlines='off'

)

;

ods graphics on;

ods text="sheet1"; /*this txt comes above to the right of sgplot1*/

ods text="something ";

proc sgplot data=q1;

run;

ods excel options(sheet_name="b");

ods text="sheet 2"; /*this txt comes under to the right of sgplot1, not on the new worksheet*/

proc sgplot data=q1;

run;

ods excel close;

ods graphics off;

Re: how to store sgplots into different worksheets in excel

RW9 — Tue, 19 Apr 2016 13:49:28 GMT

Yes, from memory ods text doesn't work that way. If you want data underneath your graph then put it in a dataset and print it out:

data texts;
  sheet=1; text="This is sheet 1"; output;
  sheet=2; text="This is sheet 2"; output;
run;
ods excel... options(sheet_name="a");
proc sgplot...;
run;
proc report data=texts;
  columns text;
  define text / " ";
run;
ods excel...

That should create a report underneath the graph.

Re: how to store sgplots into different worksheets in excel

ak2 — Tue, 19 Apr 2016 13:51:07 GMT

Thank you so much for your help!