DATA Step, Macro, Functions and more

Proc Loan output help

Reply
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.

Super Contributor
Posts: 440

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!

Ask a Question
Discussion stats
  • 1 reply
  • 112 views
  • 0 likes
  • 2 in conversation