topic Re: How to calculate average by variable and with conditions in SAS Programming

How to calculate average by variable and with conditions

d0816 — Thu, 29 Aug 2019 15:27:40 GMT

Hello,

I have following sample data which I sorted by company and Commission (high to low) (original dataset is more than 3000 rows). For each of the company, I want to take top 20% commission data and calculate average of that top 20% only, (not all data for a company) with a flexibility to change the top n%. How can I do this?

Company	Commission
I	$17.75
I	$15.00
I	$15.00
I	$13.00
I	$12.50
I	$10.00
I	$10.00
I	$9.89
I	$9.15
I	$9.00
I	$9.00
I	$8.75
I	$8.50
I	$8.00
I	$7.25
F	$15.50
F	$14.00
F	$14.00
F	$13.00
F	$13.00
F	$12.50
F	$12.00
F	$12.00
F	$12.00
F	$11.50
F	$11.50
F	$11.50
F	$11.00
F	$10.50
F	$10.45
F	$10.00
F	$10.00
F	$10.00
F	$9.00
F
M	$17.29
M	$15.91
M	$14.25
M	$13.99
M	$13.55
M	$13.54
M	$13.50
M	$12.50
M	$12.06
M	$11.50
M	$11.31
M	$11.25
M	$11.15
M	$10.00
M	$10.00
M	$10.00
M	$10.00
M	$9.00
M	$8.00
M	$7.80
M	$7.25
R	$18.29
R	$15.60
R	$13.00
R	$11.00
R	$11.00
R	$11.00
R	$10.00
R	$10.00
R	$9.56
R	$9.00
R	$0.14
R
RO	$18.54
RO	$16.75
RO	$15.75
RO	$15.25
RO	$14.00
RO	$13.25
RO	$13.00
RO	$13.00
RO	$12.50
RO	$10.50
RO	$10.00
RO	$10.00
RO	$9.95
RO	$9.25
RO	$9.25
RO	$9.00
U	$15.00
U	$13.90
U	$12.00
U	$12.00
U	$11.50
U	$11.07
U	$11.07
U	$11.00
U	$10.00
U	$10.00
U	$9.25
U	$2.33
W	$15.00
W	$12.00
W	$9.50

For e.g. Company F has 20 rows, top 20% would be first 4 rows (because it is already sorted by commission for a company) and calculating average of those 4 commission data.

Company	Commission
F	$15.50
F	$14.00
F	$14.00
F	$13.00

Result wanted:

Company	Top n% Average Commission
I
F	$14.13
M
R
RO
U
W

Appreciate any suggestion.

Re: How to calculate average by variable and with conditions

PaigeMiller — Thu, 29 Aug 2019 15:38:09 GMT

