## Proc Loan output help

Occasional Contributor
Posts: 15

# Proc Loan output help

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.

Valued Guide
Posts: 596

## Re: Proc Loan output help

[ Edited ]

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!

Discussion stats