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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.