You find the number of observations (with non-missing value for commission) for each company, then you can figure out which are the top 20% (ignoring ties and ignoring that you can't get exactly 20% unless the number of observations for a company is a multiple of 5), then you take the mean.

proc freq data=have(where=(not missing(commission)));
    table company/noprint out=_counts_;
run;
data want;
    merge have(where=(not missing(commission))) _counts_;
    by company;
    if first.company then sequence=0;
    sequence+1;
    if sequence/count<=0.2 then output;
run;
proc means data=want;
    by company;
    var commission;
run;

Also, for company W which has only 3 observations, this method returns nothing.

Re: How to calculate average by variable and with conditions

Jagadishkatam — Thu, 29 Aug 2019 15:46:53 GMT

Please try the below code

data have;
input Company$	Commission:dollar6.;
cards;
I $17.75
I $15.00
I $15.00
I $13.00
I $12.50
I $10.00
I $10.00
I $9.89
I $9.15
I $9.00
I $9.00
I $8.75
I $8.50
I $8.00
I $7.25
F $15.50
F $14.00
F $14.00
F $13.00
F $13.00
F $12.50
F $12.00
F $12.00
F $12.00
F $11.50
F $11.50
F $11.50
F $11.00
F $10.50
F $10.45
F $10.00
F $10.00
F $10.00
F $9.00
;

proc sort data=have;
by company descending Commission ;
run;

data have2;
set have;
by company;
retain row;
if first.company then row=1;
else row+1;
run;

proc sql;
create table want as select company, avg(Commission) as avg from (select company, Commission,row, round((count(*)*20)/100,1) as cnt from have2 group by company having row<=calculated cnt) group by company;
quit;

Re: How to calculate average by variable and with conditions

KachiM — Thu, 29 Aug 2019 16:04:44 GMT

@d0816

You have two answers. Just for fun I give another One Step Data Step. Your Data is presorted as you have claimed. Assuming your Data Set is named as 'HAVE' here comes:

data want;
   do _n = 1 by 1 until(last.Company);
      set have;
      by Company;
   end;
   sampsize = ceil(_n * 0.20);
   sum = 0;
   do _n = 1 by 1 until(last.Company);
      set have;
      by Company;
      if _n <= sampsize then sum + Commission;
      if last.Company then mean_Commision = sum / sampsize;
   end;
drop Commission;
run;

I have not dropped the intermediate variables used in the program for the sake giving you additional information. Here is the output:

obs 	_n 	Company 	sampsize 	sum 	mean_Commision
1 	15 	I 	3 	47.70 	15.9000
2 	19 	F 	4 	56.50 	14.1250
3 	21 	M 	5 	74.70 	14.9400
4 	11 	R 	3 	46.80 	15.6000
5 	16 	RO 	4 	66.29 	16.5725
6 	12 	U 	3 	40.90 	13.6333
7 	3 	W 	1 	15.00 	15.0000

Re: How to calculate average by variable and with conditions

Reeza — Thu, 29 Aug 2019 16:31:22 GMT

1. Use PROC RANK to group your data into the appropriate buckets. GROUPS=5 will create 5 groups that are each 20% of the data.

2. Use PROC MEANS to calculate the summary by company and calculated RANK.

proc sort data=company;
by company commission;
run;

proc rank data=have out=grouped groups=5;
by company;
var commission;
ranks commission_rank;
run;

proc means data=grouped noprint;
by company commission_rank;
var commission;
output out=want mean(commission) = avg_commission;
run;

This will provide the mean for all companies at all ranks.

You can easily filter this out to get just the top ranks though by adding another step. Then when you want to change your % you can change the GROUPS value.

proc sql;
create table want_filtered as
select *
from want
group by company, commission_rank
having commission_rank = max(commission_rank);
quit;

@d0816 wrote:

Hello,

I have following sample data which I sorted by company and Commission (high to low) (original dataset is more than 3000 rows). For each of the company, I want to take top 20% commission data and calculate average of that top 20% only, (not all data for a company) with a flexibility to change the top n%. How can I do this?

Company Commission

I $17.75

I $15.00

I $15.00

I $13.00

I $12.50

I $10.00

I $10.00

I $9.89

I $9.15

I $9.00

I $9.00

I $8.75

I $8.50

I $8.00

I $7.25

F $15.50

F $14.00

F $14.00

F $13.00

F $13.00

F $12.50

F $12.00

F $12.00

F $12.00

F $11.50

F $11.50

F $11.50

F $11.00

F $10.50

F $10.45

F $10.00

F $10.00

F $10.00

F $9.00

F

M $17.29

M $15.91

M $14.25

M $13.99

M $13.55

M $13.54

M $13.50

M $12.50

M $12.06

M $11.50

M $11.31

M $11.25

M $11.15

M $10.00

M $10.00

M $10.00

M $10.00

M $9.00

M $8.00

M $7.80

M $7.25

R $18.29

R $15.60

R $13.00

R $11.00

R $11.00

R $11.00

R $10.00

R $10.00

R $9.56

R $9.00

R $0.14

R

RO $18.54

RO $16.75

RO $15.75

RO $15.25

RO $14.00

RO $13.25

RO $13.00

RO $13.00

RO $12.50

RO $10.50

RO $10.00

RO $10.00

RO $9.95

RO $9.25

RO $9.25

RO $9.00

U $15.00

U $13.90

U $12.00

U $12.00

U $11.50

U $11.07

U $11.07

U $11.00

U $10.00

U $10.00

U $9.25

U $2.33

W $15.00

W $12.00

W $9.50

For e.g. Company F has 20 rows, top 20% would be first 4 rows (because it is already sorted by commission for a company) and calculating average of those 4 commission data.

Company Commission

F $15.50

F $14.00

F $14.00

F $13.00

Result wanted:

Company Top n% Average Commission

I

F $14.13

M

R

RO

U

W

Appreciate any suggestion.

Re: How to calculate average by variable and with conditions

PaigeMiller — Thu, 29 Aug 2019 16:41:47 GMT

The answers from @KachiM and @Jagadishkatam don't take into account that some of the original data has missing commission values, and so will give the wrong answer.

Re: How to calculate average by variable and with conditions

KachiM — Thu, 29 Aug 2019 17:23:41 GMT

@PaigeMiller

The data step program does not change. The sampsize is computed using CEIL() function. The OP may change to FLOOR() or ROUND().

Using the two missing values for F and R using the OP's Data Set, the new ouput is:

bs 	_n 	Company 	sampsize 	sum 	mean_Commision
1 	20 	F 	4 	56.50 	14.1250
2 	15 	I 	3 	47.70 	15.9000
3 	21 	M 	5 	74.70 	14.9400
4 	12 	R 	3 	46.80 	15.6000
5 	16 	RO 	4 	66.29 	16.5725
6 	12 	U 	3 	40.90 	13.6333
7 	3 	W 	1 	15.00 	15.0000

Re: How to calculate average by variable and with conditions

PaigeMiller — Thu, 29 Aug 2019 17:42:05 GMT

The variable _n is calculated including missing values. So sampsize is wrong, and the rest of the calculations are wrong.

Re: How to calculate average by variable and with conditions

Jagadishkatam — Thu, 29 Aug 2019 17:42:26 GMT

Thank you @PaigeMiller

I believe the below subset condition will help to get the correct answer with my code.

proc sort data=have;
by company descending Commission ;
where Commission  ne .;
run;

Re: How to calculate average by variable and with conditions

hashman — Thu, 29 Aug 2019 19:54:14 GMT

@PaigeMiller:

It's easy to take into account by not reading the rows where Commission is missing in the first place:

data have ;                                                                                                                                                                                                                                                     
  input Company :$1. Commission ;                                                                                                                                                                                                                               
  cards ;                                                                                                                                                                                                                                                       
F      15.50                                                                                                                                                                                                                                                    
F      14.00                                                                                                                                                                                                                                                    
F      14.00                                                                                                                                                                                                                                                    
F      13.00                                                                                                                                                                                                                                                    
F      13.00                                                                                                                                                                                                                                                    
F      12.50                                                                                                                                                                                                                                                    
F      12.00                                                                                                                                                                                                                                                    
F      12.00                                                                                                                                                                                                                                                    
F      12.00                                                                                                                                                                                                                                                    
F      11.50                                                                                                                                                                                                                                                    
F      11.50                                                                                                                                                                                                                                                    
F      11.50                                                                                                                                                                                                                                                    
F      11.00                                                                                                                                                                                                                                                    
F      10.50                                                                                                                                                                                                                                                    
F      10.45                                                                                                                                                                                                                                                    
F      10.00                                                                                                                                                                                                                                                    
F      10.00                                                                                                                                                                                                                                                    
F      10.00                                                                                                                                                                                                                                                    
F       9.00                                                                                                                                                                                                                                                    
F        .                                                                                                                                                                                                                                                      
I      17.75                                                                                                                                                                                                                                                    
I      15.00                                                                                                                                                                                                                                                    
I      15.00                                                                                                                                                                                                                                                    
I      13.00                                                                                                                                                                                                                                                    
I      12.50                                                                                                                                                                                                                                                    
I      10.00                                                                                                                                                                                                                                                    
I      10.00                                                                                                                                                                                                                                                    
I       9.89                                                                                                                                                                                                                                                    
I       9.15                                                                                                                                                                                                                                                    
I       9.00                                                                                                                                                                                                                                                    
I       9.00                                                                                                                                                                                                                                                    
I       8.75                                                                                                                                                                                                                                                    
I       8.50                                                                                                                                                                                                                                                    
I       8.00                                                                                                                                                                                                                                                    
I       7.25                                                                                                                                                                                                                                                    
M      17.29                                                                                                                                                                                                                                                    
M      15.91                                                                                                                                                                                                                                                    
M      14.25                                                                                                                                                                                                                                                    
M      13.99                                                                                                                                                                                                                                                    
M      13.55                                                                                                                                                                                                                                                    
M      13.54                                                                                                                                                                                                                                                    
M      13.50                                                                                                                                                                                                                                                    
M      12.50                                                                                                                                                                                                                                                    
M      12.06                                                                                                                                                                                                                                                    
M      11.50                                                                                                                                                                                                                                                    
M      11.31                                                                                                                                                                                                                                                    
M      11.25                                                                                                                                                                                                                                                    
M      11.15                                                                                                                                                                                                                                                    
M      10.00                                                                                                                                                                                                                                                    
M      10.00                                                                                                                                                                                                                                                    
M      10.00                                                                                                                                                                                                                                                    
M      10.00                                                                                                                                                                                                                                                    
M       9.00                                                                                                                                                                                                                                                    
M       8.00                                                                                                                                                                                                                                                    
M       7.80                                                                                                                                                                                                                                                    
M       7.25                                                                                                                                                                                                                                                    
R      18.29                                                                                                                                                                                                                                                    
R      15.60                                                                                                                                                                                                                                                    
R      13.00                                                                                                                                                                                                                                                    
R      11.00                                                                                                                                                                                                                                                    
R      11.00                                                                                                                                                                                                                                                    
R      11.00                                                                                                                                                                                                                                                    
R      10.00                                                                                                                                                                                                                                                    
R      10.00                                                                                                                                                                                                                                                    
R      9.56                                                                                                                                                                                                                                                     
R      9.00                                                                                                                                                                                                                                                     
R      0.14                                                                                                                                                                                                                                                     
R       .                                                                                                                                                                                                                                                       
RO    18.54                                                                                                                                                                                                                                                     
RO    16.75                                                                                                                                                                                                                                                     
RO    15.75                                                                                                                                                                                                                                                     
RO    15.25                                                                                                                                                                                                                                                     
RO    14.00                                                                                                                                                                                                                                                     
RO    13.25                                                                                                                                                                                                                                                     
RO    13.00                                                                                                                                                                                                                                                     
RO    13.00                                                                                                                                                                                                                                                     
RO    12.50                                                                                                                                                                                                                                                     
RO    10.50                                                                                                                                                                                                                                                     
RO    10.00                                                                                                                                                                                                                                                     
RO    10.00                                                                                                                                                                                                                                                     
RO     9.95                                                                                                                                                                                                                                                     
RO     9.25                                                                                                                                                                                                                                                     
RO     9.25                                                                                                                                                                                                                                                     
RO     9.00                                                                                                                                                                                                                                                     
U     15.00                                                                                                                                                                                                                                                     
U     13.90                                                                                                                                                                                                                                                     
U     12.00                                                                                                                                                                                                                                                     
U     12.00                                                                                                                                                                                                                                                     
U     11.50                                                                                                                                                                                                                                                     
U     11.07                                                                                                                                                                                                                                                     
U     11.07                                                                                                                                                                                                                                                     
U     11.00                                                                                                                                                                                                                                                     
U     10.00                                                                                                                                                                                                                                                     
U     10.00                                                                                                                                                                                                                                                     
U      9.25                                                                                                                                                                                                                                                     
U      2.33                                                                                                                                                                                                                                                     
W     15.00                                                                                                                                                                                                                                                     
W     12.00                                                                                                                                                                                                                                                     
W      9.50                                                                                                                                                                                                                                                     
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
%let PCT = 20 ;                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                
data want (drop = commission) ;                                                                                                                                                                                                                                 
  do _n_ = 1 by 1 until (last.company) ;                                                                                                                                                                                                                        
    set have (where=(N(commission))) ;                                                                                                                                                                                                                          
    by company ;                                                                                                                                                                                                                                                
  end ;                                                                                                                                                                                                                                                         
  N = floor (_n_ * &PCT / 100) ;                                                                                                                                                                                                                                  
  do _n_ = 1 to _n_ ;                                                                                                                                                                                                                                           
    set have (where=(N(commission))) ;                                                                                                                                                                                                                          
    if _n_ <= N then Sum = sum (Sum, commission) ;                                                                                                                                                                                                              
  end ;                                                                                                                                                                                                                                                         
  Mean = divide (Sum, N) ;                                                                                                                                                                                                                                      
run ;

As @KachiM has pointed out, FLOOR can be replaced with another function the OP may find more suitable depending on more detailed specs.

Kind regards

Paul D.

Re: How to calculate average by variable and with conditions

d0816 — Thu, 29 Aug 2019 21:06:46 GMT

Hi @hashman, I did not understand the part where you said "FLOOR can be replaced with another function the OP may find more suitable depending on more detailed specs".

Thank you for all the discussion going on in this thread. I am trying the code and figuring out how it is working.

Re: How to calculate average by variable and with conditions

d0816 — Thu, 29 Aug 2019 21:11:40 GMT

Hi @hashman,Thank you for replying. I am trying the code and figuring out how it is working.

About "The OP may change to FLOOR() or ROUND()", when should FLOOR() or ROUND() be used?

Re: How to calculate average by variable and with conditions

hashman — Thu, 29 Aug 2019 22:05:28 GMT

@d0816:

Meaning FLOOR or CEIL can be chosen depending on the business specs.

As an example, if you have PCT=20 and N=17 records in a group by COMPANY, the 20 percent is 3.4 records. If in the case of such a split the specs call for 3 records to be counted, you'd use FLOOR; if they call for 4 records, you use CEIL. Note that FLOOR will result in no records included at all if N*PCT/100 < 1 (e.g. if with PCT=20 you have N<5).

Kind regards

Paul D.

Re: How to calculate average by variable and with conditions

Reeza — Thu, 29 Aug 2019 22:22:08 GMT

@d0816 wrote:

Hi @hashman, I did not understand the part where you said "FLOOR can be replaced with another function the OP may find more suitable depending on more detailed specs".

Thank you for all the discussion going on in this thread. I am trying the code and figuring out how it is working.

If you're a beginner I would recommend using one of the simpler solutions you can understand and modify on your own.

Re: How to calculate average by variable and with conditions

PaigeMiller — Fri, 30 Aug 2019 11:58:41 GMT

I like the answer by @Reeza best, better than my own answer. I like it best because it uses PROC RANK which automatically handles the missing values properly and correctly. I also like that it uses PROC MEANS, rather than data step code to do the calculations of the mean value. Especially for beginners, PROC MEANS is much easier (and it is a much more tested and therefore less risky) than any data step code to calculate the mean. That is one of the main benefits of SAS, they have done the hard work to create the algorithms, verify the algorithms and also provide the proper handling of missings. PROC RANK also offers a number of options on how to handle ties, which none of the other solutions do (not even my solution).

Re: How to calculate average by variable and with conditions

KachiM — Fri, 30 Aug 2019 15:17:04 GMT

@PaigeMiller

I respect your views. But I differ from you.

"Especially for beginners, PROC MEANS is much easier (and it is a much more tested and therefore less risky) than any data step code to calculate the mean. "

I believe that the OP will choose a solution that is convenient to him. He might also be interested to learn other solutions. Besides, there are several others who wish to to learn Data Step Programming in the Community. I further believe that adding and dividing is not more risky for SAS Programmers.

Kind regards

DATASP

Re: How to calculate average by variable and with conditions

PaigeMiller — Sat, 31 Aug 2019 12:06:29 GMT

I have no objections to people learning data steps and deciding what code to use that is best for them. In fact, every SAS programmer needs to learn how to do things in data steps, because data steps can perform a wide variety of extremely useful tasks, many of which cannot be performed in any other way in SAS.

But when I advise people on what to do, I will advise what I said, that using a SAS PROC to perform a task (if such a PROC exists) is what I advise.

@KachiM wrote:

I further believe that adding and dividing is not more risky for SAS Programmers.

And as we have seen in this thread, data is presented with missing values, and people have written data step code that doesn't properly account for the missing values and produces the wrong answer ... in other words, it is more risky.

Re: How to calculate average by variable and with conditions

Reeza — Fri, 30 Aug 2019 18:57:29 GMT

I've actually caught this mistake a few times across code reviews - which is why we also introduced a coding standard similar to what Paige has recommeded.

We also prioritize programming time over run time. Run time doesn't cost me money, programming time does. You need to optimize the right metrics, which varies from situation to situation.

Re: How to calculate average by variable and with conditions

hashman — Fri, 30 Aug 2019 23:32:29 GMT

@Reeza;

>We also prioritize programming time over run time. Run time doesn't cost me money, programming time does.<

Hope you realize that priorities like these depend on the nature of "we", that of the task, specific platform, data volume, how much time you have to run your programs, whether they can be run repeatedly based on the latter, etc. Run time may not cost you money. However, elsewhere it not only can cost a lot of money but makes the very difference between a job having been done on time and not done at all. An example:

Suppose that a programmer was tasked with piecing together an ETL process that starts at 21:00 and must run overnight, as the analysts enterprise-wide absolutely must have their updated data by 7:00. Suppose further that he spent a remarkably short time to program it correctly from the standpoint of the output data, except it has a few wrinkles, such as any of the following:

takes 15 hours to run
bombs due to insufficient resources after running for 8 hours
gobbles up so much system resources that it causes other critical processes running in parallel to abend
other side effects, too numerous to mention here

Would the client for whom this kind of work is done will be elated to hear from this programmer that he has saved them money by prioritizing programming time over run time? I suppose it's a rhetorical question...

Kind regards

Paul D.

Re: How to calculate average by variable and with conditions

Reeza — Tue, 03 Sep 2019 02:47:20 GMT

@hashman of course, that's why I said 'we' and didn't generalize that statement. But I do have control over my team and can make those decisions accordingly. I'm in the camp of developer time is more important. IME, if you have more time, you also have time to improve your skill set and keep up with what the best methods are so you don't often run into time issues anyways because you're aware of the most efficient methods to develop solutions. Everything is relative.