SAS数据集添加行分组序号与序列值：排序错误及解决方案问询

问题说明 #

需要为给定数据集生成line_group（行分组序号）和drctn_seq（方向序列值），但原有SAS代码因数据集未按BY变量排序报错，且同一item_sufx组内存在不同Id和com值，原有方案无法适配。

现有数据集 #

报错代码与信息 #

尝试的SAS代码 #

data table_A_mod;
set table_A;
by Id com typ cust bu tar item item_sufx part dtn_cd;
line_decreased = line_nbr < lag(line_nbr);
if first.part 
or line_decreased
then line_group+1;
if first.dtn_cd then drctn_seq=1; else drctn_seq+1;
run;

错误信息 #

ERROR: BY variables are not properly sorted on data set WORK.Table_A.

SAS解决方案 #

报错核心原因是数据集未按BY语句指定的变量排序，且原逻辑未正确处理分组切换时的序列重置。以下是修正后的步骤：

1. 先按分组键排序
   确保数据集按分组维度和line顺序排列：

proc sort data=table_A out=table_A_sorted;
    by Id com typ cust bu tar item item_sufx part line;
run;

2. 生成line_group与drctn_seq
   使用retain保存前一行的line值，避免lag函数在分组切换时的异常，同时正确重置序列：

data table_A_mod;
    set table_A_sorted;
    by Id com typ cust bu tar item item_sufx part dtn_cd;
    retain line_group drctn_seq prev_line;
    
    if first.part then do;
        line_group = 1;
        drctn_seq = 1;
        prev_line = line;
    end;
    else do;
        /* 当line比前一行小时，启动新的分组 */
        if line < prev_line then line_group + 1;
        /* 同一分组内，dtn_cd变化时重置序列值 */
        if first.dtn_cd then drctn_seq = 1;
        else drctn_seq + 1;
        prev_line = line;
    end;
    
    drop prev_line; /* 临时变量无需保留 */
run;

DB2 PROC SQL解决方案 #

利用窗口函数实现分组与序列计算，无需提前排序：

WITH sorted_data AS (
    SELECT 
        *,
        /* 标记分组起始点：part变更 或 line值递减 */
        CASE 
            WHEN LAG(part) OVER(PARTITION BY Id, com, typ, cust, bu, tar, item, item_sufx ORDER BY part, line) IS NULL 
                 OR LAG(part) OVER(PARTITION BY Id, com, typ, cust, bu, tar, item, item_sufx ORDER BY part, line) != part
                 OR line < LAG(line) OVER(PARTITION BY Id, com, typ, cust, bu, tar, item, item_sufx, part ORDER BY line)
            THEN 1 
            ELSE 0 
        END AS group_flag
    FROM table_A
),
grouped_data AS (
    SELECT 
        *,
        /* 累计标记得到line_group序号 */
        SUM(group_flag) OVER(PARTITION BY Id, com, typ, cust, bu, tar, item, item_sufx ORDER BY part, line ROWS UNBOUNDED PRECEDING) AS line_group
    FROM sorted_data
)
SELECT 
    *,
    /* 每个line_group与dtn_cd组合内的行号 */
    ROW_NUMBER() OVER(PARTITION BY line_group, dtn_cd ORDER BY part, line) AS drctn_seq
FROM grouped_data
ORDER BY Id, com, typ, cust, bu, tar, item, item_sufx, part, line;

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