SAS macro that loops over vectors instead of variables

Question

First of all, my dataset looks like this:

group_name person1...person15 district Dolphin Tim...Lucy YY Star Jake...Kate ZZ

I have a 20 lists of teams:

TeamA = 'Tim', 'Sally', 'Anne';
TeamB = 'Jake', 'Lucy', 'Lily;
TeamC= 'Andy', 'Luke', 'Tom').

I want to look at only TeamA and TeamB first and see how many groups have exactly one person from TeamA and nobody from TeamB (k=1), how many have more than one person from TeamA and nobody from TeamB (k>1), and how many groups have at least one person from TeamA and TeamB (k=-10). I want to do this separately by district (there's only YY and ZZ).

Here's the code that does that:

%let TeamA = 'Tim','Sally','Anne';
%let TeamB = 'Jake', 'Lucy', 'Lily;


%macro doloop;
  data table_1;
    set table;
  format k 3.;
  k = 0;  
array person[15] person1-person15;
do i = 1 to 15;
 if person[i] in (&TeamB.) and k>=1 then k = -10;
  else if person[i] in (&TeamB.) then k = -2;
  else if person[i] in (&TeamA.) and k < 0 then k=-10;
  else if person[i] in (&TeamA.) then do;
    k = k+1;
   end;
  end;
 run;
%mend doloop;

%doloop;

%macro dividebydistrict;

%let district1= YY ZZ;
%do count= 1 %to 2;            
%let dist=%scan(&district1,&count);

proc sql;
 create table summedbydistrict_1 as
    select count(*) as &dist. format commax12. from table_1
        where district="&dist." and k=1
 quit;
%end;

%mend dividebydistrict;
%dividebydistrict;

 %macro dividebydistrict2;

%let district1= YY ZZ;
%do count= 1 %to 2;            
%let dist=%scan(&district1,&count);

proc sql;
 create table summedbydistrict_2 as
    select count(*) as &dist. format commax12. from table_1
        where district="&dist." and k>1
 quit;
%end;

 %mend dividebydistrict2;
%dividebydistrict2;

%macro dividebydistrict3;

%let district1= YY ZZ;
%do count= 1 %to 2;            
%let dist=%scan(&district1,&count);

proc sql;
 create table summedbydistrict_3 as
    select count(*) as &dist. format commax12. from table_1
        where district="&dist." and k=-10
 quit;
%end;

%mend dividebydistrict3;
%dividebydistrict3;

This works, but now I don't want to just do it for TeamA and TeamB, I want about 40 different combinations (not every possibility). I'd ideally add two variables that loop over the options I want: TeamA-TeamB (what the above code does) TeamA-TeamC TeamC-TeamD For each option, the results would be saved in a table, maybe called summedbydistrict_3_TeamA_TeamB, summedbydistrict_3_TeamA_TeamC, and so on.

(Edited to add more info)

Answer 1

If you want the treat the value of %scan(TeamA TeamB TeamD,1) as the NAME of a macro variable then do that.

So if you have

%let team1 = TeamA TeamB TeamD TeamH TeamA TeamJ TeamM;
%let TeamA = (Tim, Sally, Anne);

And you want to get the list of members of the first team listed in &TEAM1 then first get the name of the team.

%let team_name = %scan(&team1,1,%str( ));

Then use that to retrieve the value of the macro variable with that name.

%let members = &&&team_name ;

But what do you want to do with the string

(Tim, Sally, Anne)

it is not clear to me. Do you want use that string as part of an expression using the IN operator?

where player in &members

Which would generate the code

where player in (Tim, Sally, Anne)

Which might work in macro code if you have enabled the IN operator and set the delimiter to a comma:

where &player in &members

But will not work in SAS code without quotes around the names.

%let TeamA = ('Tim', 'Sally', 'Anne');