Hi Everyone,
My data has variable exit1, exit2 ....
(The actual name in the form section_&i_part_&j so I will have to do double Do loop)
I want to run a procedure for all exit variables. It should be like
do n=1 to 100;
...
proc means; output out=table&n...
end;
and I want to merge all table&n into 1 summary file.
Could you please help me with that?
Thank you so much.
HHC
data have; input id_order enter exit1 exit2 exit3;
cards;
1 1 5 12 5
2 7 20 7 20
3 11 15 11 15
4 18 22 20 50
5 22 43 22 30
6 25 100 50 100
run;
proc sort data=have;
by enter;
run;
*------DO LOOP n=1 to 3-----------------------------------------------------;
*%let n=1;
data want&n;
set have;
retain lag_exit;
if lag_exit > enter then delete;
else lag_exit = exit&n;
run;
proc means data=want&n;
var exit&n;
output out=table&n
mean=mean_exit&n;
run;
*I WANT TO MERGE ALL 3 MEAN OUTPUT INTO 1 FILE. THIS FILE WILL HAVE 3 VAIRABLEs: mean_exit1 mean_exit2 mean_exit3;
****************final code*********************;
%macro test;
%do n=1 %to 3;
data want&n;
set have;
retain lag_exit;
if lag_exit > enter then delete;
else lag_exit = exit&n;
run;
proc means data=want&n;
var exit&n;
output out=table (drop=_:)
mean=mean_exit&n;
run;
data table; set table;
t=1;
%if &n=1 %then %do;
data all; set table;run;
%end;
%else %do;
proc sql;
create table all
as select * from all left join table
on all.t=table.t;
quit;
%end;
%end;
%mend;
%test;
This could be the template code for your intended double %DO loop (as an example with 2x2 "section_part" variables):
data have;
input id_order enter section_1_part_1 section_1_part_2 section_2_part_1 section_2_part_2;
cards;
1 1 5 12 5 6
2 7 20 7 20 21
3 11 15 11 15 16
4 18 22 20 50 51
5 22 43 22 30 31
6 25 100 50 100 101
run;
%macro comp;
%local i j;
%do i=1 %to 2;
%do j=1 %to 2;
data want;
set have;
retain lag_exit;
if lag_exit > enter then delete;
else lag_exit = section_&i._part_&j;
run;
proc summary data=want;
var section_&i._part_&j;
output out=table(drop=_:)
mean=mean_exit&i._&j;
run;
%if &i=1 and &j=1 %then %do;
data allmeans;
set table;
run;
%end;
%else %do;
data allmeans;
set allmeans;
set table;
run;
%end;
%end;
%end;
%mend comp;
%comp
proc print data=allmeans;
run;
For your sample data you could try this:
%macro comp;
%local n;
%do n=1 %to 3;
data want&n;
set have;
retain lag_exit;
if lag_exit > enter then delete;
else lag_exit = exit&n;
run;
proc summary data=want&n;
var exit&n;
output out=table&n
mean=mean_exit;
run;
proc append base=allmeans0 new=table&n(drop=_:);
run;
%end;
%mend comp;
%comp
proc transpose data=allmeans0 out=allmeans(drop=_:) prefix=mean_exit;
run;
proc print data=allmeans;
run;
Edit: If you don't really need all those WANT1, WANT2, ... and TABLE1, TABLE2, ... datasets, you could simply omit all the &n suffixes, except those in exit&n. Also, please be aware that PROC APPEND is cumulative if you run the above code more than once. To avoid this effect you could replace the PROC APPEND step with:
%if &n=1 %then %do;
data allmeans0;
set table&n(drop=_:);
run;
%end;
%else %do;
proc append base=allmeans0 new=table&n(drop=_:);
run;
%end;
If you have already run the existing code, and now need to combine TABLE1 through TABLE100 after the fact, you could use:
%macro combine (n=100);
%local i;
data want;
%do i=1 %to &n;
set table&i;
%end;
run;
%mend combine;
You may get messages due to the fact that the output data sets from PROC MEANS all contain variables _TYPE_ and _FREQ_. Since you don't need those variables, you can ignore the messages.
Good luck.
This could be the template code for your intended double %DO loop (as an example with 2x2 "section_part" variables):
data have;
input id_order enter section_1_part_1 section_1_part_2 section_2_part_1 section_2_part_2;
cards;
1 1 5 12 5 6
2 7 20 7 20 21
3 11 15 11 15 16
4 18 22 20 50 51
5 22 43 22 30 31
6 25 100 50 100 101
run;
%macro comp;
%local i j;
%do i=1 %to 2;
%do j=1 %to 2;
data want;
set have;
retain lag_exit;
if lag_exit > enter then delete;
else lag_exit = section_&i._part_&j;
run;
proc summary data=want;
var section_&i._part_&j;
output out=table(drop=_:)
mean=mean_exit&i._&j;
run;
%if &i=1 and &j=1 %then %do;
data allmeans;
set table;
run;
%end;
%else %do;
data allmeans;
set allmeans;
set table;
run;
%end;
%end;
%end;
%mend comp;
%comp
proc print data=allmeans;
run;
That code teaches me a lot!
Thank you.
HHC
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.