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?
How do you build the string?
(as I see no setting of character variables in your code)
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;
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)
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;
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.
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 use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: