BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ak2
Calcite | Level 5 ak2
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

 

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

 

ak2
Calcite | Level 5 ak2
Calcite | Level 5

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ak2
Calcite | Level 5 ak2
Calcite | Level 5
Thank you so much for your help!

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3157 views
  • 0 likes
  • 2 in conversation