如何在SAS Enterprise Guide或PROC SQL中按ID聚合COL1与VAL1？

原始数据 #

假设原始数据集名为HAVE，结构如下：

ID  | COL1 | VAL1 |  
----|------|------|
111 | A    | 10   | 
111 | A    | 5    | 
111 | B    | 10   | 
222 | B    | 20   |
333 | C    | 25   |
... | ...  | ...  | 

需求说明 #

需要完成两类聚合后转置为宽表：

• 按ID统计COL1各取值的出现次数
• 按ID和COL1分组统计VAL1的求和值

期望结果 #

最终输出宽表结构如下：

ID  | COL1_A | COL1_B | COL1_C  | COL1_A_VAL1_SUM | COL1_B_VAL1_SUM | COL1_C_VAL1_SUM
----|--------|--------|---------|-----------------|-----------------|------------------
111 | 2      | 1      | 0       | 15              | 10              | 0
222 | 0      | 1      | 0       | 0               | 20              | 0
333 | 0      | 0      | 1       | 0               | 0               | 25

实现方法 #

方法1：使用PROC SQL直接聚合 #

通过条件聚合的方式，直接在GROUP BY查询中生成所需的统计列，代码如下：

PROC SQL;
CREATE TABLE WANT AS
SELECT 
    ID,
    /* 统计COL1各取值的出现次数 */
    SUM(COL1 = 'A') AS COL1_A,
    SUM(COL1 = 'B') AS COL1_B,
    SUM(COL1 = 'C') AS COL1_C,
    /* 统计对应COL1分组下VAL1的求和值 */
    SUM(CASE WHEN COL1 = 'A' THEN VAL1 ELSE 0 END) AS COL1_A_VAL1_SUM,
    SUM(CASE WHEN COL1 = 'B' THEN VAL1 ELSE 0 END) AS COL1_B_VAL1_SUM,
    SUM(CASE WHEN COL1 = 'C' THEN VAL1 ELSE 0 END) AS COL1_C_VAL1_SUM
FROM HAVE
GROUP BY ID
ORDER BY ID;
QUIT;

• 逻辑说明：
  1. GROUP BY ID 按ID分组处理每个ID的所有行
  2. SUM(COL1='A') 利用SAS中布尔值等价于1/0的特性，直接统计COL1为'A'的行数
  3. CASE WHEN 语句筛选对应COL1的VAL1值，求和后得到分组总和
  4. ORDER BY ID 让结果按ID升序排列（可选）

方法2：在SAS Enterprise Guide中可视化操作 #

如果偏好图形化操作，可通过SAS EG的查询构建器完成：

1. 导入或打开原始数据集HAVE
2. 点击菜单栏查询→查询构建器，选择HAVE表添加到查询中
3. 切换到分组选项卡，将ID字段添加到分组依据列表
4. 切换到计算列选项卡，依次创建所需统计列：
   • 新建计算列：名称设为COL1_A，表达式输入SUM(COL1='A')
   • 同理创建COL1_B、COL1_C
   • 新建计算列：名称设为COL1_A_VAL1_SUM，表达式输入SUM(CASE WHEN COL1='A' THEN VAL1 ELSE 0 END)
   • 同理创建其他VAL1求和列
5. 点击运行按钮，生成结果表

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