topic Re: How do I calculate average by grouping data ? in SAS Programming

How do I calculate average by grouping data ?

bobbyc — Thu, 25 Feb 2016 22:42:23 GMT

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.66 Average

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

Thanks much !

Re: How do I calculate average by grouping data ?

lxn1021 — Fri, 26 Feb 2016 00:54:31 GMT

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;

Re: How do I calculate average by grouping data ?

Ksharp — Fri, 26 Feb 2016 01:19:22 GMT

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;

Re: How do I calculate average by grouping data ?

ballardw — Thu, 03 Mar 2016 23:16:28 GMT

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.