topic Re: Mean across observations and variables in SAS Programming

Mean across observations and variables

mvsas — Wed, 27 Aug 2014 20:05:13 GMT

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!

Re: Mean across observations and variables

stat_sas — Wed, 27 Aug 2014 20:16:39 GMT

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;

Re: Mean across observations and variables

Haikuo — Wed, 27 Aug 2014 21:43:43 GMT

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;

Re: Mean across observations and variables

Ksharp — Thu, 28 Aug 2014 13:24:36 GMT

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

Re: Mean across observations and variables

mvsas — Thu, 28 Aug 2014 16:20:28 GMT

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.

Re: Mean across observations and variables

Astounding — Thu, 28 Aug 2014 16:47:57 GMT

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.

Re: Mean across observations and variables

mvsas — Thu, 28 Aug 2014 17:45:30 GMT

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

Re: Mean across observations and variables

Astounding — Thu, 28 Aug 2014 18:25:44 GMT

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.