将按事件行的Medicaid数据转换为按患者行并保留诊断日期

核心思路 #

针对9600万索赔记录、25万患者的超大数据量，采用先筛选去重→日期编号→转置→全量合并的流程，确保高效处理同时保留所有患者（含无诊断记录者）。

分步实现代码 #

1. 生成全量患者基础表 #

先提取所有唯一患者ID，保证无诊断记录的患者不被遗漏：

/* 从原始索赔数据提取唯一患者ID，作为基础表 */
proc sort data=raw_claims(keep=patient_id) out=all_patients nodupkey threads;
    by patient_id;
run;

注：threads选项需SAS/THREADS许可，可大幅提升排序速度

2. 预处理索赔数据：筛选有效诊断并去重 #

提取每种精神障碍的诊断日期，去除同一患者同一障碍的重复日期：

/* 自动提取所有精神障碍哑变量及其名称（适配25种障碍） */
proc sql noprint;
    /* 获取所有xxx_disorder格式的哑变量名 */
    select name into :disorder_vars separated by ' '
    from dictionary.columns
    where libname='WORK' and memname='RAW_CLAIMS' and name like '%_disorder';
    /* 提取障碍名称前缀（如mood_disorder→mood） */
    select quote(scan(name, 1, '_')) into :disorder_names separated by ' '
    from dictionary.columns
    where libname='WORK' and memname='RAW_CLAIMS' and name like '%_disorder';
quit;

/* 筛选出所有有诊断的记录，按障碍类型拆分并去重 */
data claims_clean;
    set raw_claims;
    array disorders[*] &disorder_vars.;
    array disorder_names[*] $32 &disorder_names.;
    
    /* 用HASH表去重，避免排序，适合超大数据量 */
    if _n_ = 1 then do;
        declare hash h(ordered:'no');
        h.definekey('patient_id', 'disorder_type', 'claim_date');
        h.definedone();
    end;
    
    do i=1 to dim(disorders);
        if disorders[i] = 1 then do;
            disorder_type = disorder_names[i];
            /* 仅保留未重复的记录 */
            if h.add() = 0 then output;
        end;
    end;
    keep patient_id claim_date disorder_type;
run;

3. 给诊断日期编号 #

为每个患者的每种障碍诊断日期按时间顺序生成序号（如mood_1、mood_2）：

data claims_numbered;
    set claims_clean;
    by patient_id disorder_type;
    if first.disorder_type then date_seq = 0;
    date_seq + 1;
    /* 生成转置后的列名 */
    col_name = cats(disorder_type, '_', date_seq);
run;

4. 转置为患者级宽格式 #

将长格式的诊断记录转置为每行对应一位患者的宽格式：

proc transpose data=claims_numbered out=claims_wide prefix=;
    by patient_id;
    id col_name;
    var claim_date;
run;

5. 合并全量患者数据 #

将转置后的诊断数据与全量患者表合并，确保无诊断记录的患者保留：

data final_patient_data;
    merge all_patients(in=a) claims_wide(in=b);
    by patient_id;
run;

性能优化建议 #

• SPDE引擎：如果服务器支持，将中间数据集存储在SPDE库中，减少磁盘IO压力：

  libname spde spde '/path/to/spde_storage';
  /* 将后续中间表（如claims_clean、claims_numbered）指定到spde库 */
  

• 分块处理：若内存不足，可按患者ID范围分块处理，最后合并结果。
• 缺失值处理：转置后无诊断的患者对应列会显示为SAS缺失值（.），后续回归分析可根据需求用0或其他方式填充。

内容的提问来源于stack exchange，提问作者Angela A.