如何在SAS中为同一ID合并重复缺失观测为单个非缺失观测

问题背景 #

现有大型数据集，包含数百个多时间点收集的变量。变量按宽格式规划，但实际以长格式存储：每个ID对应多个观测，每个观测仅包含对应时间点的有效变量值，其余为缺失值，timepoint字段冗余。需将数据集转换为每个ID对应单个观测的标准宽格式，且变量名无统一后缀，无法通过后缀批量引用。

原始数据集 #

data have;
input id timepoint $ var_t1 var_t2 var_t3 note_t1 $ note_t2 $ note_t3 $;
datalines;
1 time_1 1 . . note1 . .
1 time_2 . 2 . . note2 .
1 time_3 . . 3 . . note3
2 time_1 1 . . note1 . .
2 time_2 . 2 . . note2 .
2 time_3 . . 3 . . note3
;
run;

目标数据集 #

data want;
input id var_t1 var_t2 var_t3 note_t1 $ note_t2 $ note_t3 $;
datalines;
1 1 2 3 note1 note2 note3
2 1 2 3 note1 note2 note3
;
run;

之前的尝试与问题 #

• 按时间点拆分后合并：直接合并会丢失数据，删除全缺失变量后合并难以同时兼容数值型和字符型变量。
• 使用retain语句时出错：retain无法在DO循环内动态指定变量，出现语法错误。错误代码及报错信息如下：

data timepoint_1_want;
set timepoint_1_have;
array Nums[*] _numeric_;
array Chars[*] _character_;
by id;
do i = 1 to dim(Nums);
    IF not missing(Nums[i]) THEN do;
        retain Nums[i];
    end;
do i = 1 to dim(Chars);
    IF not missing(Chars[i]) THEN do;
        retain Chars[i];
    end;
drop i;
IF last.id THEN output;
run;

报错信息：

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, (, -, :, ;, _ALL_,
              _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

正确解决方法 #

方法1：RETAIN + 数组（推荐，适合大型数据集） #

retain需在数据步开头声明保留变量，再通过数组循环用非缺失值覆盖保留值：

data want;
    set have;
    by id;
    /* 定义数值型和字符型变量数组 */
    array nums[*] _numeric_;
    array chars[*] _character_;
    
    /* 保留所有数值型和字符型变量，初始值为缺失 */
    retain _numeric_ _character_;
    
    /* 用当前观测的非缺失值更新数值型变量的保留值 */
    do i = 1 to dim(nums);
        if not missing(nums[i]) then nums[i] = nums[i];
    end;
    
    /* 用当前观测的非缺失值更新字符型变量的保留值 */
    do i = 1 to dim(chars);
        if not missing(chars[i]) then chars[i] = chars[i];
    end;
    
    /* 仅输出每个ID的最后一条观测 */
    if last.id then output;
    
    /* 删除冗余变量 */
    drop timepoint i;
run;

说明：retain _numeric_ _character_会保留所有数值和字符变量，每次读入新观测时保留值不重置。循环遍历变量时，用当前观测的非缺失值覆盖保留值（对应该时间点的有效数据），最终在每个ID的最后一条观测输出完整宽格式数据。

方法2：PROC SQL分组聚合 #

利用SQL的MAX函数忽略缺失值，对每个ID分组取变量的非缺失值：

proc sql;
    create table want as
        select id,
               max(var_t1) as var_t1,
               max(var_t2) as var_t2,
               max(var_t3) as var_t3,
               max(note_t1) as note_t1,
               max(note_t2) as note_t2,
               max(note_t3) as note_t3
        from have
        group by id;
quit;

若变量数量过多，可通过字典表自动生成聚合语句：

proc sql noprint;
    select name into :vars separated by ', max(' || name || ') as ' || name
    from dictionary.columns
    where libname = 'WORK' and memname = 'HAVE' and name not in ('ID', 'TIMEPOINT');
quit;

proc sql;
    create table want as
        select id, &vars.
        from have
        group by id;
quit;

说明：MAX函数会自动忽略缺失值，返回同一ID下变量的唯一非缺失值；字典表查询可自动生成所有目标变量的聚合代码，无需手动输入数百个变量。

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