topic Re: Calculate average of variable and add as observation in SAS Programming

Calculate average of variable and add as observation

draroda — Wed, 09 Dec 2015 16:57:52 GMT

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"

Re: Calculate average of variable and add as observation

dcruik — Wed, 09 Dec 2015 17:20:18 GMT

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;

Re: Calculate average of variable and add as observation

Steelers_In_DC — Fri, 28 Sep 2018 15:28:55 GMT

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.

Re: Calculate average of variable and add as observation

draroda — Wed, 09 Dec 2015 18:09:55 GMT

Great.

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

Re: Calculate average of variable and add as observation

draroda — Wed, 09 Dec 2015 18:10:31 GMT

Perfect .

Re: Calculate average of variable and add as observation

Reeza — Wed, 09 Dec 2015 18:22:50 GMT

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.

Re: Calculate average of variable and add as observation

dcruik — Wed, 09 Dec 2015 18:41:14 GMT

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;

Re: Calculate average of variable and add as observation

LinusH — Wed, 09 Dec 2015 19:01:41 GMT

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.

Betreff: Calculate average of variable and add as observation

andreas_lds — Thu, 10 Dec 2015 13:32:07 GMT

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;

Re: Calculate average of variable and add as observation

bradkes — Fri, 13 Jul 2018 18:48:11 GMT

Thank you very much, extremely helpful!!