如何为SAS数据集中各ID生成全日期并补全缺失AMT值？

我有一个包含2023年5-6月日期与AMT字段的SAS数据集，部分日期缺失。需要添加缺失日期，并用前向填充的方式补全缺失的AMT值，最终得到如下Want数据集：

原数据集（Have） #

ID    DATE         AMT
    1     6/1/2023     $10
    1     6/3/2023     $20
     .
     .
    1     6/12/2023    $15
    1     6/14/2023    $20
    . 
    .
    1     6/30/2023     $20

目标数据集（Want） #

ID    DATE         AMT
    1     6/1/2023     $10
    1     6/2/2023     $10
    1     6/3/2023     $20
     .
     .
    1     6/12/2023    $15
    1     6/13/2023    $15
    1     6/14/2023    $20
    . 
    .
    1     6/30/2023     $20

你尝试的代码 #

Proc sql;
  create table uniqueid as select 
  distinct Id from Have;
quit;

data tempdates;
   strdate='01MAY2023'd;
   enddate='30JUN2023'd;
 do date=str to enddate;
   output;
 end;
run;

 data merged;
  merge Have tempdates;
 run;

问题分析 #

你的代码存在两个核心问题：

1. tempdates仅生成了日期序列，未关联ID，合并时无法按ID匹配，会导致数据交叉混乱
2. 未指定by语句的merge是按观测位置合并，结果不符合预期；同时缺少AMT缺失值的填充逻辑

实现逻辑与完整代码 #

实现步骤 #

1. 提取数据集中的所有唯一ID，确保每个ID都能生成对应的完整日期序列
2. 为每个ID生成2023年5月1日至6月30日的所有日期，构建全量ID+日期数据集
3. 通过左连接将原数据集与全量日期数据集合并，保留所有日期，缺失的AMT会显示为空
4. 按ID和日期排序，使用RETAIN语句实现AMT的前向填充（用最近的非缺失值补全后续缺失值）

完整代码 #

/* 1. 获取数据集中的唯一ID */
proc sql noprint;
    create table unique_ids as
    select distinct ID from Have;
quit;

/* 2. 为每个ID生成完整的日期序列（2023-05-01至2023-06-30） */
data full_dates;
    set unique_ids;
    start_date = '01MAY2023'd;
    end_date = '30JUN2023'd;
    do DATE = start_date to end_date;
        output;
    end;
    format DATE mmddyy10.; /* 日期格式与原数据对齐 */
run;

/* 3. 左连接合并全量日期与原数据，保留所有日期 */
proc sql;
    create table merged as
    select f.ID, f.DATE, h.AMT
    from full_dates f
    left join Have h
    on f.ID = h.ID and f.DATE = h.DATE
    order by f.ID, f.DATE; /* 按ID和日期排序，为后续填充做准备 */
quit;

/* 4. 前向填充AMT的缺失值 */
data Want;
    set merged;
    by ID; /* 按ID分组处理，避免跨ID填充 */
    retain last_valid_amt; /* 保留上一个有效的AMT值 */
    
    if first.ID then do;
        last_valid_amt = AMT; /* 每个ID的第一条记录初始化 */
    end;
    else if missing(AMT) then do;
        AMT = last_valid_amt; /* 缺失时用最近的有效值填充 */
    end;
    else do;
        last_valid_amt = AMT; /* 更新有效AMT值 */
    end;
    
    format AMT dollar8.; /* 金额格式与原数据对齐 */
run;

代码说明 #

• RETAIN语句用于在数据步循环中保留变量值，避免每次循环重置，是实现前向填充的高效方式
• 按ID分组处理，确保填充不会跨ID进行，符合数据逻辑
• 左连接保证了所有日期都被保留，原数据中存在的日期会自动匹配对应的AMT值

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