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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.