Please help: tsset with loops

[Ekaterina Rakotovola]

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

[Phil Bromiley]

Welcome to Statalist. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex. Being able to replicate your problem can be essential to helping you.

I don't understand your data structure - it seems like it is Borrower-year, but then you have these banks associated with different years. I suspect you want to reshape from wide to long. Look up reshape in the documentation.