如何基于ID分组计算两个特定周期间的时长（含缺失日期）

问题背景 #

现有如下SAS数据集，需要为每个ID计算Cycle 3对应日期与Cycle 1对应日期的差值，并将该差值填充到该ID的所有行中；若某个ID的Cycle 3日期缺失，则对应的差值也保持缺失。

原始数据集代码 #

data df;
  infile datalines delimiter=','; 
  attrib
  id length = $8
  cycle length = $8
  date length = 8 format = mmddyy10. informat = mmddyy10.
  ;
input ID cycle date;
datalines;
1, 1, 10/01/2021
1, 2, 10/02/2021
1, 3, 11/03/2021
2, 1, 10/01/2021
2, 1, 10/01/2021
2, 2, 11/04/2021
2, 2, 11/04/2021
2, 3, 10/07/2021
3, 1, 10/02/2021
3, 2, 10/03/2021
3, 3,           
;
run;

核心问题 #

部分ID存在Cycle 3日期缺失的情况，无法直接使用max(date)-min(date)的方式计算（会错误包含Cycle 2的日期），需要精准匹配Cycle 1和Cycle 3的日期进行计算。

解决方案1：PROC SQL 实现（推荐） #

通过子查询按ID提取Cycle 1和Cycle 3的日期，再关联原数据集计算差值，逻辑清晰且高效。

proc sql;
    create table df_duration as
    select 
        a.*,
        case 
            when b.cycle1_date is not missing and b.cycle3_date is not missing 
            then b.cycle3_date - b.cycle1_date 
            else . 
        end as duration
    from df as a
    left join (
        /* 按ID分组，提取每个ID的Cycle1和Cycle3日期 */
        select 
            id,
            max(case when cycle='1' then date else . end) as cycle1_date,
            max(case when cycle='3' then date else . end) as cycle3_date
        from df
        group by id
    ) as b
    on a.id = b.id
    order by a.id, a.cycle;
quit;

代码说明

1. 子查询中：
   • 用case when精准筛选Cycle 1和Cycle 3的日期
   • 使用max()聚合是为了兼容同一ID下Cycle 1/3存在多行重复日期的情况（如ID2的Cycle1有两行相同日期）
2. 主查询中：
   • 通过left join将原表与子查询结果关联，确保所有原始行都保留
   • 用case判断两个日期均非缺失时计算差值，否则返回缺失值（.）

解决方案2：DATA步 BY组处理 #

利用RETAIN保留变量值，结合BY组遍历每个ID的日期信息，再反向填充结果。

/* 先按ID和Cycle排序，确保BY组处理的顺序正确 */
proc sort data=df;
    by id cycle;
run;

data df_duration;
    set df;
    by id;
    retain cycle1_date cycle3_date;
    
    /* 每个ID的第一行初始化日期变量 */
    if first.id then do;
        cycle1_date = .;
        cycle3_date = .;
    end;
    
    /* 记录当前ID的Cycle1和Cycle3日期 */
    if cycle='1' then cycle1_date = date;
    if cycle='3' then cycle3_date = date;
    
    /* 当遍历到每个ID的最后一行时，反向填充所有行的duration */
    if last.id then do;
        /* 先输出当前行 */
        duration = ifn(cycle1_date ne . and cycle3_date ne ., cycle3_date - cycle1_date, .);
        output;
        /* 遍历当前ID的剩余行，填充duration */
        do _i = _n_-1 to 1 by -1;
            set df point=_i;
            duration = ifn(cycle1_date ne . and cycle3_date ne ., cycle3_date - cycle1_date, .);
            output;
        end;
        stop;
    end;
run;

/* 最后重新排序恢复原始顺序 */
proc sort data=df_duration;
    by id cycle;
quit;

代码说明

1. 先对数据集排序，保证BY组处理时同一ID的行连续
2. 使用RETAIN保留每个ID的Cycle1和Cycle3日期，避免变量在每行重新初始化
3. 当遍历到ID的最后一行时，反向遍历该ID的所有行，统一填充计算好的duration值
4. 最后重新排序恢复原始数据的顺序

验证结果 #

运行上述任意一种方案，均可得到符合预期的结果：

• ID1的Cycle3日期（11/03/2021）与Cycle1日期（10/01/2021）差值为2天，所有行的duration填充为2
• ID2的差值为6天，所有行的duration填充为6
• ID3因Cycle3日期缺失，所有行的duration保持缺失

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