如何在SAS或PROC SQL(DB2)中实现数据集的合并分组？

我有如下数据集：

我期望将数据按Id、com、typ、cust、bu、tar、item、item_sufx、part分组后进行列转行，把不同dtn_cd对应的geo_con分别转为FROM_GEO（dtn_cd=1）、TO_GEO（dtn_cd=2）、BETWEEN_GEO（dtn_cd=3）、AMONGST_GEO（dtn_cd=4）列，同时保留对应行的sign和line信息。

我尝试了以下代码但未能正常运行：

data table_A;
set table_A;
merge
table_A (where= (DCTN_CD = '1') rename=(GEO_CON=FROM_GEO))
table_A (where= (DCTN_CD = '2') rename=(GEO_CON=TO_GEO))
table_A (where= (DCTN_CD = '3') rename=(GEO_CON=BETWEEN_GEO))
table_A (where= (DCTN_CD = '4') rename=(GEO_CON=AMONGST_GEO))
run;

请问该如何实现目标输出？我接受SAS或PROC SQL(DB2)的解决方案。

一、SAS 实现方法 #

方法1：PROC TRANSPOSE 多步转置（适配同一分组多同行场景） #

先给同一分组内的相同dtn_cd生成序号，再分别转置geo_con、sign、line字段，最后合并结果：

/* 生成同分组同dtn_cd的行序号 */
data temp;
    set table_A;
    by Id com typ cust bu tar item item_sufx part dtn_cd;
    if first.dtn_cd then seq = 0;
    seq + 1;
run;

/* 转置geo_con字段 */
proc transpose data=temp out=trans_geo prefix=GEO_;
    by Id com typ cust bu tar item item_sufx part seq;
    id dtn_cd;
    var geo_con;
run;

/* 转置sign字段 */
proc transpose data=temp out=trans_sign prefix=SIGN_;
    by Id com typ cust bu tar item item_sufx part seq;
    id dtn_cd;
    var sign;
run;

/* 转置line字段 */
proc transpose data=temp out=trans_line prefix=LINE_;
    by Id com typ cust bu tar item item_sufx part seq;
    id dtn_cd;
    var line;
run;

/* 合并转置结果并重命名字段 */
data final;
    merge trans_geo trans_sign trans_line;
    by Id com typ cust bu tar item item_sufx part seq;
    rename GEO_1=FROM_GEO GEO_2=TO_GEO GEO_3=BETWEEN_GEO GEO_4=AMONGST_GEO
           SIGN_1=FROM_SIGN SIGN_2=TO_SIGN SIGN_3=BETWEEN_SIGN SIGN_4=AMONGST_SIGN
           LINE_1=FROM_LINE LINE_2=TO_LINE LINE_3=BETWEEN_LINE LINE_4=AMONGST_LINE;
    drop _NAME_ seq;
run;

方法2：数据步合并（修正原代码问题） #

原代码缺少合并键，导致无法匹配分组，需指定分组变量作为合并依据：

data final;
    merge table_A(where=(dtn_cd=1) rename=(geo_con=FROM_GEO sign=FROM_SIGN line=FROM_LINE))
          table_A(where=(dtn_cd=2) rename=(geo_con=TO_GEO sign=TO_SIGN line=TO_LINE))
          table_A(where=(dtn_cd=3) rename=(geo_con=BETWEEN_GEO sign=BETWEEN_SIGN line=BETWEEN_LINE))
          table_A(where=(dtn_cd=4) rename=(geo_con=AMONGST_GEO sign=AMONGST_SIGN line=AMONGST_LINE));
    by Id com typ cust bu tar item item_sufx part;
run;

注：若同一分组+同一dtn_cd存在多行，此方法会生成笛卡尔积，优先推荐TRANSPOSE方法。

二、DB2 PROC SQL 实现方法 #

使用条件聚合（CASE WHEN）实现列转行，按分组键聚合后提取不同dtn_cd的字段：

SELECT 
    Id,
    com,
    typ,
    cust,
    bu,
    tar,
    item,
    item_sufx,
    part,
    -- 合并同一分组dtn_cd=1的多行数据，用分号分隔（单行场景可替换为MAX(CASE...)）
    LISTAGG(CASE WHEN dtn_cd=1 THEN geo_con END, ';') WITHIN GROUP(ORDER BY line) AS FROM_GEO,
    LISTAGG(CASE WHEN dtn_cd=1 THEN sign END, ';') WITHIN GROUP(ORDER BY line) AS FROM_SIGN,
    LISTAGG(CASE WHEN dtn_cd=1 THEN line END, ';') WITHIN GROUP(ORDER BY line) AS FROM_LINE,
    -- 处理dtn_cd=2的字段
    LISTAGG(CASE WHEN dtn_cd=2 THEN geo_con END, ';') WITHIN GROUP(ORDER BY line) AS TO_GEO,
    LISTAGG(CASE WHEN dtn_cd=2 THEN sign END, ';') WITHIN GROUP(ORDER BY line) AS TO_SIGN,
    LISTAGG(CASE WHEN dtn_cd=2 THEN line END, ';') WITHIN GROUP(ORDER BY line) AS TO_LINE,
    -- 处理dtn_cd=3的字段
    LISTAGG(CASE WHEN dtn_cd=3 THEN geo_con END, ';') WITHIN GROUP(ORDER BY line) AS BETWEEN_GEO,
    LISTAGG(CASE WHEN dtn_cd=3 THEN sign END, ';') WITHIN GROUP(ORDER BY line) AS BETWEEN_SIGN,
    LISTAGG(CASE WHEN dtn_cd=3 THEN line END, ';') WITHIN GROUP(ORDER BY line) AS BETWEEN_LINE,
    -- 处理dtn_cd=4的字段
    LISTAGG(CASE WHEN dtn_cd=4 THEN geo_con END, ';') WITHIN GROUP(ORDER BY line) AS AMONGST_GEO,
    LISTAGG(CASE WHEN dtn_cd=4 THEN sign END, ';') WITHIN GROUP(ORDER BY line) AS AMONGST_SIGN,
    LISTAGG(CASE WHEN dtn_cd=4 THEN line END, ';') WITHIN GROUP(ORDER BY line) AS AMONGST_LINE
FROM 
    table_A
GROUP BY 
    Id, com, typ, cust, bu, tar, item, item_sufx, part;

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