如何使用SAS Proc SQL计算加权中位数？

你已经掌握了用Proc SQL计算加权均值的方法，现在想实现加权中位数的计算，确实可以用纯Proc SQL的方案搞定。核心思路是先确定中位数对应的权重分界点，再通过累计权重定位到对应的value区间，最终计算出结果。

先回顾你的示例数据：

data table;
    input value weight;
    datalines;
1 1 
2 1 
3 2 
;
run;

用Proc Means能轻松得到加权均值2.25、加权中位数2.5，下面用Proc SQL来复现这个结果。

实现步骤&代码 #

1. 先计算总权重的一半，明确中位数的位置分界
2. 按value排序后计算累计权重
3. 根据总权重的奇偶性，分情况计算加权中位数

对应的Proc SQL代码如下：

proc sql;
    select 
        case
            /* 总权重为偶数时，取两个中间位置值的平均值 */
            when mod(total_weight, 2) = 0 then 
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= total_weight/2
                having cum_weight = min(cum_weight)
                ) 
                + 
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= total_weight/2 + 1
                having cum_weight = min(cum_weight)
                ) 
                / 2
            /* 总权重为奇数时，直接取对应位置的value */
            else
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= (total_weight + 1)/2
                having cum_weight = min(cum_weight)
                )
        end as WeightedMedian
    from (
        select sum(weight) as total_weight from table
    ) t;
quit;

代码说明 #

• 内层子查询用窗口函数sum(weight) over(order by value)计算每个value对应的累计权重，确保数据按value升序排列
• 外层通过总权重的奇偶性分支处理：
  • 你的示例总权重是4（偶数），需要找到累计权重≥2和≥3的最小位置对应的value（分别是2和3），取平均值得到2.5，和Proc Means结果一致
  • 如果总权重是奇数，直接定位到累计权重≥(总权重+1)/2的第一个value即可

如果想一次性计算加权均值和加权中位数，可以整合代码：

proc sql;
    select 
        sum(value*weight)/sum(weight) as WeightedAverage,
        case
            when mod(total_weight, 2) = 0 then 
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= total_weight/2
                having cum_weight = min(cum_weight)
                ) 
                + 
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= total_weight/2 + 1
                having cum_weight = min(cum_weight)
                ) 
                / 2
            else
                (select value from (
                    select value, sum(weight) over(order by value) as cum_weight
                    from table
                ) t
                where cum_weight >= (total_weight + 1)/2
                having cum_weight = min(cum_weight)
                )
        end as WeightedMedian
    from (
        select sum(weight) as total_weight from table
    ) t, table;
quit;

这段代码会同时输出加权均值2.25和加权中位数2.5，和Proc Means的结果完全匹配。

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