## Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

For a simplified case, let's assume, I have following data:

 id cost profit_in_case1 profit_in_case2 A -1 10 11 B -0.6 10 5 C -0.75 10 10.5 D -0.75 10.5 10

And I have to maximize my profit with decisions,so if I just see the profits as individual rows, I'll have decisions like -

A - case2, B - case1, C - case2, D-case1.

But I have constraints like, I can only avail costs up to 1.5, so I can't choose A-case2 and B-case1 or A-case2 and C-case2(D-case1), instead I have to choose : C-case2 and D-case1. The final decision for maximum profit will look like:

 decision_Cost decision_profit_case1 decision_profit_case2 1 0 0 1 0 0 0 0 1 0 1 0

This way, we have minimized cost (Total = 1.33) and maximized profit (Total = 21) in the constraints that we can chose only one of the decisions to be 1 and total cost doesn't breach the boundary of 1.5.

My original problem has other constraints as well, but if I can get a solution to this one in PROC IML or PROC OPTMODEL, I think I can build from there. Any help is deeply appreciated. I am using SAS 9.4

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

I think the following does what you want, using PROC OPTMODEL in SAS/OR:

``````data have;
input id \$ cost profit_in_case1 profit_in_case2;
cost = -cost;
datalines;
A	-1	10	11
B	-0.6	10	5
C	-0.75	10	10.5
D	-0.75	10.5	10
;

proc optmodel;
set <str> ITEMS;
num cost {ITEMS};
set CASES = 1..2;
num profit {ITEMS, CASES};
read data have into ITEMS=[id] cost {j in CASES} <profit[id,j]=col('profit_in_case'||j)>;

var X {ITEMS, CASES} binary;
max TotalProfit = sum {i in ITEMS, j in CASES} profit[i,j] * X[i,j];
con ChooseOne {i in ITEMS}:
sum {j in CASES} X[i,j] <= 1;
con Budget:
sum {i in ITEMS, j in CASES} cost[i] * X[i,j] <= 1.5;

solve;
print X;
quit;``````

6 REPLIES 6

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

I think the following does what you want, using PROC OPTMODEL in SAS/OR:

``````data have;
input id \$ cost profit_in_case1 profit_in_case2;
cost = -cost;
datalines;
A	-1	10	11
B	-0.6	10	5
C	-0.75	10	10.5
D	-0.75	10.5	10
;

proc optmodel;
set <str> ITEMS;
num cost {ITEMS};
set CASES = 1..2;
num profit {ITEMS, CASES};
read data have into ITEMS=[id] cost {j in CASES} <profit[id,j]=col('profit_in_case'||j)>;

var X {ITEMS, CASES} binary;
max TotalProfit = sum {i in ITEMS, j in CASES} profit[i,j] * X[i,j];
con ChooseOne {i in ITEMS}:
sum {j in CASES} X[i,j] <= 1;
con Budget:
sum {i in ITEMS, j in CASES} cost[i] * X[i,j] <= 1.5;

solve;
print X;
quit;``````

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

Hi @RobPratt, thanks very much, this works perfectly. Many thanks.

Is there a source where I can read more about the features PROC OPTMODEL have with examples?

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

Hi @RobPratt any idea on how to output the final X table as it appears in PRINT statement. My efforts are resulting in one column for X with different cases in rows. I know I can transpose it, but I am asking if any direct way is there? Many thanks in adavnce.

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

Try this CREATE DATA statement:

``````create data want from [id]=ITEMS {j in CASES} <col('X'||j)=X[id,j]>;
``````

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

The official documentation is a good source:

http://support.sas.com/documentation/onlinedoc/or/

See especially these two:

• SAS/OR 15.1 User's Guide: Mathematical Programming
• SAS/OR 15.1 User's Guide: Mathematical Programming Examples

## Re: Constrained Optimization with constraints on Sum of Columns conditional on selection of Rows

Thank you very much. Works like a charm.😊
Discussion stats
• 6 replies
• 788 views
• 3 likes
• 2 in conversation