IML to replace greedy algorithm for inventory optimization

[AramF]

I don't think I made myself clear.  Let me try to do a better job. BTW, that you so much for your generosity in working this.

In my work I could have for SKU A 100 different stocks (10 thru 110), For SKU B 15 different stocks (1 thrugh 15) and SKU C 10 different ones (32-42).  The reason for it, is that I have Min stock and Max Stock values that are determined by business rules outside of optimization.

To use the code that you offered,  would I need SKU A, B and C each have 100 different stocks instead of 100 for A, 14 for B and 10 for C?

[Ksharp]

---

@AramF wrote:

I don't think I made myself clear.  Let me try to do a better job. BTW, that you so much for your generosity in working this.

In my work I could have for SKU A 100 different stocks (10 thru 110), For SKU B 15 different stocks (1 thrugh 15) and SKU C 10 different ones (32-42).  The reason for it, is that I have Min stock and Max Stock values that are determined by business rules outside of optimization.

To use the code that you offered,  would I need SKU A, B and C each have 100 different stocks instead of 100 for A, 14 for B and 10 for C?

---

Sure. Of course you can. Just change the constraint condition in code:

data have;
infile cards expandtabs truncover;
input sku $ stock	Cost	Contribution;
cards;
A	1	10	1
A	2	20	1.9
A	3	30	2.8
A	4	40	3.75
A	5	50	4.7
B	1	20	1.7
B	2	40	2.6
B	3	60	3.4
B	4	80	4.0
B	5	100	5.1
C	6	210	3.0
C	7	245	4.2
C	8	280	4.8
C	9	315	6.3
C	10	350	6.7
;

data have2;
 set have(rename=(stock=old_stock));
 by sku;
 if first.sku then stock=0;
 stock+1;
run;
proc sql;
create table have3 as
select a.*,coalesce(b.Cost,0) as cost,coalesce(b.Contribution,0) as Contribution
 from (select * from (select distinct sku from have2),(select distinct stock from have2)) as a
 natural left join
 have2 as b ;
quit;

proc sort data=have3 out=sku nodupkey;by sku;run;
proc sort data=have3 out=stock nodupkey;by stock;run;
proc optmodel;
set<str> sku;
set stock;
num cost{sku, stock};
num contribution{sku, stock};

read data sku into sku=[sku];
read data stock into stock=[stock];
read data have3 into [sku stock] cost contribution;

var v{sku, stock} binary;

min obj=sum{i in sku,j in stock} cost[i,j]*v[i,j];
con con1 :11<=sum{i in sku,j in stock} contribution[i,j]*v[i,j]<=14  ;
con con2{m in sku}:sum{n in stock} v[m,n]=2;  /*each SKU have two stock*/  /*  >=1  means at least one stock from each SKU*/
solve with clp / findallsolns;
create data want from [solution sku2 stock2]={s in 1.._NSOL_,i in sku,j in stock} 
                      cost2=cost[i,j] contribution2=contribution[i,j] flag=v[i,j].sol[s];
quit;
proc sort data=want;by sku2 stock2;run;
data want2;
 length sku2 $ 100;
 merge want have2(keep=sku stock  old_stock rename=(sku=sku2 stock=stock2));
 by sku2 stock2;
run;
proc sort data=want2;by solution sku2 stock2;run;
proc print data=want2 noobs;
run;

Base on your orginal data, you can not set solution, you need to change this sample data . 

I have to leave now!

[AramF]

I just realized that my reply had wrong counts.  Let me redo the  numbers:

In my work I could have for SKU A 100 different stocks (10 thru 109), For SKU B 15 different stocks (1 thru 15) and SKU C 10 different ones (32-41).  The reason for it, is that I have Min stock and Max Stock values that are determined by business rules outside of optimization.

To use the code that you offered,  would I need SKU A, B and C each have 100 different stocks instead of 100 for A, 15 for B and 10 for C?

[AramF]

con con2{m in sku}:sum{n in stock} v[m,n]=2;  /*each SKU have two stock*/  /*  >=1  means at least one stock from each SKU*/

I was afraid that this is what you understood.  I did not mean that.

