## How to calculate weighed average by year

Regular Contributor
Posts: 161

# How to calculate weighed average by year

Dear All:

Could you please tell me how to summarize the weighed average of the wage by company_id and year?

I have:

 company_id job_number year wage 3 1 2011 52000 4 1 2003 85000 3 1 2011 41500 3 1 2010 51000 3 2 2010 22 3 1 2010 50000 3 1 2010 57500 4 1 2003 75054 3 1 2007 106401 3 1 2012 49750 3 1 2012 54000 2 1 2011 32094 2 1 2006 47500 3 1 2008 61000 3 1 2011 65500 4 1 2006 51000 2 1 2012 70000 2 1 2012 74300 2 1 2012 50000 2 1 2007 50000 3 1 2010 52000

I hope to get:

 company_id wage_2001 wage_2002 wage_2003 1 2 3 4 5

Thanks!

Frequent Contributor
Posts: 83

## Re: How to calculate weighed average by year

proc sql;

create table have as

select distinct company_id, (sum(case when year = 2001 then wage else 0 end)) as wage_2001, (sum(case when year = 2002 then wage else 0 end)) as wage_2002,

(sum(case when year = 2003 then wage else 0 end)) as wage_2003

from want

group by company_id;

quit;

But I am sure that there is also a way to do this with Proc Summary or Proc Report, those just aren't things that I typically write code in. Granted you said something about weighted average, but didn't really include what it should be weighted by, or even how it should be evaluated. If you wanted the mean salary for each of these years by the company_id you could change sum to avg or mean.

Hope this helps

Regular Contributor
Posts: 161

## Re: How to calculate weighed average by year

Thanks, Overmar.

I used the proc sql + proc transpose.  but it is kind of ugly.  i don't know the proper SAS for doing this.  I agree with you that proc summary must be able to handle this situation

Super User
Posts: 8,111

## Re: How to calculate weighed average by year

What are you weighting by?

You can calculate means using PROC SUMMARY/MEANS and convert to columns using PROC TRANSPOSE.

data have ;

input company_id job_number year wage @@;

cards;

3 1 2011 52000 4 1 2003 85000 3 1 2011 41500 3 1 2010 51000

3 2 2010 22 3 1 2010 50000 3 1 2010 57500 4 1 2003 75054

3 1 2007 106401 3 1 2012 49750 3 1 2012 54000 2 1 2011 32094

2 1 2006 47500 3 1 2008 61000 3 1 2011 65500 4 1 2006 51000

2 1 2012 70000 2 1 2012 74300 2 1 2012 50000 2 1 2007 50000

3 1 2010 52000

;;;;

proc summary data=have nway ;

class company_id year ;

var wage;

output out=wages mean= ;

run;

proc transpose data=wages out=want(drop=_ prefix=wage_;

by company_id ;

id year ;

var wage;

run;

Discussion stats
• 3 replies
• 257 views
• 0 likes
• 3 in conversation