SAS宏改造需求：实现多团队组合的批量区域分组统计

问题背景与现有实现 #

1. 数据集结构 #

数据集包含group_name、person1至person15、district字段，示例数据如下：

2. 团队列表 #

现有20个团队定义，示例如下：

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

3. 现有代码功能 #

已编写SAS宏代码，可针对TeamA与TeamB的组合，按区域（仅YY、ZZ）统计三类分组的数量：

• 仅含1名TeamA成员且无TeamB成员的组（标记k=1）
• 含多名TeamA成员且无TeamB成员的组（标记k>1）
• 同时含TeamA与TeamB成员的组（标记k=-10）

现有代码如下：

%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;

需求说明 #

现有代码仅支持TeamA-TeamB一组组合的统计，需改造为支持约40种指定团队组合（如TeamA-TeamC、TeamC-TeamD等）的批量处理，每种组合的统计结果需保存为独立命名的表格（如summedbydistrict_3_TeamA_TeamB、summedbydistrict_3_TeamA_TeamC），即需将SAS宏改为基于团队组合向量循环而非单变量循环。

改造方案 #

1. 定义团队组合列表 #

用宏变量存储所有需要处理的团队组合，每个组合以TeamX-TeamY格式用空格分隔：

%let team_pairs = TeamA-TeamB TeamA-TeamC TeamC-TeamD; /* 补充剩余37种组合 */

2. 重构核心宏，支持传入团队参数 #

将原逻辑封装为可接收两个团队参数的宏，同时生成带组合标识的中间表与结果表：

%macro process_team_pair(team1, team2);
  /* 生成带组合名称的中间表，例如 table_TeamA_TeamB */
  data table_&team1._&team2.;
    set table;
    format k 3.;
    k = 0;  
    array person[15] person1-person15;
    do i = 1 to 15;
      if person[i] in (&&&team2.) and k>=1 then k = -10;
      else if person[i] in (&&&team2.) then k = -2;
      else if person[i] in (&&&team1.) and k < 0 then k=-10;
      else if person[i] in (&&&team1.) then do;
        k = k+1;
      end;
    end;
  run;

  %let districts = YY ZZ;
  /* 统计k=1的分组 */
  proc sql;
    create table summedbydistrict_1_&team1._&team2. as
      select 
        district,
        count(*) as group_count format commax12.
      from table_&team1._&team2.
      where k=1 and district in ("&districts.")
      group by district;
  quit;

  /* 统计k>1的分组 */
  proc sql;
    create table summedbydistrict_2_&team1._&team2. as
      select 
        district,
        count(*) as group_count format commax12.
      from table_&team1._&team2.
      where k>1 and district in ("&districts.")
      group by district;
  quit;

  /* 统计k=-10的分组 */
  proc sql;
    create table summedbydistrict_3_&team1._&team2. as
      select 
        district,
        count(*) as group_count format commax12.
      from table_&team1._&team2.
      where k=-10 and district in ("&districts.")
      group by district;
  quit;
%mend process_team_pair;

3. 循环处理所有团队组合 #

通过宏循环遍历团队组合列表，拆分出两个团队后调用核心宏：

%macro process_all_pairs;
  %let pair_count = %sysfunc(countw(&team_pairs.));
  %do i = 1 %to &pair_count.;
    %let current_pair = %scan(&team_pairs., &i.);
    %let team1 = %scan(&current_pair., 1, -);
    %let team2 = %scan(&current_pair., 2, -);
    %process_team_pair(&team1., &team2.);
  %end;
%mend process_all_pairs;

/* 执行批量处理 */
%process_all_pairs;

关键改造说明 #

• 用&&&team1.实现宏变量间接引用，通过&team1获取团队名称后，进一步调用对应的成员列表宏变量。
• 将三个重复的统计逻辑合并到核心宏，减少代码冗余。
• 所有中间表和结果表均带TeamX_TeamY后缀，确保不同组合的结果独立区分。
• 统计结果改为按区域分组输出，包含区域名称与对应计数，比原单字段表更具实用性。

内容的提问来源于stack exchange，提问作者TierTe