如何在SAS Enterprise Guide/PROC SQL中按规则生成TOP_COUNT与TOP_SUM新列？

原始示例数据 #

首先定义示例输入数据集：

data have;
input ID COUNT_COL_A COUNT_COL_B SUM_COL_A SUM_COL_B;
datalines;
111 10 10 320 120
222 15 80 500 500
333 1 5 110 350
444 20 5 670 0
;
run;

需求与约束 #

需求 #

• 创建TOP_COUNT列：优先取COUNT_COL_A和COUNT_COL_B中数值更高的列名；若两列数值相等，则取对应SUM_COL_A/SUM_COL_B中数值更高的那个COUNT列名。
• 创建TOP_SUM列：优先取SUM_COL_A和SUM_COL_B中数值更高的列名；若两列数值相等，则取对应COUNT_COL_A/COUNT_COL_B中数值更高的那个SUM列名。

约束 #

• COUNT前缀列不全为0，SUM前缀列也不全为0
• 表格无空值

实现方法 #

方法一：DATA步实现（SAS Enterprise Guide推荐） #

通过条件分支直接赋值，逻辑直观，适合在SAS EG中可视化操作：

data want;
set have;
/* 计算TOP_COUNT */
if COUNT_COL_A > COUNT_COL_B then TOP_COUNT = 'COUNT_COL_A';
else if COUNT_COL_B > COUNT_COL_A then TOP_COUNT = 'COUNT_COL_B';
else do; /* COUNT值相等时，用SUM列做二次判断 */
    if SUM_COL_A > SUM_COL_B then TOP_COUNT = 'COUNT_COL_A';
    else TOP_COUNT = 'COUNT_COL_B';
end;

/* 计算TOP_SUM */
if SUM_COL_A > SUM_COL_B then TOP_SUM = 'SUM_COL_A';
else if SUM_COL_B > SUM_COL_A then TOP_SUM = 'SUM_COL_B';
else do; /* SUM值相等时，用COUNT列做二次判断 */
    if COUNT_COL_A > COUNT_COL_B then TOP_SUM = 'SUM_COL_A';
    else TOP_SUM = 'SUM_COL_B';
end;
run;

方法二：PROC SQL实现 #

用CASE WHEN嵌套逻辑完成判断，适合习惯SQL语法的场景：

proc sql;
create table want as
select 
    ID,
    COUNT_COL_A,
    COUNT_COL_B,
    SUM_COL_A,
    SUM_COL_B,
    /* 生成TOP_COUNT */
    case
        when COUNT_COL_A > COUNT_COL_B then 'COUNT_COL_A'
        when COUNT_COL_B > COUNT_COL_A then 'COUNT_COL_B'
        else case
                when SUM_COL_A > SUM_COL_B then 'COUNT_COL_A'
                else 'COUNT_COL_B'
             end
    end as TOP_COUNT,
    /* 生成TOP_SUM */
    case
        when SUM_COL_A > SUM_COL_B then 'SUM_COL_A'
        when SUM_COL_B > SUM_COL_A then 'SUM_COL_B'
        else case
                when COUNT_COL_A > COUNT_COL_B then 'SUM_COL_A'
                else 'SUM_COL_B'
             end
    end as TOP_SUM
from have;
quit;

输出结果 #

运行上述任意代码后，将得到符合要求的数据集：

ID   | COUNT_COL_A | COUNT_COL_B | SUM_COL_A | SUM_COL_B  | TOP_COUNT   | TOP_SUM
-----|-------------|-------------|-----------|------------|-------------|---------
111  | 10          | 10          | 320       | 120        | COUNT_COL_A | SUM_COL_A 
222  | 15          | 80          | 500       | 500        | COUNT_COL_B | SUM_COL_B  
333  | 1           | 5           | 110       | 350        | COUNT_COL_B | SUM_COL_B  
444  | 20          | 5           | 670       | 0          | COUNT_COL_A | SUM_COL_A 

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