Mean across observations and variables

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Mean across observations and variables

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:

TenantMonth 1Month 2Month 3
A5.66.84.9
B4.84.25.1
C8.69.37.6
D4.55.34.7
E7.26.97.8

I want the output to look like this:

TenantMonth 1Month 2Month 3Your AverageOverall Average
A5.66.84.95.776.22
B4.84.25.14.76.22
C8.69.37.68.56.22
D4.55.34.74.836.22
E7.26.97.87.36.22

Thanks!


Accepted Solutions
Solution
‎08-28-2014 02:25 PM
Respected Advisor
Posts: 4,992

Re: Mean across observations and variables

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,203

Re: Mean across observations and 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
;

data want;
set have;
your_avg=mean(of monthSmiley Happy;
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;

Respected Advisor
Posts: 3,124

Re: Mean across observations and variables

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 MONSmiley Happy;

           _TOTAL+TENANT_AVG;

     END;

     OVERALL_AVG=_TOTAL/(_N_-1);

     DO UNTIL (LAST);

           SET HAVE END=LAST;

           TENANT_AVG=MEAN(OF MONSmiley Happy;

           OUTPUT;

     END;

     DROP _:;

RUN;

Grand Advisor
Posts: 9,584

Re: Mean across observations and variables

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

New Contributor
Posts: 4

Re: Mean across observations and variables

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.

Respected Advisor
Posts: 4,992

Re: Mean across observations and variables

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.

New Contributor
Posts: 4

Re: Mean across observations and variables

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

M1M2M3Your AverageOverall Overage
A5.66.84.95.7666676.285714
B4.84.25.14.76.285714
C8.69.37.68.56.285714
D4.54.74.66.285714
E7.26.97.87.36.285714

Overall average = 88/14=6.285714

Solution
‎08-28-2014 02:25 PM
Respected Advisor
Posts: 4,992

Re: Mean across observations and variables

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 435 views
  • 6 likes
  • 5 in conversation