在SAS中统计多变量（Year1-Year5）的众值及各值出现次数

针对需求：统计每个ID下Year1至Year5变量中各值的出现次数，并提取众值（存在并列众值时标记为tie，空值忽略），以下是实现代码及说明：

SAS代码实现 #

/* 创建示例数据集 */
data have;
    input ID Year1 Year2 Year3 Year4 Year5;
    datalines;
1 19 19 19 19 19
2 5 5 9 . .
3 12 45 11 12 12
4 14 15 10 14 .
5 20 20 19 19 28
;
run;

/* 步骤1：将每个ID的Year变量转成纵向，统计各值出现次数 */
data count_vals;
    set have;
    array years[*] Year1-Year5;
    do i = 1 to dim(years);
        val = years[i];
        if not missing(val) then do;
            output;
        end;
    end;
    keep ID val;
run;

proc sort data=count_vals;
    by ID val;
run;

data count_vals_final;
    set count_vals;
    by ID val;
    if first.val then count = 0;
    count + 1;
    if last.val then output;
run;

/* 步骤2：合并计数结果回原数据集，生成Count_Yearn变量 */
data temp;
    set have;
    array years[*] Year1-Year5;
    array counts[*] Count_Year1-Count_Year5;
    do i = 1 to dim(years);
        val = years[i];
        if not missing(val) then do;
            set count_vals_final key=ID val/unique;
            counts[i] = count;
        end;
        else counts[i] = .;
    end;
run;

/* 步骤3：提取众值 */
data want;
    set temp;
    array years[*] Year1-Year5;
    /* 用哈希表快速查询值的计数 */
    if _n_ = 1 then declare hash h(dataset:'count_vals_final');
    h.definekey('ID', 'val');
    h.definedata('count');
    h.definedone();

    /* 找出当前ID的最大计数 */
    max_count = 0;
    do i = 1 to dim(years);
        val = years[i];
        if not missing(val) then do;
            rc = h.find(key:ID, key:val);
            if count > max_count then max_count = count;
        end;
    end;

    /* 统计达到最大计数的值的数量 */
    tie_flag = 0;
    target_val = .;
    do i = 1 to dim(years);
        val = years[i];
        if not missing(val) then do;
            rc = h.find(key:ID, key:val);
            if count = max_count then do;
                tie_flag + 1;
                target_val = val;
            end;
        end;
    end;

    /* 确定众值显示内容 */
    if tie_flag > 1 then Most_common_Value = 'tie';
    else if tie_flag = 1 then Most_common_Value = put(target_val, 8.);
    else Most_common_Value = '';

    drop i val rc max_count tie_flag target_val;
run;

/* 输出结果 */
proc print data=want noobs;
run;

代码说明 #

1. 数据准备：创建与输入匹配的示例数据集have。
2. 纵向转置与计数：通过数组遍历每个ID的Year变量，提取非空值转为纵向记录，再按ID和值分组统计出现次数，得到每个ID下各值的总出现次数。
3. 生成Count_Yearn变量：再次遍历原数据集的Year变量，通过哈希表快速匹配对应值的计数，填充到Count_Year1至Count_Year5变量中，空值对应位置设为缺失。
4. 众值判断：利用哈希表获取当前ID的所有值计数，找出最大计数值，统计有多少个值达到该次数，若超过1个则标记为tie，否则显示对应值。

运行结果 #

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