how to use a looping to create a subset to feed in a macro?

[stataq]

Hello,

I have a dataset which has 3 level of 'Subject' and 5 levels of 'test'. I would like to create a looping code to create summary tables by utilize  an exisiting macro 'get_sum'. How can I do that.

I am thinking of sth like below:

do i=1 to 3;

do j=1 to 5;

%get_sum(indata=have; subset=subject eq i and test eq j);

end;

end;

I am new to sas and not sure how to handle looping situation. Could anyone guide me on this?

[ballardw]

How about providing an example of the data you have, or at least a workable dummy, and what you expect as a result for that.

As a minimum you should include the code for the "get_sum" macro.

Quite often macro code is not needed at all and when appropriate looping may not be needed either.

Typically in SAS a report based on levels such as "as subject identifier" would use BY group processing to use each level.

Depending on exactly what a "summary table" needs to look like this might be better done by Proc Tabulate or Report than some not-yet-defined macro.

[PaigeMiller]

---

@stataq wrote:

Hello,

I have a dataset which has 3 level of 'Subject' and 5 levels of 'test'. I would like to create a looping code to create summary tables by utilize  an exisiting macro 'get_sum'.

---

Don't split the data. Use a BY statement in PROC MEANS or PROC SUMMARY to get the sums.

[yabwon]

You didn't provide the data and macro code, that's why I'm doing some assumptions:

/* assuming this is your data */
data have;
  do subject=1 to 3;
    do test=1 to 5;
      do value=1 to sum(subject+test);
        output;
      end;
    end;
  end;
run;

/* and assuming this (looking at name) is your macro */
%macro get_sum(indata=have, subset=1, result=want);
proc sql;
create table &result. as
select 
 subject, test, sum(value) as sumof_value
from 
  &indata.
where
  (&subset.)
;
quit;
%mend get_sum;

Having those I guess you want to do something like:

data _null_;
  do i=1 to 3;
    do j=1 to 5;
      call execute(
        '%nrstr(%get_sum(indata=have,' !!
         catx(" ",'subset=subject eq ', i, 'and test eq ', j, ',') !!
         'result=want_'!!catx("_",i,j) !!
         '))'
      );
    end;
  end;
run;

But! As @PaigeMiller already mentioned (and I 100% agree) it would be much wiser to use "by group processing".

In case SQL it would be something like this:

proc sql;
create table want as
select 
 subject, test, sum(value) as sumof_value
from 
  have

  GROUP BY
   subject, test
;
quit;

with GROUP BY clause.  And for statistical procedures something like BY clause, or CLASS clause (if there is one).

Bart