BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bobbyc
Fluorite | Level 6

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 !

 

1 ACCEPTED SOLUTION

Accepted Solutions
lxn1021
Obsidian | Level 7

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

3 REPLIES 3
lxn1021
Obsidian | Level 7

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
Super User
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
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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