SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' name?

[hanyi0221]

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!

[Cynthia_sas]

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]

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

[Cynthia_sas]

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]

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]

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

[Tom · Accepted solution]

---

@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]

Thanks Cynthia and Tom,  it works now.