BookmarkSubscribeRSS Feed
cosimodc
Calcite | Level 5

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?

 

 

 

 

5 REPLIES 5
cosimodc
Calcite | Level 5

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;

ballardw
Super User

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)

cosimodc
Calcite | Level 5

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;

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 792 views
  • 0 likes
  • 3 in conversation