topic how to use a looping to create a subset to feed in a macro? in SAS Programming

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

stataq — Wed, 21 Feb 2024 20:39:47 GMT

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;

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

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

ballardw — Wed, 21 Feb 2024 21:29:30 GMT

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.

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

PaigeMiller — Thu, 22 Feb 2024 10:34:22 GMT

@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.

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

yabwon — Thu, 22 Feb 2024 10:52:08 GMT

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