topic Re: DO LOOP for several procedure in SAS Programming

DO LOOP for several procedure

hhchenfx — Thu, 04 Feb 2016 00:29:38 GMT

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;

Re: DO LOOP for several procedure

FreelanceReinh — Wed, 03 Feb 2016 17:35:15 GMT

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;

Re: DO LOOP for several procedure

Astounding — Wed, 03 Feb 2016 17:39:43 GMT

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.

Re: DO LOOP for several procedure

FreelanceReinh — Wed, 03 Feb 2016 18:00:13 GMT

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;

Re: DO LOOP for several procedure

hhchenfx — Thu, 04 Feb 2016 00:28:55 GMT

That code teaches me a lot!

Thank you.

HHC