Calculate average of variable and add as observation

[draroda]

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" 

[dcruik]

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;

[draroda]

Great. 

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

[Reeza]

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.

[dcruik]

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;

[Steelers_In_DC · Accepted solution]

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;

Be sure to note the cautions/advice for trying to keep up with current Mean values for these variables.  PROC REPORT is suited to create mean (and other aggregations) summaries for reporting purposes.

[draroda]

Perfect .

[LinusH]

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.

[bradkes]

Thank you very much, extremely helpful!!

[andreas_lds]

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;