Programming strategy to mark the lenders

[aj_goodnews]

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

[ChrisNZ]

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.

[aj_goodnews]

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

[ChrisNZ]

> 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

[s_lassen · Accepted solution]

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.

[aj_goodnews]

Thank you so much, it work out perfectly!