What I am asking is can my starting dataset be Table A instead of original Table B.  In Table A there is an unequal number of stocking possibilities in each SKU.  I still want only 1 outcome to be used in the solution per SKU, but for SKU A it is 1 out 2, for SKU B it is 1 out 4 and for SKU C it is 1 out 5?  I will never want to get 2 stock possibilities out of a particular SKU.

Table A					Table B
SKU	Stock	Cost	Contribution		SKU	Stock	Cost	Contribution
A	1	10	1		A	1	10	1
A	2	20	1.9		A	2	20	1.9
B	1	20	1.7		A	3	30	2.8	removed
B	2	40	2.6		A	4	40	3.75	removed
B	3	60	3.4		A	5	50	4.7	removed
B	4	80	4		B	1	20	1.7
C	6	210	3		B	2	40	2.6
C	7	245	4.2		B	3	60	3.4
C	8	280	4.8		B	4	80	4
C	9	315	6.3		B	5	100	5.1	removed
C	10	350	6.7		C	6	210	3
					C	7	245	4.2
					C	8	280	4.8
					C	9	315	6.3
					C	10	350	6.7

[ChanceTGardener · Accepted solution]

This model will accommodate an unequal number of possibilities (stocks) per SKU. Additionally, the stocks can, but are not required to increment by 1. See SKU C as an example in the input data below.

The output data set shows the optimal number of each SKU to stock.

Feel free to change the input data to accommodate the different scenarios you may face to confirm it performs well across all of them. 

data input_data;
 input sku $ stock cost contribution;
datalines;
A 1 10 1
A 2 20 1.9
A 3 30 2.8
A 4 40 3.75
A 5 50 4.7
B 1 20 1.7
B 2 40 2.6
B 3 60 3.4
B 4 80 4.0
B 5 100 5.1
C 1 210 3.0
C 17 245 4.2
C 18 280 4.8
C 30 315 6.3
C 100 350 6.7
;

proc optmodel;

set <str,num> ARCS;

set SKUS = setof{<i,j> in ARCS} i;
set STOCK = setof{<i,j> in ARCS} j;

num cost{ARCS};
num contribution{ARCS};

read data input_data into ARCS=[sku stock] cost contribution;

var Assign{ARCS} binary;

impvar TotalContribution = sum{<i,j> in ARCS} Assign[i,j] * contribution[i,j];

min TotalCost = sum{<i,j> in ARCS} Assign[i,j]*cost[i,j];

con contribuion_lb: 11 <= TotalContribution <= 14;

con sku_minimum{i in SKUS}: sum{j in STOCK: <i,j> in ARCS} Assign[i,j] = 1;

solve;

print TotalContribution;
print TotalCost.sol;

create data output_data from [sku stock] = {<i,j> in ARCS: Assign[i,j] > 0.5};

quit;

[Ksharp]

Sorry. The code I posted before is NOT right.

Try the following code for unbalance data.

1)  one stock for each SKU:

data have;
infile cards expandtabs truncover;
input sku $ stock	Cost	Contribution;
cards;
A	1	10	1
A	2	20	1.9
A	3	30	2.8
A	4	40	3.75
A	5	50	4.7
B	1	20	1.7
B	2	40	2.6
B	3	60	3.4
B	4	80	4.0
B	5	100	5.1
C	6	210	3.0
C	7	245	4.2
C	8	280	4.8
C	9	315	6.3
C	10	350	6.7
;


proc sort data=have;by sku;run;
data have;
 set have;
 obs+1;
run;
data idx;
 set have;
 by sku;
 retain start end;
 if first.sku then start=obs;
 if last.sku then do;end=obs;output;end;
 keep start end;
run;
data idx;
 set idx;
 n+1;
run;
filename cond temp;
data _null_;
set idx;
file cond;
put 'con con'  n ':sum{c'  n ' in ' start '..' end '} v[c' n ']=1;' ;
run;
proc optmodel;
set idx;
num cost{idx};
num contribution{idx};
read data have into idx=[obs] cost contribution;

var v{idx} binary;

min obj=sum{i in idx} cost[i]*v[i];

con con :11<=sum{i in idx} contribution[i]*v[i]<=14  ;
%include cond;

solve with clp / findallsolns;

