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.
Click image to register for webinar
