如何在SAS中按(Employee,Timepoint)组推导realvisit字段

目标数据集示例 #

以下是符合预期结果的SAS数据集代码：

data work.employees;
    length employee timepoint visit realvisit $30;
    input employee $ timepoint $ visit $ realvisit $;
    datalines;
Smith 1 Screening Screening
Smith 1 Randomization Randomization
Williams 1 Screening Baseline
Williams 2 Randomization Randomization
Jones 1 Visit1 Visit1
Jones 2 Visit3 Visit3
;
run;

需求规则 #

按(Employee, Timepoint)分组，若组内同时不存在visit='Randomization'和visit='Screening'的记录，则将组内对应记录的realvisit设置为'Baseline'。

当前尝试的代码 #

proc sort data = work.employees;
    by employee timepoint;
run;

data work.employees2;
    set work.employees;
    by employee timepoint;
    
    if visit = 'Randomization' then exists = "Y";
        else exists = "N";
        
    if visit = 'Screening' and exists = "N" then
        realvisit = 'Baseline';
run;

正确实现方案 #

现有代码仅判断单条记录的visit值，未基于整个分组做全局校验，无法覆盖所有场景。正确做法是先在分组内标记是否存在Randomization和Screening，再根据标记设置realvisit：

简化版一步实现 #

可以在单个数据步内完成逻辑，利用by组的first./last.关键字和retain保留分组状态：

proc sort data=work.employees;
    by employee timepoint;
run;

data work.employees_final;
    set work.employees;
    by employee timepoint;
    retain has_random has_screen;
    
    /* 每组初始化标记 */
    if first.timepoint then do;
        has_random = 'N';
        has_screen = 'N';
    end;
    
    /* 更新组内是否存在目标visit的标记 */
    if visit = 'Randomization' then has_random = 'Y';
    if visit = 'Screening' then has_screen = 'Y';
    
    /* 读完组内所有记录后，重新遍历设置realvisit */
    if last.timepoint then do;
        do _n_ = _iorc_ to _n_;
            set work.employees point=_n_;
            if has_random = 'N' and has_screen = 'N' then realvisit = 'Baseline';
            else realvisit = visit;
            output;
        end;
    end;
run;

逻辑说明 #

• 用retain关键字保留分组内的标记值，确保在组内所有记录中持续有效
• 通过first.timepoint在每个分组开始时初始化标记状态
• 遍历分组内所有记录，更新has_random和has_screen的最终状态
• 借助last.timepoint触发分组后处理，重新读取当前组的所有记录，根据标记设置realvisit

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