BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mvsas
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
stat_sas
Ammonite | Level 13

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
Onyx | Level 15

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
Super User

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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