12-09-2015 11:57 AM
I need to calculate mean of variable and add the same as last observation as below.
Average of A="MEAN"
Average of b="MEAN"
12-09-2015 12:14 PM - edited 12-09-2015 12:20 PM
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;
12-09-2015 01:22 PM
12-09-2015 01:40 PM - edited 12-09-2015 01:41 PM
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;
12-09-2015 12:21 PM - edited 12-09-2015 12:27 PM
Here is a solution:
input Subject$ Score;
create table prep as
select *,avg(coalesce(score,0)) as mean format = number.2
group by subject;
length subject $50.;
if last.subject then do;output;
subject = catx(' ','Average of',subject,'= ');
12-09-2015 02:01 PM
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.
12-10-2015 08:32 AM
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;