如何在SAS中转置含缺失日期记录的滚动8天报表数据？

需求是创建滚动8天报表（统计范围为TODAY()-8至TODAY()-1），按FacilityNM、MetricGrp、MetricNM组合分组时，部分日期无对应数据，导致使用PROC TRANSPOSE或ARRAY转置时数值无法匹配到正确的Day变量，出现错位。

样本记录 #

FacilityNM    MetricGrp    MetricNM      Date      Value
PB            Surgery      Cases - SDS   6/29/23      50
PB            Surgery      Cases - SDS   6/30/23      41
PB            Surgery      Cases - SDS   7/3/23        5 
PB            Surgery      Cases - SDS   7/5/23       30
PB            Surgery      Cases - SDS   7/6/23       58
PB            Volume       ED Visits     6/29/23     180
PB            Volume       ED Visits     6/30/23     210
PB            Volume       ED Visits     7/1/23      160
PB            Volume       ED Visits     7/2/23      170
PB            Volume       ED Visits     7/3/23      185
PB            Volume       ED Visits     7/4/23      140
PB            Volume       ED Visits     7/5/23      180
PB            Volume       ED Visits     7/6/23      180

期望输出 #

FacilityNM MetricGrp  MetricNM    Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8 
PB         Surgery    Cases - SDS   50   41              5        30   58 
PB         Volume     ED Visits    180  210  160  170  185  140  180  180

当前实际输出 #

FacilityNM    MetricGrp    MetricNM      Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8
PB            Surgery      Cases - SDS     50   41    5   30   58
PB            Volume       ED Visits      180  210  160  170  185  140  180  180

解决方案 #

问题核心是原始数据缺少部分日期的记录，转置时无法对应到正确的Day列。需先补全所有缺失日期的记录，再执行转置操作。

步骤1：生成完整的日期与分组框架 #

先生成统计范围内的所有8个日期，再与所有分组组合做笛卡尔积，补全缺失的日期记录：

/* 定义统计日期范围 */
%let start_date = %sysfunc(intnx(day, %sysfunc(today()), -8));
%let end_date = %sysfunc(intnx(day, %sysfunc(today()), -1));

/* 生成完整的日期序列 */
data dates;
  do date = &start_date. to &end_date.;
    output;
  end;
  format date mmddyy10.;
run;

/* 获取所有唯一分组组合 */
data groups;
  set original_data;
  by FacilityNM MetricGrp MetricNM;
  if first.MetricNM;
  keep FacilityNM MetricGrp MetricNM;
run;

/* 交叉连接分组与日期，生成完整记录框架 */
data full_data;
  merge groups dates;
run;

/* 合并原始数据，补全缺失值 */
data full_data_with_values;
  merge full_data original_data;
  by FacilityNM MetricGrp MetricNM date;
  if missing(Value) then Value = .;
run;

步骤2：为日期分配Day编号 #

根据日期在统计范围内的顺序，生成Day1到Day8的标识：

data full_data_with_day;
  set full_data_with_values;
  by FacilityNM MetricGrp MetricNM;
  if first.MetricNM then day_num = 0;
  day_num + 1;
  day_col = cat('Day', day_num);
run;

步骤3：PROC TRANSPOSE转置生成报表 #

此时所有分组都包含8天的记录，转置后数值会自动匹配到正确的Day列：

proc transpose data=full_data_with_day out=final_report(drop=_name_);
  by FacilityNM MetricGrp MetricNM;
  id day_col;
  var Value;
run;

/* 调整输出格式 */
proc print data=final_report noobs;
  format Day1-Day8 4.;
run;

替代方案：用ARRAY直接构建宽表 #

若不想使用PROC TRANSPOSE，可在数据步中用ARRAY直接生成目标报表：

data final_report_array;
  set full_data_with_values;
  by FacilityNM MetricGrp MetricNM;
  array days[8] Day1-Day8;
  retain Day1-Day8;
  if first.MetricNM then call missing(of days[*]);
  days[day_num] = Value;
  if last.MetricNM then output;
  drop date Value day_num;
run;

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