DATA Step, Macro, Functions and more

Nested Loop and splitting

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 161
Accepted Solution

Nested Loop and splitting

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);

 

 


Accepted Solutions
Solution
‎04-19-2018 11:55 AM
PROC Star
Posts: 631

Re: Nested Loop and splitting

Your syntax is invalid. Why do you have these?

DATA _NULL_ ;
select(&varx);

 

remove these and try.

Thanks,
Suryakiran

View solution in original post


All Replies
Contributor
Posts: 37

Re: Nested Loop and splitting

[ Edited ]

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

Solution
‎04-19-2018 11:55 AM
PROC Star
Posts: 631

Re: Nested Loop and splitting

Your syntax is invalid. Why do you have these?

DATA _NULL_ ;
select(&varx);

 

remove these and try.

Thanks,
Suryakiran
PROC Star
Posts: 631

Re: Nested Loop and splitting

[ Edited ]

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.

Thanks,
Suryakiran
Regular Contributor
Regular Contributor
Posts: 161

Re: Nested Loop and splitting

Posted in reply to SuryaKiran

Ah-thanks!

 

Lawrence 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 151 views
  • 2 likes
  • 3 in conversation