如何在SAS Enterprise Guide中计算事件日期最大差值并生成新列

数据集说明 #

原始数据集字段信息：

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

原始数据集未排序，解决方案中会先按客户ID和变更日期排序。

原始数据示例 #

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的日期）与开始日期的差值
  • 若未回归PR，取该客户最后一条记录的日期与P/M状态开始日期的差值
• 若未发生从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

规则说明 #

• ID=123：两次从PR转P/M，最长区间为05MAY2020至20MAY2020，持续15天，因此所有记录的COL1为15
• ID=777：从PR转M后无后续回归PR记录，取最后一条记录日期（30MAY2020）与变更日期的差值，持续1天
• ID=223/402/33：未发生PR到P/M的变更，COL1为0
• ID=11：从PR转M后无回归PR记录，区间为20JAN2020至05FEB2020，持续16天

解决方案 #

方法1：常规SAS数据步实现 #

先按ID和DT排序，再识别P/M区间、计算时长，最后取每个ID的最大时长并合并回原始数据：

/* 1. 对原始数据按ID和变更日期排序 */
proc sort data=原始数据集 out=sorted_data;
    by ID DT;
run;

/* 2. 识别每个P/M区间，计算持续时长 */
data intervals;
    set sorted_data;
    by ID;
    retain pm_start;
    length interval_days 8;
    interval_days = .;

    if first.ID then pm_start = .;

    /* 标记从PR转为P/M的起始点 */
    if OFFER_1 = 'PR' and OFFER_2 in ('P','M') then pm_start = DT;

    /* 当回归PR时，计算当前区间时长并输出 */
    if OFFER_1 in ('P','M') and OFFER_2 = 'PR' then do;
        if not missing(pm_start) then do;
            interval_days = DT - pm_start;
            output;
            pm_start = .;
        end;
    end;

    /* 处理最后一条记录仍处于P/M状态的情况 */
    if last.ID then do;
        if not missing(pm_start) and OFFER_2 in ('P','M') then do;
            interval_days = DT - pm_start;
            output;
        end;
    end;
    keep ID interval_days;
run;

/* 3. 计算每个ID的最长持续天数，无有效区间则为0 */
proc sql;
    create table max_days as
        select ID, coalesce(max(interval_days), 0) as COL1
        from intervals
        group by ID;
quit;

/* 4. 将最长天数合并回原始排序后的数据 */
proc sql;
    create table 最终输出数据集 as
        select a.*, b.COL1
        from sorted_data as a
        left join max_days as b
        on a.ID = b.ID;
quit;

方法2：PROC SQL结合窗口函数实现 #

利用窗口函数识别区间起始点，计算时长并取最大值：

/* 先排序数据 */
proc sort data=原始数据集 out=sorted_data;
    by ID DT;
run;

/* 计算每个ID的最长P/M持续天数 */
proc sql;
    create table 最终输出数据集 as
        select 
            s.*,
            coalesce(m.max_col1, 0) as COL1
        from sorted_data as s
        left join (
            select 
                ID,
                max(case 
                    when pm_start is not missing then 
                        coalesce(lead(DT) over(partition by ID order by DT), DT) - pm_start
                    else . 
                end) as max_col1
            from (
                select 
                    ID,
                    DT,
                    OFFER_1,
                    OFFER_2,
                    /* 标记PR转P/M的起始日期 */
                    case when OFFER_1='PR' and OFFER_2 in ('P','M') then DT else . end as pm_start
                from sorted_data
            ) as t
            group by ID
        ) as m
        on s.ID = m.ID;
quit;

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