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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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