DATA Step, Macro, Functions and more

DO LOOP for several procedure

Accepted Solution Solved
Reply
Super Contributor
Posts: 420
Accepted Solution

DO LOOP for several procedure

[ Edited ]

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;

 


Accepted Solutions
Solution
‎02-03-2016 07:27 PM
Trusted Advisor
Posts: 1,118

Re: DO LOOP for several procedure

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,118

Re: DO LOOP for several procedure

[ Edited ]

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;
Super User
Posts: 5,516

Re: DO LOOP for several procedure

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.

Solution
‎02-03-2016 07:27 PM
Trusted Advisor
Posts: 1,118

Re: DO LOOP for several procedure

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;
Super Contributor
Posts: 420

Re: DO LOOP for several procedure

Posted in reply to FreelanceReinhard

That code teaches me a lot!

Thank you.

HHC

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 394 views
  • 0 likes
  • 3 in conversation