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;
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;
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.
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;
Thanks guys ! both methods are working 🙂
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.