## How do I calculate average by grouping data ?

Solved
Occasional Contributor
Posts: 11

# How do I calculate average by grouping data ?

Hi,

I need to calculate average of values for each group and create a new record in the same dataset with value as 'Average' in new variable.

This is what I have:

GROUP           VALUE                TYPE

--------           ------------            -----------

1                     11

1                     11.5

1                     12.5

2                      14

2                      12

2                      13

This is what I need:

GROUP             VALUE               TYPE

--------           ------------            -----------

1                     11

1                     11.50

1                     12.50

1                     11.66              Average

2                     14

2                     12

2                     12

2                     12.66              Average

Can someone please teach me what is the best way to do it ?

Thanks much !

Accepted Solutions
Solution
‎03-08-2016 08:26 PM
Occasional Contributor
Posts: 17

## Re: How do I calculate average by grouping data ?

Try this:

data aaa;
set aaa;
by group;
if first.group then Type1=value;
if first.group=0 then
Type1+value;
if first.group then N=1;
if first.group=0 then
N+1;
if last.group then
Type1=round(Type1/N,0.01);
keep group value type1;
run;

data avg;
set aaa;
by group;
if last.group then output;
keep group type1;
run;

data avg;
set avg;
rename type1=value;
run;

data aaa_avg;
set aaa avg;
drop type1;
run;

proc sort data=aaa_avg;
by group;
run;

data aaa_avg;
set aaa_avg;
by group;
if last.group then
type="Average";
run;

All Replies
Solution
‎03-08-2016 08:26 PM
Occasional Contributor
Posts: 17

## Re: How do I calculate average by grouping data ?

Try this:

data aaa;
set aaa;
by group;
if first.group then Type1=value;
if first.group=0 then
Type1+value;
if first.group then N=1;
if first.group=0 then
N+1;
if last.group then
Type1=round(Type1/N,0.01);
keep group value type1;
run;

data avg;
set aaa;
by group;
if last.group then output;
keep group type1;
run;

data avg;
set avg;
rename type1=value;
run;

data aaa_avg;
set aaa avg;
drop type1;
run;

proc sort data=aaa_avg;
by group;
run;

data aaa_avg;
set aaa_avg;
by group;
if last.group then
type="Average";
run;

Super User
Posts: 10,784

## Re: How do I calculate average by grouping data ?

``````data have;
input GROUP           VALUE     ;
cards;
1                     11
1                     11.5
1                     12.5
2                      14
2                      12
2                      13
;
run;
proc sql;
create table want as
select * from have
outer union corr
select  GROUP,mean(VALUE) as VALUE,'Average' as TYPE
from have
group by GROUP
order by GROUP,TYPE;
quit;``````
Super User
Posts: 13,577

## Re: How do I calculate average by grouping data ?

I consider it very dangerous to add a record in this fashion as further analysis should exclude that "average" line value.

If this is for a report the Proc Report will do a summary at the end the change of a variable and insert the summary average.

🔒 This topic is solved and locked.