topic Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n in Graphics Programming

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

hanyi0221 — Wed, 27 Dec 2017 14:12:14 GMT

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!

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

Cynthia_sas — Wed, 27 Dec 2017 14:39:25 GMT

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

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

hanyi0221 — Thu, 28 Dec 2017 14:16:55 GMT

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

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

Cynthia_sas — Thu, 28 Dec 2017 16:47:53 GMT

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

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

hanyi0221 — Mon, 08 Jan 2018 20:32:24 GMT

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;

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

Cynthia_sas — Mon, 08 Jan 2018 21:15:00 GMT

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

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

Tom — Mon, 08 Jan 2018 21:46:26 GMT

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

Re: SGplot - macro do loop produce many graphs and output in one excel file with identical sheets' n

hanyi0221 — Tue, 09 Jan 2018 18:32:37 GMT

Thanks Cynthia and Tom, it works now.