Weighted Percentiles

Replicating the weighted percentile algorithm used in SAS is hard because it’s undocumented. Howerver, using some clever heuristics did show me a way how SAS calculates the weighted percentiles and I wrote this algorithm in SQL Server.

To calculate as weighted percentile you need two values. One column with the data and one column with the weight. The base rule is that the 100th weighted percentile is the largest data value, no matter what.

The first exception is that all rows with a weight of zero or less are omitted. The second exception is that if the weighted percentile value is exactly the same value as the running total over weight, the weighted percentile value is averaged with next row.

Leave a Reply