如何在PROC SQL中创建按MOB子组计算占比的汇总表

核心修改点 #

1. 修正GROUP BY子句的笔误：将FLAG_LDP替换为表中实际存在的FLAG字段
2. 把占比计算的分母从全表总合约数，调整为对应MOB分组的总合约数，即可实现组内占比统计

写法1：关联子查询实现（写法简洁） #

PROC SQL;
SELECT 
    MOB,
    FLAG,
    COUNT(*) AS Contracts,
    (COUNT(*)*1.0 / (SELECT COUNT(*) FROM Clients c2 WHERE c2.MOB = c1.MOB)) AS Share FORMAT percent8.2
FROM Clients c1
GROUP BY MOB, FLAG;
QUIT;

写法2：预聚合关联实现（大数据量下效率更高） #

先分别聚合MOB+FLAG维度的合约数、MOB维度的总合约数，再关联计算占比，避免每行执行子查询：

PROC SQL;
SELECT 
    t1.MOB,
    t1.FLAG,
    t1.Contracts,
    (t1.Contracts * 1.0 / t2.Total_MOB) AS Share FORMAT percent8.2
FROM 
    (SELECT MOB, FLAG, COUNT(*) AS Contracts FROM Clients GROUP BY MOB, FLAG) t1
LEFT JOIN
    (SELECT MOB, COUNT(*) AS Total_MOB FROM Clients GROUP BY MOB) t2
ON t1.MOB = t2.MOB;
QUIT;

样例输出结果 #

基于提供的测试数据，最终输出如下：

内容的提问来源于stack exchange，提问作者Marcelo Wuo Lopes