基于KEY变量在SAS中创建带MASTER标识的新观测需求

原始数据 #

期望输出结果 #

实现代码 #

方法1：PROC SQL + DATA步组合 #

/* 1. 统计每个KEY下各字段的唯一值数量与对应取值 */
proc sql;
    create table key_stats as
    select 
        KEY,
        count(distinct AGE) as age_distinct,
        count(distinct LOCATION) as loc_distinct,
        count(distinct TYPE) as type_distinct,
        max(AGE) as age_val,
        max(LOCATION) as loc_val,
        max(TYPE) as type_val
    from original_data
    group by KEY;
quit;

/* 2. 合并数据并生成最终观测 */
data final_data;
    set original_data key_stats(in=stats);
    by KEY;
    
    /* 输出原始观测，MASTER字段留空 */
    if not stats then do;
        MASTER = .;
        output;
    end;
    /* 输出MASTER观测 */
    else do;
        MASTER = 1;
        /* 字段值不唯一则置空，否则保留唯一值 */
        if age_distinct > 1 then call missing(AGE);
        else AGE = age_val;
        
        if loc_distinct > 1 then call missing(LOCATION);
        else LOCATION = loc_val;
        
        if type_distinct > 1 then call missing(TYPE);
        else TYPE = type_val;
        
        output;
    end;
    drop age_distinct loc_distinct type_distinct age_val loc_val type_val;
run;

方法2：纯DATA步BY组处理 #

/* 先按KEY排序，确保BY组逻辑生效 */
proc sort data=original_data;
    by KEY;
run;

data final_data;
    set original_data;
    by KEY;
    /* 保留组内初始值与差异计数 */
    retain age_count loc_count type_count age_first loc_first type_first;
    
    /* 输出原始观测 */
    MASTER = .;
    output;
    
    /* 组内初始化统计变量 */
    if first.KEY then do;
        age_count = 0;
        loc_count = 0;
        type_count = 0;
        age_first = AGE;
        loc_first = LOCATION;
        type_first = TYPE;
    end;
    
    /* 统计字段值差异情况 */
    if AGE ne age_first then age_count + 1;
    if LOCATION ne loc_first then loc_count + 1;
    if TYPE ne type_first then type_count + 1;
    
    /* 组尾生成MASTER观测 */
    if last.KEY then do;
        MASTER = 1;
        /* 存在差异则置空，否则保留初始值 */
        if age_count > 0 then call missing(AGE);
        else AGE = age_first;
        
        if loc_count > 0 then call missing(LOCATION);
        else LOCATION = loc_first;
        
        if type_count > 0 then call missing(TYPE);
        else TYPE = type_first;
        
        output;
    end;
    drop age_count loc_count type_count age_first loc_first type_first;
run;

代码说明 #

• 两种方法核心逻辑一致：先判断每个KEY组内各字段取值是否完全一致，再生成对应MASTER观测。
• call missing()函数可统一处理数值型与字符型变量的置空需求，数值型变量会被设为缺失值，字符型变量设为空字符串。
• 方法1适合大数据量场景，PROC SQL分组统计效率较高；方法2无需生成中间表，纯DATA步完成所有逻辑。

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