Rob graciously helped me out a few weeks ago solving my efforts to infer initial balances from interest rate data. After working with the data a bit, I have two questions.
1. How do I add a constraint that the EodBalance is always non-negative? I tried adding it (noneng1) but in this particular case I get negative EodBalance values on days for count =42-46 regardless of whether my constraint is commented out or not. Clearly, I'm doing something wrong.
2. In this particular case, there is not a "solution." Total Interest is 0.000072374 while total_interest is 0.000050858. That's the best solution but the difference between total_interest and Total Interest is 0.000021516
(which is also reported as the "objective value"), i.e., the inferred interest is about 42% greater than the actual value. Why does proc optmodel view this "solution" as best rather than not a solution? I must be misunderstanding what the opttol value means. Any insights would be appreciated.
Thanks again,
Rick
options nocenter;
proc datasets kill;
data a1; input id $ count date :yymmdd10. deposit; /*this is investor id, a count variable (that goes from 1-90 for each investor), and dates of investors' deposits (positive value) or withdrawals (negative values*/
format date yymmdd10.;
datalines;
A 1 2022-04-14 0.00
A 2 2022-04-15 0.00
A 3 2022-04-16 0.00
A 4 2022-04-17 0.00
A 5 2022-04-18 0.00
A 6 2022-04-19 0.00
A 7 2022-04-20 0.00
A 8 2022-04-21 0.00
A 9 2022-04-22 0.00
A 10 2022-04-23 0.00
A 11 2022-04-24 0.00
A 12 2022-04-25 0.00
A 13 2022-04-26 0.00
A 14 2022-04-27 0.00
A 15 2022-04-28 0.00
A 16 2022-04-29 0.00
A 17 2022-04-30 0.00
A 18 2022-05-01 0.00
A 19 2022-05-02 0.00
A 20 2022-05-03 0.00
A 21 2022-05-04 0.00
A 22 2022-05-05 0.00
A 23 2022-05-06 0.00
A 24 2022-05-07 0.00
A 25 2022-05-08 0.00
A 26 2022-05-09 0.00
A 27 2022-05-10 0.00
A 28 2022-05-11 0.00
A 29 2022-05-12 0.00
A 30 2022-05-13 0.00
A 31 2022-05-14 0.00
A 32 2022-05-15 0.00
A 33 2022-05-16 0.00
A 34 2022-05-17 0.00
A 35 2022-05-18 0.00
A 36 2022-05-19 0.00
A 37 2022-05-20 0.00
A 38 2022-05-21 0.00
A 39 2022-05-22 0.00
A 40 2022-05-23 0.00
A 41 2022-05-24 0.00
A 42 2022-05-25 -0.01126
A 43 2022-05-26 0.00
A 44 2022-05-27 0.00
A 45 2022-05-28 0.00
A 46 2022-05-29 0.00
A 47 2022-05-30 0.00
A 48 2022-05-31 0.00
A 49 2022-06-01 0.00
A 50 2022-06-02 0.00
A 51 2022-06-03 0.00
A 52 2022-06-04 0.00
A 53 2022-06-05 0.00
A 54 2022-06-06 0.00
A 55 2022-06-07 0.00
A 56 2022-06-08 0.00
A 57 2022-06-09 0.00
A 58 2022-06-10 0.00
A 59 2022-06-11 0.00
A 60 2022-06-12 0.00
A 61 2022-06-13 0.00
A 62 2022-06-14 0.00
A 63 2022-06-15 0.00
A 64 2022-06-16 0.00
A 65 2022-06-17 0.00
A 66 2022-06-18 0.00
A 67 2022-06-19 0.00
A 68 2022-06-20 0.00
A 69 2022-06-21 0.00
A 70 2022-06-22 0.00
A 71 2022-06-23 0.00
A 72 2022-06-24 0.00
A 73 2022-06-25 0.00
A 74 2022-06-26 0.00
A 75 2022-06-27 0.00
A 76 2022-06-28 0.00
A 77 2022-06-29 0.00
A 78 2022-06-30 0.00
A 79 2022-07-01 0.00
A 80 2022-07-02 0.00
A 81 2022-07-03 0.00
A 82 2022-07-04 0.00
A 83 2022-07-05 0.00
A 84 2022-07-06 0.00
A 85 2022-07-07 0.00
A 86 2022-07-08 0.00
A 87 2022-07-09 0.00
A 88 2022-07-10 0.00
A 89 2022-07-11 0.00
A 90 2022-07-12 0.00
A 91 2022-07-13 0.00
;
proc sort; by id;
run;
data a2; input id $ total_interest; /*this is investor id and the total interest they earned over the 90 days*/
datalines;
A 0.000050858
;
proc sort; by id;
data a4; merge a1 a2; by id;
proc sort; by id;
run;
proc optmodel;
set DAYS;
num deposit {DAYS} ;
num date {DAYS};
read data a1(where=(id='A')) into DAYS=[count] deposit date;
DAYS = {0} union DAYS;
num total_interest;
read data a2(where=(id='A')) into total_interest;
var EodBalance {DAYS} >= 0;
impvar Interest {t in DAYS diff {0}} =
if t <= 15 then (
if EodBalance[t-1] <= 30 then EodBalance[t-1]*(1.0535**(1/365)-1)
else 30*(1.0535**(1/365)-1) + (EodBalance[t-1]-30)*(1.0352**(1/365)-1)
)
else (
if EodBalance[t-1] <= 2 then EodBalance[t-1]*(1.06**(1/365)-1)
else if EodBalance[t-1] <= 25 then 2*(1.06**(1/365)-1) + (EodBalance[t-1]-2)*(1.04**(1/365)-1)
else 2*(1.06**(1/365)-1) + 23*(1.04**(1/365)-1) + (EodBalance[t-1]-25)*(1.0352**(1/365)-1)
)
;
var TotalInterest >= 0;
con TotalInterestCon:
TotalInterest = sum {t in DAYS diff {0}} Interest[t];
*min SumSquaredError = (TotalInterest - total_interest)^2;
min absdif=abs(TotalInterest - total_interest);
con BalanceCon {t in DAYS diff {0}}:
/*EodBalance[t] = EodBalance[t-1] + Interest[t] + deposit[t];*/
EodBalance[t] = EodBalance[t-1] + Interest[t] + deposit[t];
*con nonneg1 {t in DAYS}:
EodBalance[t] ge 0;
solve with nlp / opttol=1e-10 ;
print TotalInterest total_interest;
create data OutData from [count] EodBalance Interest deposit date totalInterest total_interest;
quit;
run;
data outdata1; set outdata;
options ps=120;
format date yymmddn8.;
proc print;run;
1. You have properly imposed a lower bound of 0 in the VAR statement. An alternative approach (not preferred) is to impose an explicit constraint, and the nonneg1 constraint you have commented out is also correct. When I run your code on my machine, the lower bounds are respected. The values of EodBalance starting at day 42 are tiny and written in scientific notation like 4.378375E-11, but this is still nonnegative. Do you see something different?
2. I get the following notes in the log:
NOTE: Optimal.
NOTE: Objective = 0.0000215596.
NOTE: Objective of the best feasible solution found = 0.0000215592.
NOTE: The best feasible solution found is returned.
NOTE: To return the local optimal solution found, set the SOLTYPE= option to 0.
The interpretation is that the solver terminated with a locally optimal solution with objective value 0.0000215596, but a slightly better solution with objective value 0.0000215592 was found along the way, and this better solution is returned with solution status Best Feasible. Both solutions satisfy all the bounds and constraints within tolerance and hence are considered feasible, and this determination does not depend on the objective. If you prefer the locally optimal solution, you can specify SOLTYPE=0 in the solver options. If you explicitly constrain TotalInterest = total_interest, the solution status will be Failed.
1. You have properly imposed a lower bound of 0 in the VAR statement. An alternative approach (not preferred) is to impose an explicit constraint, and the nonneg1 constraint you have commented out is also correct. When I run your code on my machine, the lower bounds are respected. The values of EodBalance starting at day 42 are tiny and written in scientific notation like 4.378375E-11, but this is still nonnegative. Do you see something different?
2. I get the following notes in the log:
NOTE: Optimal.
NOTE: Objective = 0.0000215596.
NOTE: Objective of the best feasible solution found = 0.0000215592.
NOTE: The best feasible solution found is returned.
NOTE: To return the local optimal solution found, set the SOLTYPE= option to 0.
The interpretation is that the solver terminated with a locally optimal solution with objective value 0.0000215596, but a slightly better solution with objective value 0.0000215592 was found along the way, and this better solution is returned with solution status Best Feasible. Both solutions satisfy all the bounds and constraints within tolerance and hence are considered feasible, and this determination does not depend on the objective. If you prefer the locally optimal solution, you can specify SOLTYPE=0 in the solver options. If you explicitly constrain TotalInterest = total_interest, the solution status will be Failed.
First, thanks again for all your help Rob!
I also get very tiny values from day 42 forward, but my EodBalance values are reported as slightly negative (-0.000000) for days 42-46 then non-negative (0.000000) for days 47-on. I didn't understand the lower bound on the Var statement so pointing that out helps a lot. I'm guessing the extremely tiny negative values are, essentially, a rounding error.
I'm still don't understand the opttol. That is, I interpreted the opttol as a tolerance constraint that said the absolute difference between Total Interest and total_interest (i.e., absdif) must be less than 1e-10. Any guidance?
The solver allows the variables to violate their lower and upper bounds slightly, up to the feasibility tolerance. So slightly negative values are possible even if you specify >= 0.
The optimality tolerance is more complicated than what you expected. Instead, it is a threshold for the norm of the optimality conditions. For more details, see SAS Help Center: Solver Termination Criterion
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.