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

I'm working on multiple graphs (around 100) and have used SGPLot  to produce graphs. So now my question is how can I output all the graphs into one excel and in different work sheets. Here is my code:


%macro subdat;

%do z = 1 %to 10;

%let sheet=%scan(&sheets.,&z);

proc import datafile="&path.\test.xls"
out=T&sheet. dbms=xls replace; sheet="&sheet."; getnames=yes; run;

ods listing close;
ods graphics / width=14in height=8in;
ods excel file="&outpath.\tables.xlsx"
options(SHEET_NAME="&sheet." embedded_titles="yes" embed_titles_once="yes" );

 

PROC SGPLOT DATA = T&sheet. dattrmap=attrmap noborder;
SERIES Y = PPS_NAME X = Performance_Goal/LINEATTRS=(COLOR=firebrick thickness=3) CURVELABEL="Performance Goal=&Target." CURVELABELPOS=end CURVELabelattrs=(size=8pt weight=bold color=firebrick);
Scatter Y=PPS_NAME X = MY0R/markerAttrs=(COLOR=gainsboro size=6mM symbol=CIRCLEFILLED) attrid=MY0R;
Scatter Y=PPS_NAME X = MY1R/markerAttrs=(size=6mM) group=ALL_PPS attrid=ALL_PPS datalabel=MY1R DATALABELATTRS=(size=9pt Family=ArialNarrow weight=bold COLOR=White) DATALABELPOS=CENTER transparency=0.1 discreteoffset=0.12;
Scatter Y=PPS_NAME X = MY2R/markerAttrs=(size=6mM) group=Met_AIT attrid=Met_T datalabel=MY2R DATALABELATTRS=(size=9pt Family=ArialNarrow weight=bold COLOR=White) DATALABELPOS=CENTER transparency=0.1 discreteoffset=0.12;
XAXIS  label=' ';
YAXIS  LABEL = ' ' labelattrs=(color=White) valueattrs=(size=8pt Family=Arial color=black ) GRID gridattrs=(color=WhiteSmoke) MINORGRIDATTRS=(color=green pattern=longdash thickness=2);
keylegend 'Percent'/noborder;
title "&sheet.";
run;


Ods excel close;
%end;
%mend subdat;
%subdat;

 

Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@hanyi0221 wrote:

Hi Cynthia, 

My earlier reply means I cannot use the following code from you in my macro since I only have one procedure (same procedure for all the sheets) and I don't want to write it 10 times so I guess I can only output 10 different files instead of 10 sheets under same file name.

 


That statement is NOT true.  Your original posting is executing a separate PROC SGPLOT step for each iteration of the macro %DO loop. Just move the ODS statement with FILE= option outside of the %DO loop and leave the ODS statements with the SHEET_NAME= option inside the %DO loop.

View solution in original post

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

Hi:
Every time you invoke ODS EXCEL with FILE=, it creates a NEW file. If you want to create 1 workbook with many worksheets, the basic model is:

ods excel file='wholeWB.xlsx';

ods excel options(sheet_name="name1");
...code for procedure 1 ...;

ods excel options(sheet_name="name2");
...code for procedure 2...

ods excel options(sheet_name="name3");
...code for procedure 3...

ods excel close;

That should give you some idea of the basic way it works outside of macros or do loops. So if you are going to introduce macros and do loops, you need to work within the constraints of how ODS EXCEL works.

cynthia

hanyi0221
Fluorite | Level 6

Thank you, so I guess I need to produce 10 excel files instead of one file with 10 worksheets. 

Cynthia_sas
SAS Super FREQ

Hi:

  The code I posted was based on your original statement that you needed "one excel and in different work sheets" -- to me that implied one WORKBOOK with multiple WORKSHEETS. So that was the basis for the code sample I posted. If you now want multiple WORKBOOKS, each a separate .XLSX file, with one WORKSHEET in each WORKBOOK, then the model would be different:

** some do loop syntax that sets a changing ## for the FILE= option;
  
ods excel file="workbook_##.xlsx";
*** code for procedure output;
ods excel close;
  
** end do loop syntax;

 

If you are going to produce a uniquely named XLSX file for every iteration of a loop of some kind (probably a Macro %DO loop, then you need to have a macro variable that changes the name every time ODS EXCEL is invoked.

 

cynthia

hanyi0221
Fluorite | Level 6

Hi Cynthia, 

My earlier reply means I cannot use the following code from you in my macro since I only have one procedure (same procedure for all the sheets) and I don't want to write it 10 times so I guess I can only output 10 different files instead of 10 sheets under same file name.

 

ods excel file='wholeWB.xlsx';

ods excel options(sheet_name="name1");
...code for procedure 1 ...;

ods excel options(sheet_name="name2");
...code for procedure 2...

ods excel options(sheet_name="name3");
...code for procedure 3...

ods excel close;

Cynthia_sas
SAS Super FREQ

Hi:

  You could investigate the use of sheet_interval to get a separate sheet based on the procedure output. For example, with BY group processing, I can use sheet_interval='bygroup' to get multiple sheets, as shown below.

Cynthia

 

sheet_interval.png

Tom
Super User Tom
Super User

@hanyi0221 wrote:

Hi Cynthia, 

My earlier reply means I cannot use the following code from you in my macro since I only have one procedure (same procedure for all the sheets) and I don't want to write it 10 times so I guess I can only output 10 different files instead of 10 sheets under same file name.

 


That statement is NOT true.  Your original posting is executing a separate PROC SGPLOT step for each iteration of the macro %DO loop. Just move the ODS statement with FILE= option outside of the %DO loop and leave the ODS statements with the SHEET_NAME= option inside the %DO loop.

hanyi0221
Fluorite | Level 6

Thanks Cynthia and Tom,  it works now.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 3083 views
  • 1 like
  • 3 in conversation