如何使用SAS PROC SQL处理父子关系表，生成SAS Viya仪表盘所需层级排序表？

核心思路 #

利用SAS的递归CTE（公共表表达式）遍历树形结构，分别从根节点向下、子节点向上生成所有层级关联记录，同时计算每个节点的层级（sort_level）和从根到节点的排序链（sort_chain），满足仪表盘分组排序需求。

示例代码 #

1. 模拟原始数据并预处理

先处理空parent_cid为自身（对应你已完成的table1步骤）：

/* 模拟原始父子关系表 */
data original_companies;
  input cid $ parent_cid $;
  datalines;
C1 
C2 C1
C3 C1
C4 C2
C5 C4
C6 C3
;
run;

/* 处理空parent_cid为自身，生成table1 */
data table1;
  set original_companies;
  if missing(parent_cid) then parent_cid = cid;
run;

2. 递归生成层级结构表

通过两次递归遍历，覆盖所有父级、子级关联：

proc sql;
  create table company_hierarchy as
  /* 递归1：从根节点向下遍历，生成子节点的层级与路径 */
  with recursive hierarchy (cid, parent_cid, sort_level, sort_chain, root_cid) as (
    select 
      cid, 
      parent_cid, 
      1 as sort_level, 
      cid as sort_chain,
      cid as root_cid
    from table1
    where cid = parent_cid

    union all

    select 
      t.cid, 
      t.parent_cid, 
      h.sort_level + 1 as sort_level,
      cats(h.sort_chain, '.', t.cid) as sort_chain,
      h.root_cid as root_cid
    from hierarchy h
    inner join table1 t
      on h.cid = t.parent_cid
      and t.cid ne t.parent_cid /* 避免循环引用 */
  )
  select * from hierarchy

  /* 合并递归2：从子节点向上遍历，补充父级关联记录 */
  union all
  with recursive reverse_hierarchy (cid, child_cid, sort_level_rev, sort_chain_rev, root_cid) as (
    select 
      cid, 
      cid as child_cid, 
      1 as sort_level_rev, 
      cid as sort_chain_rev,
      cid as root_cid
    from table1

    union all

    select 
      t.parent_cid, 
      r.child_cid, 
      r.sort_level_rev + 1 as sort_level_rev,
      cats(t.parent_cid, '.', r.sort_chain_rev) as sort_chain_rev,
      r.root_cid as root_cid
    from reverse_hierarchy r
    inner join table1 t
      on r.cid = t.cid
      and t.parent_cid ne t.cid /* 避免循环引用 */
  )
  select 
    child_cid as cid,
    cid as parent_cid,
    sort_level_rev as sort_level,
    sort_chain_rev as sort_chain,
    root_cid
  from reverse_hierarchy
  where child_cid ne cid; /* 排除自身关联，避免重复 */
quit;

关键字段说明 #

• sort_level：节点在树形结构中的层级（根节点为1，子节点依次递增）
• sort_chain：从根节点到当前节点的路径字符串（如C1.C2.C4），用于仪表盘的层级排序
• root_cid：当前节点所属的根节点ID，用于同组节点的分组

注意事项 #

• 如果数据存在循环引用（如A的父是B，B的父是A），需保留ne判断避免无限递归
• sort_chain格式可自定义（如用数字、下划线分隔），只要保证排序逻辑符合树形结构即可
• 若仅需每个节点的完整路径（无需所有父/子关联），可只保留第一个递归CTE部分

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