Output 6.2 PROC SQL Output for Weighted Averages
How It Works
This solution uses an in-line view to create a temporary table that eliminates the negative
data values in the Weight column. The in-line view is a query that performs the
• selects the Gender and Value columns.
• uses a CASE expression to select the value from the Weight column. If Weight is
greater than zero, then it is retrieved. If Weight is less than zero, then a value of zero
is used in place of the Weight value.
(select Gender, Value,
when Weight>0 then Weight
end as Weight
The first, or outer, SELECT statement in the query, performs the following tasks:
• selects the Gender column
• constructs a weighted average from the results that were retrieved by the in-line view
The weighted average is the sum of the products of Value and Weight divided by the sum
of the Weights.
select Gender, sum(Value*Weight)/sum(Weight) as WeightedAverage
Finally, the query uses a GROUP BY clause to combine the data so that the calculation
is performed for each gender.
group by Gender;
You have two copies of a table. One of the copies has been updated. You want to see
which rows have been changed.
180 Chapter 6 • Practical Problem-Solving with PROC SQL