How to calculate weighed average by year

Reply
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_idjob_numberyearwage
31201152000
41200385000
31201141500
31201051000
32201022
31201050000
31201057500
41200375054
312007106401
31201249750
31201254000
21201132094
21200647500
31200861000
31201165500
41200651000
21201270000
21201274300
21201250000
21200750000
31201052000

I hope to get:

company_idwage_2001wage_2002wage_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 Smiley Happy

Super User
Super User
Posts: 6,502

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=_Smiley Happy prefix=wage_;

  by company_id ;

  id year ;

  var wage;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 231 views
  • 0 likes
  • 3 in conversation