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!
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.
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;
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;
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
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.
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.