如何在SAS/DB2 SQL中实现动态部分数据转置（无需硬编码）

原数据集结构 #

| desc_cd | item_cd | abc | line_nbr | type_cd | val1 | val2 | val3 |
|---------|---------|-----|----------|---------|------|------|------|
|   100   |   458   | 15  |     1    |    3    |  50  |  0   |  100 |
|   100   |   458   | 15  |     2    |    6    |  0   |  150 |  0   |
|   100   |   458   | 15  |     3    |    9    |  50  |  150 |  0   |
|   225   |   778   | 41  |     1    |    3    |  50  |  0   |  100 |
|   225   |   778   | 41  |     2    |    6    |  0   | 150 |  0   |
|   225   |   778   | 41  |     4    |    12   |  0   | 1990 |  230 |
|   225   |   778   | 41  |     3    |    9    |  50  | 150 |  0   |

目标转置格式 #

| desc_cd | item_cd | abc | line_nbr | type_cd | val1 | val2 | val3 | line_nbr_2 | type_cd_2 | val1_2 | val2_2 | val3_2 | line_nbr_3 | type_cd_3 | val1_3 | val2_3 | val3_3 | line_nbr_4 | type_cd_4 | val1_4 | val2_4 | val3_4 | 
|---------|---------|-----|----------|---------|------|------|------|------------|-----------|--------|--------|--------|------------|-----------|--------|--------|--------|------------|-----------|--------|--------|--------|
|   100   |   458   | 15  |     1    |    3    |  50  |  0   |  100 |       2    |      6    |    0   |  150   |    0   |     3      |    9      |  50    |  150   |  0     |            |           |        |        |        |
|   225   |   778   | 41  |     1    |    3    |  50  |  0   |  100 |       2    |      6    |    0   |  150   |    0   |     4      |    12     |  0     |  1990  |  230   |     3      |    9      |  50    |  150   |  0     |

当前问题 #

现有SAS代码无法得到预期结果，且line_nbr和type_cd取值不固定，不能硬编码列名：

proc transpose data= sample
out= sample_merged;
var desc_cd;
var item_cd;
var abc; 
by line_nbr;
by type_cd;
by val1;
by val2;
by val3;
run;

解决方案 #

SAS实现方法 #

核心思路是先按分组变量（desc_cd, item_cd, abc）生成组内序号，再通过宏或批量转置实现动态列名，避免硬编码。

1. 生成组内序号
   给每个分组内的行分配唯一序号，用于区分不同的重复组：

data sample_with_seq;
    set sample;
    by desc_cd item_cd abc;
    if first.abc then seq = 1;
    else seq + 1;
run;

2. 宏批量转置合并
   用宏循环自动处理所有需要转置的变量，最后合并结果：

%macro transpose_all;
    /* 定义需要转置的变量列表 */
    %let vars = line_nbr type_cd val1 val2 val3;
    
    /* 循环转置每个变量 */
    %do i=1 %to %sysfunc(countw(&vars));
        %let var = %scan(&vars, &i);
        proc transpose data=sample_with_seq out=trans_&var prefix=&var._;
            by desc_cd item_cd abc;
            id seq;
            var &var;
        run;
    %end;

    /* 合并所有转置后的数据集 */
    data final_wide;
        merge trans_:;
        by desc_cd item_cd abc;
    run;
%mend;

/* 执行宏 */
%transpose_all;

DB2 SQL实现方法 #

利用窗口函数生成组内序号，结合动态条件聚合实现宽表转换，无需硬编码列名。

1. 生成组内序号

WITH sample_with_seq AS (
    SELECT 
        desc_cd, item_cd, abc, line_nbr, type_cd, val1, val2, val3,
        ROW_NUMBER() OVER(PARTITION BY desc_cd, item_cd, abc ORDER BY line_nbr) AS seq
    FROM sample
)

2. 动态生成SQL并执行
   通过LISTAGG自动生成条件聚合语句，适配任意数量的组内行数：

-- 第一步：生成动态SQL语句
WITH max_seq AS (SELECT MAX(seq) AS max_seq FROM sample_with_seq)
SELECT 'SELECT desc_cd, item_cd, abc,' || 
       LISTAGG(
           'MAX(CASE WHEN seq=' || seq || ' THEN line_nbr END) AS line_nbr_' || seq || ',' ||
           'MAX(CASE WHEN seq=' || seq || ' THEN type_cd END) AS type_cd_' || seq || ',' ||
           'MAX(CASE WHEN seq=' || seq || ' THEN val1 END) AS val1_' || seq || ',' ||
           'MAX(CASE WHEN seq=' || seq || ' THEN val2 END) AS val2_' || seq || ',' ||
           'MAX(CASE WHEN seq=' || seq || ' THEN val3 END) AS val3_' || seq
       , ',') WITHIN GROUP(ORDER BY seq) ||
       ' FROM sample_with_seq GROUP BY desc_cd, item_cd, abc;'
FROM (SELECT DISTINCT seq FROM sample_with_seq) t, max_seq;

-- 第二步：执行上述SQL输出的动态语句，得到最终宽表

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