Need Help Allocating Best Promotion with Constraints

[rkepple]

Looking for best practice to allocate best available promotion to my customers.  Sample Data below:

data have;
input customer $ customer_rank promo_id promo_score;
datalines;
carl 1 4000 9
carl 1 4001 7
carl 1 4002 4
carl 1 4003 3
jess 2 4000 9
jess 2 4004 7
jess 2 4002 5
jess 2 4001 3
robb 3 4004 8
robb 3 4000 6
robb 3 4002 5
robb 3 4008 2
amii 4 4004 8
amii 4 4000 7
amii 4 4001 4
amii 4 4002 3
;
run;

I am looking for code that would assign each customer the best promotion (based on promo_score), BUT I can only assign the promo_id once (two customers cannot receive same promo_id) so in this example I would want carl (as he is my top customer) to get 4000, jess to get 4004 (since 4000 is gone), robb to get 4004 and amii to get 4001 (since her top 2 are gone).  Thank you so much!!

[ballardw]

Is that all the values involved? I think I have a possible solution in mind but it may not be very easy to deal with if there are dozens (or hundreds) of Promo_id promo_score combinations.

Rob can't get 4004 as you just gave it to Jess in your description.

This seems to work for the given sample, and how I noticed that you can't give 4004 to Rob.

data want;
   set have;
   array pick(4000:4008) _temporary_;
   retain custpicked;
   by customer_rank;
   if first.customer_rank then custpicked=0;
   if custpicked=0 then do;
      if pick[promo_id] ne 1 then do;
         SelectPromo=Promo_id;
         pick[promo_id] = 1;
         custpicked=1;
         output;
      end;
   end;
   if last.customer_rank and custpicked=0 then do
      SelectPromo=.;
      output;
   end;
run;

This uses the promo_id as index into an array. The _temporary_ array values are held across iterations of the data step so when one of them in the array is set to 1, marking it has been selected, that is kept unless reset.

The custpicked variable is used to see if a particular customer has a promo picked and is set to 1 when selected. You could drop the variable.

This uses by group processing based on the Rank of the customer (and assumes that the promo_id are in descending order of promo_score). So we can use first. and last. variables to do some things. When you use BY group in a data step SAS creates automatic variables for each variable on the by statement. The values are 1/0, or true/false to indicate that the particular record is the first or last of the by group. So we can reset the custpicked variable to not selected, 0, for the first of the customer rank score values.

If the custpicked we check to see if the particular promo_id has been previously selected (would have a value of 1) or not (missing). If the Promo_id has not been selected then we do some housekeeping, setting the custpicked value to1 (we are selecting one), setting the Pick array value to 1 so it has been used, setting a specific new variable to the selected promo id (there's a reason) and then writing to the output set.

IF you go through all of the promo_ids for a customer and one has not been picked the last record is output with a missing value for the SelectPromo variable. So you can tell that the customer does not have one selected. Your data didn't have an example of this but if you are providing X choices of promo_id and have X+1 or more customers someone will be left out with the one promo_id per customer rule.

[rkepple]

Thank you, the real world example is much more complex, but I can start with this and see if I can crack it.

[Ksharp]

data have;
input customer $ customer_rank promo_id promo_score;
datalines;
carl 1 4000 9
carl 1 4001 7
carl 1 4002 4
carl 1 4003 3
jess 2 4000 9
jess 2 4004 7
jess 2 4002 5
jess 2 4001 3
robb 3 4004 8
robb 3 4000 6
robb 3 4002 5
robb 3 4008 2
amii 4 4004 8
amii 4 4000 7
amii 4 4001 4
amii 4 4002 3
;
run;

proc sort data=have out=temp;
by customer_rank descending promo_score;
run;

data want;
if _n_=1 then do;
  if 0 then set temp;
  declare hash h();
  h.definekey('promo_id');
  h.definedone();
end;
set temp;
by customer_rank;
retain found 0;
if first.customer_rank then found=0;
if h.check() ne 0 and not found then do;h.add();found=1;output;end;
drop found;
run;

[rkepple]

Thank you @Ksharp 

this is great - I have one follow up, if i wanted to make the offer cap 2 (Instead of just 1) how would i make that edit?

[Ksharp]

data have;
input customer $ customer_rank promo_id promo_score;
datalines;
carl 1 4000 9
carl 1 4001 7
carl 1 4002 4
carl 1 4003 3
jess 2 4000 9
jess 2 4004 7
jess 2 4002 5
jess 2 4001 3
robb 3 4004 8
robb 3 4000 6
robb 3 4002 5
robb 3 4008 2
amii 4 4004 8
amii 4 4000 7
amii 4 4001 4
amii 4 4002 3
;
run;

proc sort data=have out=temp;
by customer_rank descending promo_score;
run;

data want;
if _n_=1 then do;
  if 0 then set temp;
  declare hash h();
  h.definekey('promo_id');
  h.definedone();
end;
set temp;
by customer_rank;
retain count 0;
if first.customer_rank then count=0;
if h.check() ne 0 and count<2 then do;h.add();count+1;output;end;
drop count;
run;

[rkepple]

@Ksharp thanks for the continued help

When i apply this code to my real dataset, the issue I am getting is it is only EACH ccid ONCE.  I would like every customer (many in my real data) to get an offer.   I would like to apply up to 5000 CCIDs across 100K+ customers, once i reach 5000th ccid assignment i want it to stop (and then assign the next best one).

Does this make sense? Thanks AGAIN, this is close!

[rkepple]

I feel like my response was unclear 

I would like to allocated a promo_id UP TOO 5,000x -- after it hits 5000 assignments, i CANNOT allocate it any longer.  The max Promo_ID a customer can get is 5.  Maybe that provides additional context

[Ksharp]

I think I am still confused,you have too many scenarios to take into account of .

Assuming I understood what you mean.

But I think you'd better post an input dataset to let me test the code ,and output dataset(important) to verify my code's result.

data have;
input customer $ customer_rank promo_id promo_score;
datalines;
carl 1 4000 9
carl 1 4001 7
carl 1 4002 4
carl 1 4003 3
jess 2 4000 9
jess 2 4004 7
jess 2 4002 5
jess 2 4001 3
robb 3 4004 8
robb 3 4000 6
robb 3 4002 5
robb 3 4008 2
amii 4 4004 8
amii 4 4000 7
amii 4 4001 4
amii 4 4002 3
xcarl 11 14000 9
xcarl 11 14001 7
xcarl 11 14002 4
xcarl 11 14003 3
xjess 21 14000 9
xjess 21 14004 7
xjess 21 14002 5
xjess 21 14001 3
xrobb 31 14004 8
xrobb 31 14000 6
xrobb 31 14002 5
xrobb 31 14008 2
xamii 41 14004 8
xamii 41 14000 7
xamii 41 14001 4
xamii 41 14002 3
;
run;



%let n_Promo_ID_each_customer=2;
%let n_Promo_ID_all_customer=10;

proc sort data=have out=temp;
by customer_rank descending promo_score;
run;

data want;
if _n_=1 then do;
  if 0 then set temp;
  declare hash h();
  h.definekey('promo_id');
  h.definedone();
end;
set temp;
by customer_rank;
retain count 0;
if first.customer_rank then count=0;
if h.num_items<&n_Promo_ID_all_customer. then do;
   if h.check() ne 0 and count<&n_Promo_ID_each_customer. then do;h.add();count+1;output;end;
end;
drop count;
run;