Nested Loop and splitting

[LB]

Hi all; 

So I am attempting to run macro loops within each other-

In this example I am using the sashelp.baseball dataset as I can't use company data but the concept and results are the same-

So in this case-the first loop outputs data from the league (American,National) and outputs via proc report into separate excel documents. This part works fine- 

It's the nested loop that becomes the issue where for each league report-I am attempting to output a proc shewart graph for each team in the  league on separate tabs- 

However, while it produces separate excel workbooks for each league, it simply repeats the same team shewart graph for each tab. 

(ie. for the National League it outputs Chicago 12 times)  

I am sure I am missing something simple. 

Code follows below- 

Assistance much appreciated. 

Lawrence

proc sort data=SASHELP.baseball out=BASEBALL;
by YrMajor;
run;
%macro ambulA (data=, var=);
proc sort data=&data(keep=&var) out=values nodupkey;
by &var;
run;
data _null_;
set values end=last;
call symputx('facid'||left(_n_),&var);

if last then call symputx('count1',_n_, 'g');
run;
%put _local_;

DATA _NULL_ ;
select(&var);
%do i=1 %to &count1;

options center;
ods listing close;

title ' ';

footnote1 j=l H=1 ' ';

title1 H=4 "DATA Statistics";
title2 H=4 "FOR &&facid&i";

ods excel file="c:\temp\&&facid&i...xlsx" style=Seaside options(sheet_name="league" SHEET_INTERVAL= 'PAGE' autofilter='1' FROZEN_ROWHEADERS='no'
GRIDLINES='ON' embedded_titles='yes' embedded_footnotes='yes');

proc report data=baseball (where=(League= "&&facid&i")) headline headskip nowd spanrows
style (column)={background=white just=center color=black font =("arial",7.5pt)}
style (header)={background=white just=center color=black font =("arial",8.0pt)};

columns League YrMajor Team nRuns ;
run;

proc sql;
create table toch as select distinct Team,YrMajor,sum(nruns) as nruns
from baseball
where League= "&&facid&i"
group by Team,YrMajor;
quit;

%macro ambulB (data=, varx=);
proc sort data=&data(keep=&varx) out=valuesx nodupkey;
by &varx;
run;
data _null_;
set valuesx end=last;
call symputx('unit'||left(_n_),&varx);
if last then call symputx('count3',_n_,'g');
run;
%put _local_;

DATA _NULL_ ;
select(&varx);
%do J=1 %to &count3;

symbol2 c=blue value=dot h=3.0 pct;

ods excel style=Seaside options(sheet_name="&&unit&i Control Chart" SHEET_INTERVAL= 'PAGE' autofilter='1' FROZEN_ROWHEADERS='no'
embedded_titles='yes' embedded_footnotes='yes' embed_titles_once = 'on'
embedded_titles='on'
gridlines='off' START_AT='3,3');

Title "&&unit&i Control Chart";

proc shewhart data=toch (where=(TEAM="&&unit&i"));
xchart nruns * YrMajor/nolegend MARKERS ;
run;

%end;
%mend ambulB;
%ambulB (data=toch, varx=team);

ODS _ALL_ Close;
%end;
%mend;
%ambulA(data=Baseball, var=League);

[ShenQicheng]

Are you sure the code you posted can be executed without error?

(It is highly recommended that you post your code with 'Insert sas code' commad so that it can be more readable.)

I can't run you code but I think the reason might be the macro call in the inner loop is only pushed into a stack and won't be executed until the data step is completed.

Try 'call exectute' or 'dosubl'.

Shen

[SuryaKiran · Accepted solution]

Your syntax is invalid. Why do you have these?

DATA _NULL_ ;
select(&varx);

remove these and try.

[SuryaKiran]

ods excel style=Seaside options(sheet_name="&&unit&i Control Chart" SHEET_INTERVAL= 'PAGE' autofilter='1' FROZEN_ROWHEADERS='no'
embedded_titles='yes' embedded_footnotes='yes' embed_titles_once = 'on'
embedded_titles='on'
gridlines='off' START_AT='3,3');

Title "&&unit&i Control Chart";

proc shewhart data=toch (where=(TEAM="&&unit&i"));
xchart nruns * YrMajor/nolegend MARKERS ;
run;

You need to have "j" instead of "i" here.

[LB]

Ah-thanks!

Lawrence