Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-20-2023 01:54 PM
(1052 views)

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 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Rob!

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 25. Read more here about **why** you should contribute and **what is in it** for you!

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.