topic Programming strategy to mark the lenders in SAS Programming

Programming strategy to mark the lenders

aj_goodnews — Wed, 18 Dec 2019 04:13:31 GMT

Hello all, I am working on some banking paper using the dataset contains information for each loan, and I need to identify the lender for each loan. The issue is complicated because, while each loan has only one borrower, loans can have multiple lenders due to syndication, in which case a group of banks and/or other financial institutions make a loan jointly to a borrower. My dataset reports the roles of participating lenders in each loan.

Common practice to identify the lender for each loan is that: If a lender is denoted as the “administrative agent,” it is defined as the lead bank. If no lender is denoted as the “administrative agent,” then the lender is defined as those who is denoted as the “agent,” “arranger,” “book-runner,” “lead arranger,” as the lender.

my dataset looks like this:

LoanID LenderID LenderRole

1 bank1 administrative agent

1 bank2 agent

1 bank3 adviser

1 bank4 arranger

2 bank5 coordinator

2 bank6 agent

2 bank7 coordinator

3 bank8 arranger

3 bank9 adviser

4 bank10 coordinator

4 bank11 book-runner

4 bank12 dealer

5 bank13 coordinator

5 bank14 lead arranger

What I want looks like this:

LoanID Lender LenderRole

1 bank1 administrative agent

2 bank6 agent

3 bank8 arranger

4 bank11 book-runner

5 bank14 lead arranger

Re: Programming strategy to mark the lenders

ChrisNZ — Wed, 18 Dec 2019 05:19:35 GMT

Like this?

data WANT;

merge HAVE (where=(ROLE='lead arranger '))

HAVE (where=(ROLE='book-runner '))

HAVE (where=(ROLE='arranger '))

HAVE (where=(ROLE='agent '))

HAVE (where=(ROLE='administrative agent'))

by LOAN_ID;

* if first.ROLE_ID;

run;

The last role read takes precedence.

Assuming each role only appears once, otherwise uncomment the last line to keep the first entry.

Re: Programming strategy to mark the lenders

s_lassen — Wed, 18 Dec 2019 09:49:18 GMT

You could create an informat to calculate the priority of the lender:

proc format; 
  invalue Priority (upcase) 
  'ADMINISTRATIVE AGENT'=1 
  'AGENT'=2 
  'ARRANGER'=3 
  'BOOK-RUNNER'=4 
  'LEAD ARRANGER'=5 
  other=999; 
run;


proc sql;
  create table want as select
  * from have 
  group by LoanID
  having input(LenderRole,Priority.)=min(input(LenderRole,Priority.));
quit;

I used the UPCASE option on the format, as your data looks like test data- your real data probably contains both loser and upper case.

Note that with the SQL solution shown, you may get two or more rows for each loan, if there is more than one lender with the top priority.

Re: Programming strategy to mark the lenders

aj_goodnews — Wed, 18 Dec 2019 13:26:23 GMT

Thank you Chris! May be I didn't explain my objective very well, but each LenderRole are not equally weighted in selecting the lender for each loan. For example, when two banks are assigned as 'administrative agent' and 'agent', respectively, only the bank assigned as 'administrative agent' will be selected as lender. When no 'administrative agent' role assigned in a loan, then other LenderRole are treated equally important and will be ranked by additional variables.

So yes, eventually each loan will have only one lender but such lender is not selected based on first observation in each loan with multiple banks with acceptable LenderRole

Re: Programming strategy to mark the lenders

aj_goodnews — Wed, 18 Dec 2019 15:24:22 GMT

Thank you so much, it work out perfectly!

Re: Programming strategy to mark the lenders

ChrisNZ — Thu, 19 Dec 2019 01:58:11 GMT

> when two banks are assigned as 'administrative agent' and 'agent', respectively, only the bank assigned as 'administrative agent' will be selected.

That's what my code does, as far as I understand your needs