如何在SAS Enterprise Guide/PROC SQL中生成按值降序排列列名的新列？

原始数据 #

ID   | COL_A | COL_B | COL_C
-----|-------|-------|------
111  | 10    | 20    | 30
222  | 15    | 80    | 10
333  | 11    | 10    | 20
444  | 20    | 5     | 20

需求 #

• 创建TOP_1、TOP_2、TOP_3三个新列，存储每个ID对应的COL_A、COL_B、COL_C列值从高到低的列名
• 若多列值并列最高，按列名的字母顺序取靠前的列名
• TOP_1存最高值列名，TOP_2存第二高，TOP_3存第三高

期望输出 #

ID   | COL_A | COL_B | COL_C  | TOP_1  | TOP_2   | TOP_3
-----|-------|-------|--------|--------|---------|--------- 
111  | 10    | 20    | 30     | COL_C  | COL_B   | COL_A
222  | 15    | 80    | 10     | COL_B  | COL_A   | COL_C
333  | 11    | 10    | 20     | COL_C  | COL_A   | COL_B
444  | 20    | 5     | 20     | COL_A  | COL_C   | COL_B

规则说明 #

• ID=111：COL_C值最高，依次为COL_B、COL_A，对应TOP列取值
• ID=444：COL_A和COL_C值并列最高，按字母序COL_A在前，故TOP_1为COL_A，TOP_2为COL_C，COL_B值最低为TOP_3

实现方法 #

方法1：SAS数据步实现 #

利用数组和排序逻辑逐行处理，同时兼容并列场景的字母序优先级：

data want;
    set have;
    /* 定义数组存储目标列名与对应数值 */
    array cols[3] $ COL_A COL_B COL_C;
    array vals[3] COL_A COL_B COL_C;
    /* 临时数组存储排序后的列名和数值 */
    array sorted_names[3] $ _TEMPORARY_;
    array sorted_vals[3] _TEMPORARY_;

    /* 初始化临时数组 */
    do i = 1 to 3;
        sorted_names[i] = vname(cols[i]);
        sorted_vals[i] = vals[i];
    end;

    /* 按数值降序、列名升序排序 */
    do i = 1 to 2;
        do j = i+1 to 3;
            if sorted_vals[j] > sorted_vals[i] or 
               (sorted_vals[j] = sorted_vals[i] and sorted_names[j] < sorted_names[i]) then do;
                /* 交换数值 */
                temp_val = sorted_vals[i];
                sorted_vals[i] = sorted_vals[j];
                sorted_vals[j] = temp_val;
                /* 交换列名 */
                temp_name = sorted_names[i];
                sorted_names[i] = sorted_names[j];
                sorted_names[j] = temp_name;
            end;
        end;
    end;

    /* 赋值到TOP系列列 */
    TOP_1 = sorted_names[1];
    TOP_2 = sorted_names[2];
    TOP_3 = sorted_names[3];

    drop i j temp_val temp_name;
run;

方法2：PROC SQL结合CASE逻辑实现 #

通过嵌套CASE语句依次判断每一列的排名，直接处理并列时的字母序规则：

proc sql;
    create table want as
        select 
            ID, COL_A, COL_B, COL_C,
            /* 确定TOP_1：取数值最高的列，数值相同时取字母序靠前的 */
            case
                when COL_A >= COL_B and COL_A >= COL_C then 'COL_A'
                when COL_B >= COL_A and COL_B >= COL_C then 'COL_B'
                else 'COL_C'
            end as TOP_1,
            /* 确定TOP_2：排除TOP_1后，取剩余列中数值最高的，数值相同则取字母序靠前的 */
            case
                when calculated TOP_1 = 'COL_A' then 
                    case when COL_B >= COL_C then 'COL_B' else 'COL_C' end
                when calculated TOP_1 = 'COL_B' then 
                    case when COL_A >= COL_C then 'COL_A' else 'COL_C' end
                else 
                    case when COL_A >= COL_B then 'COL_A' else 'COL_B' end
            end as TOP_2,
            /* 确定TOP_3：取剩余的最后一列 */
            case
                when calculated TOP_1 = 'COL_A' and calculated TOP_2 = 'COL_B' then 'COL_C'
                when calculated TOP_1 = 'COL_A' and calculated TOP_2 = 'COL_C' then 'COL_B'
                when calculated TOP_1 = 'COL_B' and calculated TOP_2 = 'COL_A' then 'COL_C'
                when calculated TOP_1 = 'COL_B' and calculated TOP_2 = 'COL_C' then 'COL_A'
                when calculated TOP_1 = 'COL_C' and calculated TOP_2 = 'COL_A' then 'COL_B'
                else 'COL_A'
            end as TOP_3
        from have;
quit;

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