☑ This topic is solved.
Need further help from the community? Please
sign in and ask a new question.
1 ACCEPTED SOLUTION
Accepted Solutions
It looks like you just need a simple mixed integer linear programming (MILP) problems.
Since you have so many variables and so many constraint conditions, the only choice is using SAS/OR, if you do not have its license ,you could try free OR by SAS OnDemand for Academic :
Anyway, here is an example:
proc optmodel;
set idx={1..15};
num cost{idx}=[10 20 30 40 50 20 40 60 80 100 210 245 280 315 350];
num contribution{idx}=[1 1.9 2.8 3.75 4.7 1.7 2.6 3.4 4.0 5.1 3.0 4.2 4.8 6.3 6.7];
var v{idx} binary;
min obj=sum{i in idx} cost[i]*v[i];
con con :11<=(sum{a in 1..15} contribution[a]*v[a])<=14 ;
con con1:sum{a1 in 1..5} v[a1]=1;
con con2:sum{a2 in 6..10} v[a2]=1;
con con3:sum{a3 in 11..15} v[a3]=1;
solve with clp / findallsolns;
print obj Cost Contribution v;
quit;
If you want input thing is a dataset/table , not want type it by hand, try the following code:
NOTE: I changed the index of stock(from 1 to 5) in SKU=C to make the code easy to read, you can easily change the index by a data step.
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 1 210 3.0
C 2 245 4.2
C 3 280 4.8
C 4 315 6.3
C 5 350 6.7
;
proc sort data=have out=sku nodupkey;by sku;run;
proc sort data=have 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 have 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]=1;
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 print data=want noobs;
run;
@AramF wrote:
When I say dissimilar stock, I mean A could have 2 stocks, B 4 stocks and C 5 stocks.
Stock Cost Contribution A 1 10 1 A 2 20 1.9 B 1 20 1.7 B 2 40 2.6 B 3 60 3.4 B 4 80 4 C 6 210 3 C 7 245 4.2 C 8 280 4.8 C 9 315 6.3 C 10 350 6.7
So the number of stock from each SKU is varying ?
Just change the constraint condition and could yield THREE solutions:
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]>=1;
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;
OK. Here is .
I make a new index of stock(1 2 3 4 ....) , you could merge old index of stock back by one more data step (as I showed).
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]=1;
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;
data want2;
length sku2 $ 100;
merge want have2(keep=sku stock old_stock rename=(sku=sku2 stock=stock2));
by sku2 stock2;
run;
proc print data=want2 noobs;
run;
Multiple Linear Regression in SAS
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.
-
1347 views
-
4 likes
-
-
