BookmarkSubscribeRSS Feed
Obsidian | Level 7



I am trying to solve a problem with proc optmodel. The objective is failrly simple. I need to maximize selected units so that the profit is maximized.


The table consists of many variables, however the combination of Unique_Name and Week makes it unique, i.e. they are the key values.

I have 3 tables that I am using in  here:

1. Week : This consists of unique values of week (Day_of_Week+Time).

2. Rate: this consists of Unique_Title as '1' variable and Week (Multiple values) as other variables. I have created this table by transposing data to create a matrix structure.

3. D_Max_Value :Its similar to the table above except it has maximum values for constraints. The only issue with this table is that the maximum values are repeated across all Week columns, therefore I am taking MEan.


proc transpose data=subset_Table out= Rate prefix=ST_;

	by  Unique_Name;
	id Air_Dt_Time;
	VAR Pitched_rate;

data Rate(drop=_Name_);
	set Rate;

proc transpose data=subset_Table out=D_MaxVal prefix=ST_;

	by Unique_Name;
	id Air_Dt_Time;
	VAR Maximumvalue;

data D_MaxVal(drop=_Name_);
	set D_MaxVal;

proc sql;
create table week as
select  name as week
from dictionary.columns
where memname = 'RATE' and name like 'ST_%';


proc optmodel;
   /* declare variables */
	set <str> Unique_Title;
	set <str> Week;

	num Rate {Unique_Title, Week} init 0;
	num D_MaxVal {Unique_Title, Week} init 0;

	read data Week into Week = [week];
	read data Rate
			into Unique_Title = [Unique_Name]
			{j in Week} <Rate[Unique_Name,j]=col(j)>; 
	read data D_MaxVal 
			into Unique_Title = [Unique_Name]
			{j in Week} <D_MaxVal[Unique_Name,j]=col(j)>; 

	var Selected_Units {Unique_Title, Week} integer >= 0 ;
   /* maximize objective function (Budget) */
   maximize Budget = sum{i in Unique_Title, j in Week} Rate [i,j]* selected_units [i,j];

   /* subject to constraints */
   constraint Max_Budget{i in Unique_Title, j in Week}:
         sum(Rate [i,j]* selected_units [i,j]) <= mean(sum(D_MaxVal [i,j]));

   solve with milp ;
/* display solution */ print selected_units; quit;

The issue is that I am gettting multiple errors as below and I cannot comprehend it. Please let me know where I am going wrong.


'ERROR: The value for 'Max_Budget['Secret_Life_of_Pets',ST_02NOV1607_17_16_00].UB' may not be missing.'





The error message indicates that the upper bound of the constraint is missing.  You can use the EXPAND statement to see the constraint that results from your declaration:

expand Max_Budget;

Also, it might be simpler to read the data into PROC OPTMODEL directly from subset_Table instead of calling PROC TRANSPOSE and PROC SQL.



Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.


Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 


Register now!

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.

Discussion stats
  • 1 reply
  • 2 in conversation