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 !
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;
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;
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.