topic Re: Problem Goal Seek(length variable/macrovariable) in SAS Programming

Problem Goal Seek(length variable/macrovariable)

cosimodc — Wed, 16 May 2018 09:50:36 GMT

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?

Re: Problem Goal Seek(length variable/macrovariable)

Kurt_Bremser — Wed, 16 May 2018 07:57:41 GMT

How do you build the string?

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

Re: Problem Goal Seek(length variable/macrovariable)

cosimodc — Wed, 16 May 2018 09:51:50 GMT

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;

Re: Problem Goal Seek(length variable/macrovariable)

ballardw — Wed, 16 May 2018 15:18:43 GMT

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)

Re: Problem Goal Seek(length variable/macrovariable)

cosimodc — Wed, 16 May 2018 21:10:41 GMT

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;

Re: Problem Goal Seek(length variable/macrovariable)

Kurt_Bremser — Thu, 17 May 2018 07:09:57 GMT

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.