Request assistance to roll up loans

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Request assistance to roll up loans

Hi guys,

I have a question that pertains to aggregating (grouping) of loans with the end goal of having the principal balances on unique loans rolled up (summed). Let's consider that I have a portfolio of 4 different customers (CUSTOMER_NBR) and with that have a total of 10 loans (LOAN_NBR) each with different principal balances (PRINCIPAL_BAL).

CUSTOMER_NBRLOAN_NBRPRINCIPAL_BAL
1000000010253971991,000
1000000010253971992,000
1000000010253971993,000
2599177979385000012,000
2599177979385000012,000
25991779793850000110,000
2599177979385000011,000
100728800154101190500
1007288001541011901,000
1007288001541011907,000
2007110001465000196,600

As indicated in the above table there are 4 different customer numbers, each having a loan number and corresponding principal balance.

What I am attempting to do is roll up this data to have a single loan number for each that sums the entire principal balance for all loans of the same loan number.

i.e., I would like to group by CUSTOMER_NBR and LOAN_NBR

The resulting output would have only 4 rows and would look like this:

CUSTOMER_NBRLOAN_NBRPRINCIPAL_BAL

1000000010

253971996,000
25991779793850000115,000
1007288001541011908,500
2007110001465000196,600

I would ideally like to accomplish this using SQL Procedures; however, if there is a much simpler way in Data Step that would be sufficient as well.

Thanks so much!


Accepted Solutions
Solution
‎06-03-2014 06:42 PM
Trusted Advisor
Posts: 1,231

Re: Request assistance to roll up loans

Hi fedakd,

This is great you have the solution. This is a normal practice on this forum to use "have" as a dataset name and this not being used as a function. Term "want" is used to get processed dataset. Please see the following code that is creating "have" dataset first based on your input and from there proc sql creating a dataset "want" that provides the your desired output.

Regards,

Naeem

data have;
input customer_nbr loan_nbr principal_bal comma6.;
format principal_bal comma6.;
datalines;
1000000010 25397199 1,000
1000000010 25397199 2,000
1000000010 25397199 3,000
2599177979 38500001 2,000
2599177979 38500001 2,000
2599177979 38500001 10,000
2599177979 38500001 1,000
1007288001 54101190 500
1007288001 54101190 1,000
1007288001 54101190 7,000
2007110001 46500019 6,600
;

proc sql;
create table want as
select loan_nbr,sum(principal_bal) as principal_bal format=comma6.
from have
group by loan_nbr;
quit;

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Request assistance to roll up loans

Have you tried

Proc SQL ;

     Create table want as

          Select      CUSTOMER_NBR

                    ,          LOAN_NBR

                    ,          Sum(PRINCIPAL_BAL)

          From          have

          Group by   1, 2

          ;

Quit ;

This will sum at customer and account level.  However, if you want just the customer total (where there is more than one loan) leave out the LOAN_NBR and Group by CUSTOMER_NBR only.

There is a complication you may strike, where a loan is 'owned' by more than one customer, and depending on how your data is structured the total sum of grouped data exceeds the overall sum.  You will have to have some business rules to apportion partial balances to each customer.

Richard.

New Contributor
Posts: 2

Re: Request assistance to roll up loans

Posted in reply to RichardinOz

Hi Richard,

This worked perfectly. I was completely unaware of the "have" function, and being able to group the loans in that fashion.  Cheers!

Solution
‎06-03-2014 06:42 PM
Trusted Advisor
Posts: 1,231

Re: Request assistance to roll up loans

Hi fedakd,

This is great you have the solution. This is a normal practice on this forum to use "have" as a dataset name and this not being used as a function. Term "want" is used to get processed dataset. Please see the following code that is creating "have" dataset first based on your input and from there proc sql creating a dataset "want" that provides the your desired output.

Regards,

Naeem

data have;
input customer_nbr loan_nbr principal_bal comma6.;
format principal_bal comma6.;
datalines;
1000000010 25397199 1,000
1000000010 25397199 2,000
1000000010 25397199 3,000
2599177979 38500001 2,000
2599177979 38500001 2,000
2599177979 38500001 10,000
2599177979 38500001 1,000
1007288001 54101190 500
1007288001 54101190 1,000
1007288001 54101190 7,000
2007110001 46500019 6,600
;

proc sql;
create table want as
select loan_nbr,sum(principal_bal) as principal_bal format=comma6.
from have
group by loan_nbr;
quit;

Super User
Posts: 19,878

Re: Request assistance to roll up loans

Proc Means/Summary/Univariate is also a good option.

I'd rarely suggest using a data step.

proc means data=have;

class CUSTOMER_NBR loan_NBR;

var principal_bal;

output out=loan_summary sum(principal_bal) = total_bal;

quit;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 204 views
  • 6 likes
  • 4 in conversation