General SAS Programming

[mvsas]

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!

[Astounding · Accepted solution]

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.

[stat_sas]

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;

[Haikuo]

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;

[Ksharp]

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

[mvsas]

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.

[Astounding]

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.

[mvsas]

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

[Astounding · Accepted solution]

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.