I want to assign on people to work on the stores based on the stores revenue. The revenue will be equaly or almost equally distributed between 3 managers.
Partner Name | Area Revenue |
Store 1 | $ 268,144.38 |
Store 2 | $ 257,660.73 |
Store 3 | $ 250,675.20 |
Store 4 | $ 174,474.83 |
Store 5 | $ 168,140.60 |
Store 6 | $ 145,631.76 |
Store 7 | $ 138,761.64 |
Store 8 | $ 135,393.91 |
Store 9 | $ 114,173.34 |
Store 10 | $ 109,770.67 |
Store 11 | $ 106,283.21 |
Store 12 | $ 40,844.72 |
Store 13 | $ 37,127.90 |
Store 14 | $ 22,112.36 |
Store 15 | $ 16,358.29 |
Store 16 | $ 7,984.21 |
sum | $ 1,993,537.75 |
sum/3 | $ 664,512.58 |
If I understand, you want to assign each store to one of three manager in such a way that they manage about the same revenue. A decent heuristic to do this is to assign each store to the manager with the least revenue, in decreasing order of revenue. For example:
data have;
input store $ revenue;
datalines;
Store1 268144.38
Store2 257660.73
Store3 250675.20
Store4 174474.83
Store5 168140.60
Store6 145631.76
Store7 138761.64
Store8 135393.91
Store9 114173.34
Store10 109770.67
Store11 106283.21
Store12 40844.72
Store13 37127.90
Store14 22112.36
Store15 16358.29
Store16 7984.21
;
proc sort data=have; by descending revenue; run;
data want;
array m manager1-manager3 (3*0);
set have;
manager = whichn(min(of m{*}), of m{*});
m{manager} = m{manager} + revenue;
*keep store manager;
run;
proc print data=want noobs; run;
I'm not sure what the question is, and I'm not sure which part you are having trouble with in SAS?
If I understand, you want to assign each store to one of three manager in such a way that they manage about the same revenue. A decent heuristic to do this is to assign each store to the manager with the least revenue, in decreasing order of revenue. For example:
data have;
input store $ revenue;
datalines;
Store1 268144.38
Store2 257660.73
Store3 250675.20
Store4 174474.83
Store5 168140.60
Store6 145631.76
Store7 138761.64
Store8 135393.91
Store9 114173.34
Store10 109770.67
Store11 106283.21
Store12 40844.72
Store13 37127.90
Store14 22112.36
Store15 16358.29
Store16 7984.21
;
proc sort data=have; by descending revenue; run;
data want;
array m manager1-manager3 (3*0);
set have;
manager = whichn(min(of m{*}), of m{*});
m{manager} = m{manager} + revenue;
*keep store manager;
run;
proc print data=want noobs; run;
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.