Proc Loan output help

JT99 — Tue, 18 Apr 2017 00:31:54 GMT

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.

Re: Proc Loan output help

ChrisBrooks — Tue, 18 Apr 2017 09:55:44 GMT

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

Proc Loan doesn't accept an input file - everything must be hard-coded into it's call; and
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:

Amen the macro so that it generates an OUT= statement with a filename containing the Accountnum value; and
Use Proc Append to stick all the output files together; and
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!

topic Re: Proc Loan output help in SAS Programming

Proc Loan output help

Re: Proc Loan output help