create data want from [solution obs]={s in 1.._NSOL_,i in idx} 
                      cost2=cost[i] contribution2=contribution[i] flag=v[i].sol[s];
quit;
proc sql;
create table want2 as 
select a.*,b.sku,b.stock
 from want as a,have as b
  where a.obs=b.obs
   order by 1,2 ;
quit;
proc print noobs;run;

2) at least one stock for each SKU:

data have;
infile cards expandtabs truncover;
input sku $ stock	Cost	Contribution;
cards;
A	1	10	1
A	2	20	1.9
A	3	30	2.8
A	4	40	3.75
A	5	50	4.7
B	1	20	1.7
B	2	40	2.6
B	3	60	3.4
B	4	80	4.0
B	5	100	5.1
C	6	210	3.0
C	7	245	4.2
C	8	280	4.8
C	9	315	6.3
C	10	350	6.7
;


proc sort data=have;by sku;run;
data have;
 set have;
 obs+1;
run;
data idx;
 set have;
 by sku;
 retain start end;
 if first.sku then start=obs;
 if last.sku then do;end=obs;output;end;
 keep start end;
run;
data idx;
 set idx;
 n+1;
run;
filename cond temp;
data _null_;
set idx;
file cond;
put 'con con'  n ':sum{c'  n ' in ' start '..' end '} v[c' n ']>=1;' ;
run;
proc optmodel;
set idx;
num cost{idx};
num contribution{idx};
read data have into idx=[obs] cost contribution;

var v{idx} binary;

min obj=sum{i in idx} cost[i]*v[i];

con con :11<=sum{i in idx} contribution[i]*v[i]<=14  ;
%include cond;

solve with clp / findallsolns;

create data want from [solution obs]={s in 1.._NSOL_,i in idx} 
                      cost2=cost[i] contribution2=contribution[i] flag=v[i].sol[s];
quit;
proc sql;
create table want2 as 
select a.*,b.sku,b.stock
 from want as a,have as b
  where a.obs=b.obs
   order by 1,2 ;
quit;
proc print noobs;run;

You yield THREE solution.

3) the TWO stocks for each SKU:

data have;
infile cards expandtabs truncover;
input sku $ stock	Cost	Contribution;
cards;
A	1	10	1
A	2	20	1.9
A	3	30	2.8
A	4	40	3.75
A	5	50	4.7
B	1	20	1.7
B	2	40	2.6
B	3	60	3.4
B	4	80	4.0
B	5	100	5.1
C	6	210	3.0
C	7	245	4.2
C	8	280	4.8
C	9	315	6.3
C	10	350	6.7
;


proc sort data=have;by sku;run;
data have;
 set have;
 obs+1;
run;
data idx;
 set have;
 by sku;
 retain start end;
 if first.sku then start=obs;
 if last.sku then do;end=obs;output;end;
 keep start end;
run;
data idx;
 set idx;
 n+1;
run;
filename cond temp;
data _null_;
set idx;
file cond;
put 'con con'  n ':sum{c'  n ' in ' start '..' end '} v[c' n ']=2;' ;
run;
proc optmodel;
set idx;
num cost{idx};
num contribution{idx};
read data have into idx=[obs] cost contribution;

var v{idx} binary;

min obj=sum{i in idx} cost[i]*v[i];

con con :11<=sum{i in idx} contribution[i]*v[i]<=14  ;
%include cond;

solve with clp / findallsolns;

create data want from [solution obs]={s in 1.._NSOL_,i in idx} 
                      cost2=cost[i] contribution2=contribution[i] flag=v[i].sol[s];
quit;
proc sql;
create table want2 as 
select a.*,b.sku,b.stock
 from want as a,have as b
  where a.obs=b.obs
   order by 1,2 ;
quit;
proc print noobs;run;

You yield none solution. Maybe you use other dataset to run the code.

[AramF]

Ksharp and ChaseTGardner,

Thank you both so much for putting together multiple iterations of code to help me solve this.  I've been sitting on this untapped potential that you have both made available for me overnight.  2 thumbs and 2 toes up for your awesome effort.  I think I can tinker with the code from here.  Although I did not initially request it, I like Ksharp's idea that maybe I do want to see multiple combinations of stocksXSKU if they all yield the same contribution and cost.  I am very grateful for your help.