topic How to calculate weighed average by year in SAS Programming

How to calculate weighed average by year

caveman529 — Sat, 15 Feb 2014 21:23:19 GMT

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!

Re: How to calculate weighed average by year

overmar — Sat, 15 Feb 2014 21:50:59 GMT

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

Re: How to calculate weighed average by year

caveman529 — Sat, 15 Feb 2014 23:54:29 GMT

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

Re: How to calculate weighed average by year

Tom — Sun, 16 Feb 2014 01:04:27 GMT

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;