DO LOOP for several procedure

[hhchenfx]

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;

[FreelanceReinh]

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;

[Astounding]

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.

[FreelanceReinh · Accepted solution]

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;

[hhchenfx]

That code teaches me a lot!

Thank you.

HHC