Hello everyone,

I would like to do time series for different combinations of variables. My data set looks like the following: I have a loan in a certain year with a certain borrower. Now, I need the loans made in the prior 5 years but considering M&A of the respective bank.

YEAR BORROWER BANK AMOUNT BANK_2017 BANK_2016 BANK_2015
2017 Company A BankA 100 BankA BankA.1 BankA.2
2016 Company A BankA.1 50 BankA BankA.1 BankA.2
2015 Company B BankA.2 80 BankA BankA.1 BankA.2

Consequently, I want to create a time serie for each column of BANK_2017, BANK_2016, BANK_2015 together with the borrower column.

Now, I am searching for an efficient way to do the same coding but for each year, thus basically:

gen sponsorlenderid_1 = _n
bys BORROWER BANK_2017: egen sponsorlenderid1 = min(sponsorlenderid_1) /*generate numeric id for each pair*/
tsset sponsorlenderid1 YEAR
tsfill, full
bys sponsorlenderid1: gen fiveyeartotal1 = sum(L1.AMOUNT) - sum(L60.AMOUNT)

gen sponsorlenderid_2 = _n
bys BORROWER BANK_2016: egen sponsorlenderid2 = min(sponsorlenderid_2) /*generate numeric id for each pair*/
tsset sponsorlenderid2 YEAR
tsfill, full
bys sponsorlenderid2: gen fiveyeartotal2 = sum(L1.AMOUNT) - sum(L60.AMOUNT)

gen sponsorlenderid_3 = _n
bys BORROWER BANK_2015: egen sponsorlenderid3 = min(sponsorlenderid_3) /*generate numeric id for each pair*/
tsset sponsorlenderid3 YEAR
​​​​​​​tsfill, full
bys sponsorlenderid3: gen fiveyeartotal3 = sum(L1.AMOUNT) - sum(L60.AMOUNT)

Since I have more than three years of data it would really help to have a loop to create this function for each year.

Many thanks in advance!

Best regards,

Ekaterina