Contributor
Posts: 63

# Calculate average of variable and add as observation

Dear Alll,

I need to calculate mean of variable and add the same as last observation as below.

Input dataset.

Subject Score

a         1

a         .

a        3

a        7

b        4

b        .

b        1

b        7

Output:

Subject Score

a         1

a         .

a        3

a        7

Average of A="MEAN"

b        4

b        .

b        1

b        7

Average of b="MEAN"

Frequent Contributor
Posts: 132

## Re: Calculate average of variable and add as observation

[ Edited ]

Here's a way to get the output you are looking for in a data set.  If you want a report, I would look into different procedures.  Hope this helps!

``````data have;
input Subject\$ Score;
datalines;
a 1
a .
a 3
a 7
b 4
b .
b 1
b 7
;
run;

proc sql noprint;
select mean(Score) format 6.2 into: a_mean
from have
where Subject="a";

select mean(Score) format 6.2 into: b_mean
from have
where Subject="b";
quit;

data a_mean;
Subject="Average of A=";
Score=&a_mean;
run;

data b_mean;
Subject="Averge of B=";
Score=&b_mean;
run;

data want;
length Subject \$25;
set have (where=(Subject="a"))
a_mean
have (where=(Subject="b"))
b_mean;
run;``````
Contributor
Posts: 63

## Re: Calculate average of variable and add as observation

Great.

But i may have more subjects when data gets entered , so is it possible to create macro for same ?

Super User
Posts: 23,700

## Re: Calculate average of variable and add as observation

Why are you doing this? Do you actually need a dataset or do you need a printed report? If it's a printed report then you need a proc report or possibly a print instead of this. Regardless you don't need a macro but you do need to clarify your requirements.
Frequent Contributor
Posts: 132

## Re: Calculate average of variable and add as observation

[ Edited ]

This will loop through each individual subject and create the same output as many times as there are unique subjects.  As @Reeza mentioned though, there could be a better way to do this if you don't need it in a data set that doesn't use macros and such.

``````data have;
input Subject\$ Score;
datalines;
a 1
a .
a 3
a 7
b 4
b .
b 1
b 7
c 3
c 7
c .
c 1
;
run;

options mprint symbolgen mlogic;
%macro subject;
proc sql;
create table subjects as
select distinct Subject
from have;
quit;

data _NULL_;
set subjects end=last;
call symputx(cats("Subject",_N_),Subject);
If last then call symputx("n",_N_);
run;

%do i=1 %to &n;
proc sql noprint;
select mean(Score) format 6.2 into: mean
from have
where Subject="&&Subject&i";
quit;

data mean;
Subject="Average of &&Subject&i=";
Score=&mean;
run;

%if &i=1 %then %do;
data want;
length Subject \$25;
set have (where=(Subject="&&Subject&i"))
mean;
run;
%end;
%else %do;
data combine;
length Subject \$25;
set have (where=(Subject="&&Subject&i"))
mean;
run;

data want;
set want
combine;
run;
%end;
%end;
%mend;
%subject;``````
Valued Guide
Posts: 863

## Re: Calculate average of variable and add as observation

[ Edited ]

Here is a solution:

data have;
input Subject\$ Score;
cards;
a        1
a        .
a        3
a        7
b        4
b        .
b        1
b        7
;

proc sql;
create table prep as
select *,avg(coalesce(score,0)) as mean format = number.2
from have
group by subject;

data want;
length subject \$50.;
set prep;
by subject;
if last.subject then do;output;
subject = catx(' ','Average of',subject,'= ');
score= mean;
end;
drop mean;
output;
run;

Contributor
Posts: 63

Perfect .

Super User
Posts: 5,876

## Re: Calculate average of variable and add as observation

No, it's not.

Doing aggregations for table display is a job for a reporting procedure, like PROC REPORT, and should not be stored in a table.

Data never sleeps
New Contributor
Posts: 3

## Re: Calculate average of variable and add as observation

Thank you very much, extremely helpful!!
Valued Guide
Posts: 564

## Betreff: Calculate average of variable and add as observation

Having those mean-rows in the dataset does not sound useful for further usage of that dataset. Anything, except for printing, will require additional code to skip the mean-rows.

Solution using DOW-loop:

``````data work.want;
count = 0;
scoreSum = 0;
do _n_ = 1 by 1 until(last.Subject);
set work.have;
by Subject;

count = count +1;
scoreSum = sum(scoreSum, Score);

output;
end;

Subject = catx(" ", "Average of ", Subject);
Score = scoreSum / count;
output;

drop count scoreSum;
run;
``````
Discussion stats
• 9 replies
• 3552 views
• 5 likes
• 7 in conversation