You can use the following methods to calculate a weighted average in SAS:
Method 1: Calculate Weighted Average
proc sql;
create table new_data as
select sum(weight * value) / sum(weight) as weighted_average
from original_data;
quit;
Method 2: Calculate Weighted Average by Group
proc sql;
create table new_data as
select grouping_variable,
sum(weight * value) / sum(weight) as weighted_average
from original_data
group by grouping_variable;
quit;
The following examples show how to use each method with the following dataset in SAS:
/*create dataset*/
data original_data;
input sales_rep $ price amount;
datalines;
A 8 1
A 5 3
A 6 2
B 7 2
B 12 5
B 14 4
;
run;
/*view dataset*/
proc print data=original_data;
Example 1: Calculate Weighted Average
The following code shows how to calculate a weighted average for the price variable, using the amount variable as the weight:
/*calculate weighted average of price*/
proc sql;
create table new_data as
select sum(amount * price) / sum(amount) as weighted_average
from original_data;
quit;
/*view weighted average of price*/
proc print data=new_data;
The weighted average of price turns out to be 9.70588.
Example 2: Calculate Weighted Average by Group
The following code shows how to calculate the weighted average of the price variable, grouped by the sales_rep variable:
/*calculate weighted average of price, grouped by sales_rep*/
proc sql;
create table new_data as
select sales_rep,
sum(amount * price) / sum(amount) as weighted_average
from original_data
group by sales_rep;
quit;
/*view results*/
proc print data=new_data;
From the output we can see:
- The weighted average of price for sales rep A is 5.8333.
- The weighted average of price for sales rep B is 11.8182.
Additional Resources
The following tutorials explain how to perform other common tasks in SAS:
How to Calculate the Mean by Group in SAS
How to Calculate Correlation in SAS
How to Create Frequency Tables in SAS