## Problem Goal Seek(length variable/macrovariable)

Occasional Contributor
Posts: 8

# Problem Goal Seek(length variable/macrovariable)

[ Edited ]

Hi All,

i have an amortization plan(20 number rate,capital=100000,rate=2%)

CODE:

%let baloon=0;
%let capitale=100000;
%let tasso=2;
%let periodo_num=4;
%let per_tot=20;

data str;
do num_prog=1 to &per_tot;
output;
end;
run;

data loan(drop=_q_capitale cap_res_baloon baloon i rata interesse);
set str;
format q_cap_res q_cap_ammto q_capitale 10.2;
retain _q_capitale ;
retain cap_res_baloon 0;
if &baloon=0 then baloon=&capitale;
else baloon=&capitale-((&capitale*&baloon)/100);
i=&tasso/(&periodo_num*100);
if num_prog=1 then do;
q_capitale=&capitale;
rata=mort(baloon,.,i,&per_tot);
interesse=baloon*i;
q_cap_res=q_capitale-(rata-interesse);
q_cap_ammto=rata-interesse;
end;
else do;
q_capitale=_q_capitale;
rata=mort(baloon,.,i,&per_tot);
cap_res_baloon=q_capitale-(&baloon*&capitale/100);
if &baloon=0 then interesse=q_capitale*i;
else interesse=cap_res_baloon*i;
q_cap_res=q_capitale-(rata-interesse);
q_cap_ammto=q_capitale-q_cap_res;
end;
if num_prog=&per_tot and &baloon^=0 then do;
q_cap_ammto=(q_capitale-q_cap_res)+(&capitale*&baloon/100);
q_cap_res=0;
end;
_q_capitale=q_cap_res;
run;

I want sum(q_capitale) will 400000(changing tasso)

My approach is PROC MODEL

I have to build the formulas to calculate q_capitale(with tasso is my x) for use in proc model.

My problem is the formulas (q_capitale) goes beyond 32k;if i use macrovariable goes beyond 64k.

Any Suggestion?

Super User
Posts: 10,534

## Re: Problem Goal Seek(length variable/macrovariable)

How do you build the string?

(as I see no setting of character variables in your code)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 8

## Re: Problem Goal Seek(length variable/macrovariable)

[ Edited ]

Hi KurtBremser,

my code for building formulas are:

data loan(drop=_q_capitale cap_res_baloon baloon i rata interesse interesse_form rata_form i_form
cap_res_baloon_form q_capitale_form _q_capitale_form);
set str;
format q_cap_res q_cap_ammto q_capitale 10.2;
length q_capitale_form interesse_form q_cap_res_form q_cap_ammto_form
cap_res_baloon_form _q_capitale_form \$32000;
retain _q_capitale _q_capitale_form;
retain cap_res_baloon 0;
if &baloon=0 then baloon=&capitale;
else baloon=&capitale-((&capitale*&baloon)/100);
i=&tasso/(&periodo_num*100);
i_form=cats('x/',(&periodo_num*100));
if num_prog=1 then do;
q_capitale=&capitale;
q_capitale_form=compress(&capitale);
rata=mort(baloon,.,i,&per_tot);
rata_form=cats('(mort(',baloon,',.,',i_form,',',&per_tot,'))');
interesse=baloon*i;
interesse_form=cats('(',baloon,'*',i_form,')');
q_cap_res=q_capitale-(rata-interesse);
q_cap_res_form=cats('(',q_capitale_form,'-(',rata_form,'-',interesse_form,'))');
q_cap_ammto=rata-interesse;
q_cap_ammto_form=cats('(',rata_form,'-',interesse_form,')');
end;
else do;
q_capitale=_q_capitale;
q_capitale_form=_q_capitale_form;
rata=mort(baloon,.,i,&per_tot);
rata_form=cats('(mort(',baloon,',.,',i_form,',',&per_tot,'))');
cap_res_baloon=q_capitale-(&baloon*&capitale/100);
cap_res_baloon_form=cats('(',q_capitale_form,'-',&baloon*&capitale/100,')');
if &baloon=0 then do;
interesse=q_capitale*i;
interesse_form=cats('(',q_capitale_form,'*',i_form,')');
end;
else do;
interesse=cap_res_baloon*i;
interesse_form=cats('(',cap_res_baloon_form,'*',i_form,')');
end;
q_cap_res=q_capitale-(rata-interesse);
q_cap_res_form=cats('(',q_capitale_form,'-(',rata_form,'-',interesse_form,'))');
q_cap_ammto=q_capitale-q_cap_res;
q_cap_ammto_form=cats('(',rata_form,'-',interesse_form,')');
end;
if num_prog=&per_tot and &baloon^=0 then do;
q_cap_ammto=(q_capitale-q_cap_res)+(&capitale*&baloon/100);
q_cap_ammto_form=cats('(',q_capitale_form,'-',q_cap_res_form,')','+(',&capitale*&baloon/100);
q_cap_res=0;
q_cap_res_form="0";
end;
_q_capitale=q_cap_res;
_q_capitale_form=q_cap_res_form;
run;

Super User
Posts: 13,909

## Re: Problem Goal Seek(length variable/macrovariable)

Can you show the result of what you are building for a much smaller case that does work?

I am afraid that I do not see any "formula" being created either. Your set contains 4 numeric variables.

No formula.

No Proc Model code.

If you have access to SAS/ETS Proc Loan might help somewhat but I really don't understand what is intended by :

I want sum(q_capitale) will 400000(changing tasso)

Occasional Contributor
Posts: 8

## Re: Problem Goal Seek(length variable/macrovariable)

Hi ballardw,

in attach dataset(it's same loan(change name)).

Code for Proc MODEL:

proc sql noprint;
select q_cap_res_form
into :eq separated by ","
from amm_francese_fisso_11;
quit;

data goal;
target=400000;
run;

proc model data=goal;
endo x;
exo target;
target =sum(&eq);
solve / out=solution solveprint ;
run;

Super User
Posts: 10,534

## Re: Problem Goal Seek(length variable/macrovariable)

I have often found (in discussions here) that people tend to solve such interest calculations in an iterative way

``````do i = 1 to years;
amount = amount + amount * interest;
end;``````

which can be simplified to

``````do i = 1 to years;
amount = amount * (1 + interest);
end;``````

and finally to

``amount = amount * (1 + interest) ** years;``

which completely removes the need for any iteration.

I somehow have an inkling that your massive formula might be equally suited for simplification by applying algebraic rules to it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Discussion stats
• 5 replies
• 146 views
• 0 likes
• 3 in conversation