How to change execute order in SAS DO loop.

[Pysiek]

Hi Guys,

I have a macro with do loop inside. I would like to change order of executing do loop. I need to run first proc report (where = _trt =1)  five times (for each age_n group) and then execute second proc report five times also for each age_n group. Dummy code is presented below. Thanks for your help !

Order of proc report should be like :

_trt 1 , age_n =1 

_trt 1 , age_n =2 

_trt 1 , age_n =3 

_trt 1 , age_n =4 

_trt 1 , age_n =5 

_trt 2 , age_n =1 

_trt 2 , age_n =2

_trt 2 , age_n =3 

_trt 2 , age_n =4 

_trt 2 , age_n =5 

data a;
do i = 1 to 5;
y = i;
_trt=1;
output;
end;
run;

data b;
do i = 1 to 5;
y = i;
_trt=2;
output;
end;
run;

data c;
set a b;
rename i = age_n;
run;

proc sql noprint;
select count(distinct age_n) into: n from c;
quit;

options spool mprint mlogic symbolgen;

%macro test;

%do i=1 %to &n;

proc report data=c (where=(_trt=1 and age_n=&i)) headline missing nowindows split='|';
by age_n;
column age_n y _trt;
define _trt / display;
define age_n / display;
define y / display;
run;

proc report data=c (where=(_trt=2 and age_n=&i)) headline missing nowindows split='|';
by age_n;
column age_n y _trt;
define _trt / display;
define nage / display;
define y / display;
run;

%end;

%mend;

%test;

[Kurt_Bremser]

data c;
do _trt = 1 to 2;
  do age_n = 1 to 5;
    y = age_n;
    output;
  end;
end;
run;

proc report data=c headline missing nowindows split='|';
by _trt age_n;
column age_n y _trt;
define _trt / display;
define age_n / display;
define y / display;
run;

No macro needed at all.

[emrancaan · Accepted solution]

TRY This one. I have amended a bit more.

data a;
do i = 1 to 5;
y = i;
_trt=1;
output;
end;
run;

 

data b;
do i = 1 to 5;
y = i;
_trt=2;
output;
end;
run;


data have;
set a b;
rename i = age_n;
run;

proc sort data=have; by _trt age_n ; run;

proc sql noprint;
select count(*) into: n from have;
quit;


options spool mprint mlogic symbolgen;

%macro test;

%do i=1 %to &n;
		proc sql noconstdatetime noprint;
			select _trt, age_n  into :trt, :age
            from have 
            where monotonic() between &i and &i  
        ;quit;


proc report data=have (where=(_trt=&trt and age_n=&age)) headline missing nowindows split='|';
by _trt age_n;
column age_n y _trt;
define _trt / display;
define age_n / display;
define y / display;
run;

%end;
 
%mend;

[Pysiek]

Thanks guys ! both methods are working 🙂