topic Re: average by multiple groups in New SAS User

average by multiple groups

dapenDaniel — Fri, 24 Jan 2020 21:06:36 GMT

Hi SAS master,

I have a dataset like below.

ID	Year	Var1
1	2001	3.5
1	2003	32.6
1	2005	0
1	2005	0
1	2005	5.6
1	2006	72.5
1	2010	0
1	2012	4.5
2	2004	0
2	2004	5
2	2007	42.31
2	2012	95.12
2	2012	86.4
2	2012	49.3
2	2015	0
2	2015	0

I would like to get the average for each firm in each year. The dataset I want is below.

ID	Year	Var1
1	2001	3.5
1	2003	32.6
1	2005	1.866667
1	2006	72.5
1	2010	0
1	2012	4.5
2	2004	2.5
2	2007	42.31
2	2012	76.94
2	2015	0

what program do I need to use? Thanks.

Re: average by multiple groups

novinosrin — Fri, 24 Jan 2020 21:14:14 GMT

proc sql;
create table want as
select id, year,avg(var1) as avg
from have
group by id,year;
quit;

Re: average by multiple groups

novinosrin — Fri, 24 Jan 2020 21:16:20 GMT

proc summary data=have nway;
class id year;
var var1;
output out=want(drop=_:) mean=;
run;

Re: average by multiple groups

ballardw — Fri, 24 Jan 2020 23:40:10 GMT

I suggest that you also look at the results for @novinosrin's Proc Summary solution without the NWAY option and removing the (drop=) option on the output set.

Proc summary can create LOTS of different groups of summaries with a single pass through the data which can be very helpful for some tasks as you may not need to create multiple data sets but just select the appropriate _type_ value.