如何用双重DOW循环填充缺失AVAL：基于末次访视（LOV）数据

我需要为SAS数据集实现Composite Strategy 2填充逻辑：针对每个受试者（SUBJID）的参数（PARAMCD），当DTYPE=LOV时，用Early Termination访视的AVAL值，填充该访视之前所有缺失的AVAL值，且不覆盖已有非缺失的AVAL。要求用双重DOW循环实现。

原始输入数据集 #

data have;
input  PARAMCD $ STRATEGY :$30. DTYPE $ SUBJID $ VISIT :$30. AVAL :8.; 
infile datalines dlm = '|';
datalines;
TOTEDPS1|Composite strategy 2|LOV|1001|Week 2|61.2
TOTEDPS1|Composite strategy 2|LOV|1001|Week 4|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 8|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 12|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 16|
TOTEDPS1|Composite strategy 2|LOV|1001|Early Termination|43.5
TOTEDPS1|Composite strategy 2|LOV|1002|Week 2|16
TOTEDPS1|Composite strategy 2|LOV|1002|Week 4|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 8|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 12|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 16|
TOTEDPS1|Composite strategy 2|LOV|1002|Early Termination|6.6
;
run;

期望输出数据集 #

data want;
input  PARAMCD $ STRATEGY :$30. DTYPE $ SUBJID $ VISIT :$30. AVAL :8.; 
infile datalines dlm = '|';
datalines;
TOTEDPS1|Composite strategy 2|LOV|1001|Week 2|61.2
TOTEDPS1|Composite strategy 2|LOV|1001|Week 4|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 8|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 12|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 16|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Early Termination|43.5
TOTEDPS1|Composite strategy 2|LOV|1002|Week 2|16
TOTEDPS1|Composite strategy 2|LOV|1002|Week 4|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 8|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 12|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 16|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Early Termination|6.6
;
run;

data want;
  /* 第一层DOW循环：按分组读取所有观测，提取Early Termination的AVAL值 */
  do _n_ = 1 by 1 until(last.SUBJID);
    set have;
    by PARAMCD DTYPE SUBJID;
    if VISIT = 'Early Termination' then lov_aval = AVAL;
  end;
  /* 第二层DOW循环：重新读取分组内观测，填充缺失值 */
  do _n_ = 1 by 1 until(last.SUBJID);
    set have;
    by PARAMCD DTYPE SUBJID;
    /* 仅填充缺失的AVAL，不覆盖已有值 */
    if missing(AVAL) then AVAL = lov_aval;
    output;
  end;
  drop lov_aval;
run;

代码逻辑说明 #

• 第一层循环：按PARAMCD、DTYPE、SUBJID分组遍历观测，捕获该组中Early Termination访视的AVAL值，存入临时变量lov_aval。
• 第二层循环：再次遍历同一分组的所有观测，对缺失的AVAL用lov_aval填充，已有非缺失值保持原样，最后输出所有观测。
• 临时变量lov_aval使用后被删除，避免影响输出数据集结构。

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