BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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
PROC Star

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
Jade | Level 19

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
Barite | Level 11

That code teaches me a lot!

Thank you.

HHC

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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