How do I calculate average by grouping data ?

[bobbyc]

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 !

[lxn1021 · Accepted solution]

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;

[Ksharp]

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;

[ballardw]

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.