BookmarkSubscribeRSS Feed
JT99
Obsidian | Level 7

Hello! I have a task of calculating the monthly interest, principal, and balance of accounts that are in an installment payment plan. While searching the net, I discovered proc loan.

My data looks like this:

 

Acctnum Amort Balance Term(months) EffectiveRate
5296 9250.00 100000.00 12 19.71885
1356 4687.50 90000.00 24 22.41502
6976 6516.67 85000.00 15 21.60180
8610 6600.00 55000.00 9 18.81004
0966 2163.33 33000.00 18 21.64264

 

This is the code for the first account

 

proc loan;

fixed amount=100000 rate=19.71885 payment=9250 life=12 round=2

schedule=1;

run;

 

the output looks like this

 

Loan Repayment Schedule
 
Loan No. 1
Month Year Beginning
Outstanding
Payment Interest
Payment
Principal
Repayment
Ending
Outstanding
Dec 0 100000.00 0.00 0.00 0.00 100000.00
*** 0 100000.00 0.00 0.00 0.00 100000.00
Jan 1 100000.00 9250.00 1643.24 7606.76 92393.24
Feb 1 92393.24 9250.00 1518.24 7731.76 84661.48
Mar 1 84661.48 9250.00 1391.19 7858.81 76802.67
Apr 1 76802.67 9250.00 1262.05 7987.95 68814.72
May 1 68814.72 9250.00 1130.79 8119.21 60695.51
Jun 1 60695.51 9250.00 997.37 8252.63 52442.88
Jul 1 52442.88 9250.00 861.76 8388.24 44054.64
Aug 1 44054.64 9250.00 723.92 8526.08 35528.56
Sep 1 35528.56 9250.00 583.82 8666.18 26862.38
Oct 1 26862.38 9250.00 441.41 8808.59 18053.79
Nov 1 18053.79 9250.00 296.67 8953.33 9100.46
Dec 1 9100.46 9250.00 149.54 9100.46 0.00
*** 1 100000.00 111000.00 11000.00 100000.00 0.00

 

 

My question is, is there a way to have the output in one row only for each account that i can export as excel file? like all 12 interest/principal/balance in one row.

Acctnum Amort Balance Term(months) EffectiveRate interest1 interest2 interest3 principal1 principal2 principal3 outstandingbal1 outstandingbal2 outstandingbal3
5296 9250.00 100000.00 12 19.71885 1643.24 1518.24 1391.19 7606.76 7731.76 7858.81 92393.24 84661.48 76802.67

 

Any help or recommendation will be greatly appreciated! I have thousands of accounts so I'm looking for ways to do the task as fast as possible.

1 REPLY 1
ChrisBrooks
Ammonite | Level 13

Firstly I have to confess that I've never used Proc Loan but looking at it's documentation you have two extra problems:

 

  1. Proc Loan doesn't accept an input file - everything must be hard-coded into it's call; and
  2. You will have to use the OUT= statement to get it's output into a data set but that won't include the Accountnum variable you need

You can "sort of" get round the first problem by using a macro as in this SAS note http://support.sas.com/kb/38/168.html - you'll have to:

 

  1. Amen the macro so that it generates an OUT= statement with a filename containing the Accountnum value; and
  2. Use Proc Append to stick all the output files together; and
  3. Use Proc Transpose to get the format you want

Here's a link to the full syntax for Proc Loan https://support.sas.com/documentation/onlinedoc/ets/132/loan.pdf

 

This should be possible - good luck!

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
  • 1 reply
  • 859 views
  • 0 likes
  • 2 in conversation