DATA Step, Macro, Functions and more

How do I calculate average by grouping data ?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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;

 

View solution in original post


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: 9,676

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: 10,497

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 0 likes
  • 4 in conversation