使用SAS实现多账户按ID聚合多层关联ID至单行记录

现有SAS数据集存在多层级关联ID（如2001关联2001b，2001b又关联2001c），需要生成每个根ID一行的宽表，展示所有层级的关联ID。多次PROC SQL左连接的方式无法适配未知的层级数量，以下提供两种高效实现方法：宏循环自动生成连接、DATA步哈希表+数组循环。

示例数据 #

原始数据集（Have） #

目标数据集（Want） #

现有尝试代码 #

proc sql;
   create table Relid1 as select 
   a.id,b.rel_id 
   from Have a
   left join Have b 
   on a.Rel_id=b.Id;
quit;

方法一：宏循环自动适配多层关联 #

通过递归CTE计算最大关联层级，再用宏循环动态生成SQL左连接语句，无需手动指定层级数。

代码实现 #

/* 1. 筛选根节点：未出现在Rel_ID列中的ID，即每个关联链条的起点 */
proc sql noprint;
    create table root_ids as
    select distinct id from Have
    where id not in (select rel_id from Have);
quit;

/* 2. 用递归CTE计算每个根节点的最长关联链条，得到最大层级数 */
proc sql noprint;
    with recursive chain as (
        select id as root, id, rel_id, 1 as level 
        from Have 
        where id in (select id from root_ids)
        union all
        select c.root, h.id, h.rel_id, c.level + 1 as level
        from chain c
        join Have h on c.rel_id = h.id
    )
    select max(level) into :max_level
    from chain;
quit;

/* 3. 宏循环生成多层左连接的SQL语句 */
%macro expand_relations;
proc sql;
    create table want as
    select r.id
    %do i=1 %to &max_level;
        , rel&i..rel_id as rel_id&i
    %end;
    from root_ids r
    %do i=1 %to &max_level;
        left join Have rel&i
        on %if &i=1 %then r.id=rel&i..id;
        %else rel%eval(&i-1)..rel_id=rel&i..id;
    %end;
    ;
quit;
%mend;

/* 执行宏 */
%expand_relations;

方法二：DATA步哈希表+数组循环 #

利用SAS哈希表实现快速查找，结合数组存储每一层关联ID，循环递归直到无后续关联，性能更优，适合大数据量场景。

代码实现 #

/* 先通过递归CTE获取最大层级数，动态定义数组长度 */
proc sql noprint;
    with recursive chain as (
        select id as root, id, rel_id, 1 as level 
        from Have 
        where id not in (select rel_id from Have)
        union all
        select c.root, h.id, h.rel_id, c.level + 1 as level
        from chain c
        join Have h on c.rel_id = h.id
    )
    select max(level) into :max_level
    from chain;
quit;

/* 用哈希表快速查找关联ID，数组存储各层级结果 */
data want;
    if _n_=1 then do;
        /* 加载原始数据到哈希表，用于快速查询 */
        declare hash rel(dataset:'Have');
        rel.defineKey('id');
        rel.defineData('rel_id');
        rel.defineDone();
        call missing(id, rel_id);
    end;
    /* 读取根节点 */
    set root_ids;
    /* 动态定义关联ID列 */
    length %do i=1 %to &max_level; rel_id&i $20 %end;;
    array rels[&max_level] rel_id1-rel_id&max_level;
    
    current_id = id;
    /* 循环查找每一层关联ID */
    do i=1 to dim(rels);
        if rel.find(key:current_id) = 0 then do;
            rels[i] = rel_id;
            current_id = rel_id;
        end;
        else leave; /* 无后续关联则退出循环 */
    end;
    output;
run;

方法对比 #

• 宏循环SQL：代码结构直观，适合熟悉SQL的用户，自动适配层级数，但层级过多时SQL语句会冗长，性能略逊。
• DATA步哈希表：哈希查找速度快，循环处理灵活，大数据量下性能更优，代码逻辑更贴近底层数据处理。

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