Hi all,

I have data that looks like the following
Loan_ID Loan_date Lender_ID Lead_lender Borrower_ID Borrower_Profits Lender_Profits VAR1 VAR2
1 31/10/2008 231 1 1100 0.1 0.12
1 31/10/2008 234 0 1100 0.1 0.13
1 31/10/2008 235 0 1100 0.1 0.1
5 20/12/2009 150 0 3000 0.2 0.07
5 20/12/2009 234 0 3000 0.2 0.11
5 20/12/2009 231 1 3000 0.2 0.07
5 20/12/2009 400 0 3000 0.2 0.09
7 01/10/2010 120 0 4000 0.05 0.12
7 01/10/2010 125 0 4000 0.05 0.14
7 01/10/2010 240 1 4000 0.05 0.11
7 01/10/2010 234 0 4000 0.05 0.1
9 01/10/2011 231 1 1500 0.15 0.01
9 01/10/2011 234 0 1500 0.15 0.2
9 01/10/2011 250 0 1500 0.15 0.15
3 01/10/2012 231 1 2300 0.1 0.13 0.175 0.136
3 01/10/2012 234 0 2300 0.1 0.01 0.175 0.136
3 01/10/2012 270 0 2300 0.1 0.09 0.175 0.136

This data represents loan data where multiple lenders (identified by Lender_ID) are giving loans to one borrower (identified by Borrower_ID). For example, where Loan_ID=1 there are 3 different lenders providing credit to one borrower. In each loan, there is one lead lender (i.e. main lender) identified by Lead_lender column and the remaining lenders are partners. Now I want to construct two variables as follows:

1. for each lead_lender, I need the average borrower_profits for all borrowers that the lead lender has been giving loans to over the past 3 years. For example, in the last loan (loan_id=3) the lead lender has an ID=231. This lender is also the lead lender in loan_id=9 and loan_id=5 in the past three years. So the average profits for his portfolio=(0.15+0.20) /2 = 0.175. See VAR1 column.

2. for each lead_lender, I need the average partner lender_profits for lenders that are most frequently working with the lead lender (say top 5 by frequency) over the past 3 years. For example, in the last loan (loan_id=3) the lead lender has an ID=231. This lender is frequently working with lender_id=234. So the average profits for his partners=(0.20+0.10+0.11) / 3 = 0.136. See VAR2 column.

Hope it is clear

Thank you for help

Gad