08-27-2014 04:05 PM
Hello,
I was hoping someone could help me with some code to determine the mean across rows and variables. All of the examples I can find tell me how to do across observations OR variables, but not the combination yielding the "Overall Average" I'm wanting below.
Input Data Set:
Tenant | Month 1 | Month 2 | Month 3 |
A | 5.6 | 6.8 | 4.9 |
B | 4.8 | 4.2 | 5.1 |
C | 8.6 | 9.3 | 7.6 |
D | 4.5 | 5.3 | 4.7 |
E | 7.2 | 6.9 | 7.8 |
I want the output to look like this:
Tenant | Month 1 | Month 2 | Month 3 | Your Average | Overall Average |
A | 5.6 | 6.8 | 4.9 | 5.77 | 6.22 |
B | 4.8 | 4.2 | 5.1 | 4.7 | 6.22 |
C | 8.6 | 9.3 | 7.6 | 8.5 | 6.22 |
D | 4.5 | 5.3 | 4.7 | 4.83 | 6.22 |
E | 7.2 | 6.9 | 7.8 | 7.3 | 6.22 |
Thanks!
08-28-2014 02:25 PM
As you have seen, everyone has his own favorite style. Here's mine:
data want;
do until (done1);
set have end=done1;
overall_sum + sum(of m1-m3);
overall_n + n(of m1-m3);
end;
overall_average = overall_sum / overall_n;
drop overall_sum overall_n;
do until (done2);
set have end=done2;
your_average = mean(of m1-m3);
output;
end;
run;
Good luck.
08-27-2014 04:16 PM
data have;
input Tenant $ Month1 Month2 Month3;
datalines;
A 5.6 6.8 4.9
B 4.8 4.2 5.1
C 8.6 9.3 7.6
D 4.5 5.3 4.7
E 7.2 6.9 7.8
;
data want;
set have;
your_avg=mean(of month;
format your_avg 8.2;
run;
proc sql;
create table done as
select *,avg(your_avg) as overall_avg from want;
quit;
proc print data=done;
run;
08-27-2014 05:43 PM
Another way (2XDOW):
data have;
input Tenant $ Month1 Month2 Month3;
datalines;
A 5.6 6.8 4.9
B 4.8 4.2 5.1
C 8.6 9.3 7.6
D 4.5 5.3 4.7
E 7.2 6.9 7.8
;
DATA WANT;
DO _N_=1 TO NOBS;
SET HAVE NOBS=NOBS POINT=_N_;
TENANT_AVG=MEAN(OF MON;
_TOTAL+TENANT_AVG;
END;
OVERALL_AVG=_TOTAL/(_N_-1);
DO UNTIL (LAST);
SET HAVE END=LAST;
TENANT_AVG=MEAN(OF MON;
OUTPUT;
END;
DROP _:;
RUN;
08-28-2014 09:24 AM
Suppose there are not any missing value in month variables.
data have; input Tenant $ Month1 Month2 Month3; datalines; A 5.6 6.8 4.9 B 4.8 4.2 5.1 C 8.6 9.3 7.6 D 4.5 5.3 4.7 E 7.2 6.9 7.8 ; run; proc sql; create table want as select *,mean(month1,month2,month3) as avg,mean(mean(month1,month2,month3)) as overall_avg from have; quit;
Xia Keshan
08-28-2014 12:20 PM
I suppose I should have included at least one missing value in the original data set. There is no guarantee that all values will be present.
I believe all of the above solutions require no missing values to work.
08-28-2014 12:47 PM
Also, present the formula you would like for the overall mean. Once there are missing values involved, there will be more than one way to calculate an overall mean.
08-28-2014 01:45 PM
Overall mean should be calculated as the sum of all data divided by n where n is the count of non-missing values.
So if I have
M1 | M2 | M3 | Your Average | Overall Overage | |
A | 5.6 | 6.8 | 4.9 | 5.766667 | 6.285714 |
B | 4.8 | 4.2 | 5.1 | 4.7 | 6.285714 |
C | 8.6 | 9.3 | 7.6 | 8.5 | 6.285714 |
D | 4.5 | 4.7 | 4.6 | 6.285714 | |
E | 7.2 | 6.9 | 7.8 | 7.3 | 6.285714 |
Overall average = 88/14=6.285714
08-28-2014 02:25 PM
As you have seen, everyone has his own favorite style. Here's mine:
data want;
do until (done1);
set have end=done1;
overall_sum + sum(of m1-m3);
overall_n + n(of m1-m3);
end;
overall_average = overall_sum / overall_n;
drop overall_sum overall_n;
do until (done2);
set have end=done2;
your_average = mean(of m1-m3);
output;
end;
run;
Good luck.
Need further help from the community? Please ask a new question.