在SAS Enterprise Guide中如何基于报价变更记录生成COL1统计列？

数据集信息 #

原始数据集未排序，字段定义：

• ID：数值型，客户唯一标识
• DT：日期型，报价变更日期
• OFFER_1：字符型，变更前报价
• OFFER_2：字符型，变更后报价

样本数据：

ID   | DT        | OFFER_1  | OFFER_2
-----|-----------|----------|----------
123  | 01MAY2020 | PR       | PR
123  | 05MAY2020 | PR       | P
123  | 10MAY2020 | P        | P
123  | 11MAY2020 | P        | P
123  | 20MAY2020 | P        | PR
123  | 21MAY2020 | PR       | M
123  | 25MAY2020 | M        | M
777  | 30MAY2020 | PR       | M
223  | 02JAN2020 | PR       | PR
223  | 15MAR2020 | PR       | PR
402  | 20MAR2020 | M        | M
33   | 11AUG2020 | M        | PR
11   | 20JAN2020 | PR       | M
11   | 05FEB2020 | M        | M

需求规则 #

需生成新列COL1，规则如下：

• 若客户存在从PR报价变更为P/M报价的记录，计算其在P/M状态的最长持续天数：
  • 若后续回到PR，取进入P/M到回到PR的天数（如ID=123的第一次变更：05MAY2020到20MAY2020，共15天）
  • 若未回到PR，取进入P/M到该客户最后一条记录的天数（如ID=11：20JAN2020到05FEB2020，共16天）
• 无PR转P/M变更的客户，COL1赋值为0

期望输出 #

ID   | DT        | OFFER_1  | OFFER_2  | COL1
-----|-----------|----------|----------|---------
123  | 01MAY2020 | PR       | PR       | 15
123  | 05MAY2020 | PR       | P        | 15
123  | 10MAY2020 | P        | P        | 15
123  | 11MAY2020 | P        | P        | 15
123  | 20MAY2020 | P        | PR       | 15
123  | 21MAY2020 | PR       | M        | 15
123  | 25MAY2020 | M        | M        | 15
777  | 30MAY2020 | PR       | M        | 1
223  | 02JAN2020 | PR       | PR       | 0
223  | 15MAR2020 | PR       | PR       | 0
402  | 20MAR2020 | M        | M        | 0
33   | 11AUG2020 | M        | PR       | 0
11   | 20JAN2020 | PR       | M        | 16
11   | 05FEB2020 | M        | M        | 16

实现代码 #

1. 排序数据集 #

首先按客户ID和日期升序排序，确保时间序列正确：

proc sort data=原始数据集名 out=sorted_data;
    by ID DT;
run;

2. 追踪状态区间并计算最长天数 #

使用数据步分组处理每个客户的记录，标记P/M状态的起止时间，计算各区间时长并保留最大值：

data intermediate;
    set sorted_data;
    by ID;
    /* 保留状态起点、终点及当前最大天数 */
    retain start_pm end_pm max_days 0;
    
    /* 组内第一条记录初始化变量 */
    if first.ID then do;
        start_pm = .;
        end_pm = .;
        max_days = 0;
    end;
    
    /* 记录从PR进入P/M的起点日期 */
    if OFFER_1 = 'PR' and OFFER_2 in ('P','M') then do;
        start_pm = DT;
    end;
    
    /* 记录从P/M回到PR的终点日期，计算区间时长并更新最大值 */
    if OFFER_1 in ('P','M') and OFFER_2 = 'PR' then do;
        end_pm = DT;
        if start_pm ne . then do;
            current_days = end_pm - start_pm;
            if current_days > max_days then max_days = current_days;
            /* 重置起止点，准备下一个状态区间 */
            start_pm = .;
            end_pm = .;
        end;
    end;
    
    /* 组内最后一条记录时，检查是否有未结束的P/M区间 */
    if last.ID then do;
        if start_pm ne . then do;
            /* 未回到PR时，计算到最后一条记录的天数（包含起始日） */
            current_days = DT - start_pm + 1;
            if current_days > max_days then max_days = current_days;
        end;
    end;
    output;
run;

3. 统一赋值最长天数到所有记录 #

将每个客户的max_days赋值给该客户的所有记录，生成最终数据集：

data final_data;
    set intermediate;
    by ID;
    retain final_col1;
    
    /* 组内第一条记录时，获取该客户的最长天数 */
    if first.ID then final_col1 = max_days;
    /* 所有行赋值COL1 */
    COL1 = final_col1;
    
    /* 清理中间变量 */
    drop start_pm end_pm max_days current_days final_col1;
run;

代码说明 #

• 排序是核心前提，确保按时间顺序处理每个客户的报价变更
• 通过retain语句持续追踪状态区间的起止时间，避免变量被重置
• 组内最后一条记录的处理逻辑覆盖了"未回到PR"的场景
• 最终通过retain将最长天数统一赋值给客户的所有记录，符合需求中"所有行COL1取最大值"的要求

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