expanding dataset

[Mo Gad]

Dear all

I have a dataset as follows:

clear
input byte loan_id int lender_id byte key_lender int key_lender_id byte key_lender_nr
1 201 1 201 3
1 202 0   . 3
1 300 1 300 3
1 450 0   . 3
1 120 1 120 3
end

this is loan data where each loan involves multiple lenders. loan_id is loan identifier and lender_id is bank identifier. For each loan, there are one or more key banks. In this example, I have a total of 5 banks 3 of which are key banks. Now I want to expand this dataset with respect to the key-bank(s) (i.e. 3 times) as follows:

loan_id	lender_id	key_lender	key_lender_id	key_lender_nr	key_bank_id
1	201	1	201	3	201
1	202	0		3	201
1	300	1	300	3	201
1	450	0		3	201
1	120	1	120	3	201
1	201	1	201	3	300
1	202	0		3	300
1	300	1	300	3	300
1	450	0		3	300
1	120	1	120	3	300
1	201	1	201	3	120
1	202	0		3	120
1	300	1	300	3	120
1	450	0		3	120
1	120	1	120	3	120

I can expand using the following code but I am having difficulty in creating the last variable above "key_bank_id"

bysort loan_id : gen toexpand = _N
expand key_lender_nr if toexpand

Thank you for help

Gad

[Andrew Musau]

input byte loan_id int lender_id byte key_lender int key_lender_id byte key_lender_nr
1 201 1 201 3
1 202 0   . 3
1 300 1 300 3
1 450 0   . 3
1 120 1 120 3
end

qui sum if !missing(key_lender_id)
expand `=r(N)', gen(new)
bys loan_id lender_id: gen seq=_n
sort seq lender_id
by seq: gen k= sum(key_lender)
replace new = key_lender_id if lender_id== key_lender_id & seq==k
bys seq: egen key_bank_id=  max(new)
drop new seq k

Result:

. l

     +----------------------------------------------------------------+
     | loan_id   lender~d   key_l~er   key_le~d   key_l~nr   key_ba~d |
     |----------------------------------------------------------------|
  1. |       1        120          1        120          3        120 |
  2. |       1        201          1        201          3        120 |
  3. |       1        202          0          .          3        120 |
  4. |       1        300          1        300          3        120 |
  5. |       1        450          0          .          3        120 |
     |----------------------------------------------------------------|
  6. |       1        120          1        120          3        201 |
  7. |       1        201          1        201          3        201 |
  8. |       1        202          0          .          3        201 |
  9. |       1        300          1        300          3        201 |
 10. |       1        450          0          .          3        201 |
     |----------------------------------------------------------------|
 11. |       1        120          1        120          3        300 |
 12. |       1        201          1        201          3        300 |
 13. |       1        202          0          .          3        300 |
 14. |       1        300          1        300          3        300 |
 15. |       1        450          0          .          3        300 |
     +----------------------------------------------------------------

[Mo Gad]

Thank you very much Andrew Musau !