My dataset looks is as below:
I want to transform this dataset such that I have a bank-firm observation for every year till the loan is repaid. If there is no loan made in that year, I want the loan_amount to be zero for that year. If there are multiple loans made by a bank to a firm for the same year, I want the loan_amount to be the total of the loans. Note: If the loan repayment year is missing, it means that the loan hasn't been repaid yet and I want the firm-bank pair to show up till the end of my sample(2023).
For instance, I want my data to look like this:
firm_name co_code bank_code bank_name loan_amount year
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 49 1995
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 85 1996
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1997
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1998
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1999
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 90 2000
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2001
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2002
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2003
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2004
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2005
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2006
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2007
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 50 2008
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2009
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2010
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2011
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2012
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2013
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2014
I want the data to look like this for every firm-bank pair in my dataset. As long as there is an outstanding loan between a bank and a firm, I want the bank-firm pair to be present in the data. In a sense, I am looking to create a panel dataset where my panel id is a bank-firm pair and the time period ranges from 1990-2023. I have around 250,000 observations in my data.
I am not sure how to proceed in order to transform my data as needed. Any help is greatly appreciated. Thank you.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str78 firm_name long(co_code bank_code) str62 bank_name double loan_amount float(year repayment_year) "20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 50 2008 2014 "20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 49 1995 2014 "20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 85 1996 2014 "20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 90 2000 2014 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 230 2010 2013 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 9 1996 2014 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 49 2020 2021 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 1394.6 2012 . "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 1.64 1996 1999 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 1394.6 1994 . "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 3.7 1991 2013 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 1280.4 2013 2013 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 49 2020 2022 "20 MICRONS LIMITED" 11 236328 "STATE BANK OF INDIA" 6 1990 2013 "20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." 4.706169 2012 2016 "20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." .483 2010 2013 "20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." 1.135 2009 2013 "20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." .338 2009 2013 end
For instance, I want my data to look like this:
firm_name co_code bank_code bank_name loan_amount year
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 49 1995
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 85 1996
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1997
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1998
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 1999
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 90 2000
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2001
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2002
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2003
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2004
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2005
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2006
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2007
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 50 2008
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2009
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2010
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2011
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2012
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2013
"20 MICRONS LIMITED" 11 100283 "I D B I BANK LTD." 0 2014
I want the data to look like this for every firm-bank pair in my dataset. As long as there is an outstanding loan between a bank and a firm, I want the bank-firm pair to be present in the data. In a sense, I am looking to create a panel dataset where my panel id is a bank-firm pair and the time period ranges from 1990-2023. I have around 250,000 observations in my data.
I am not sure how to proceed in order to transform my data as needed. Any help is greatly appreciated. Thank you.
Comment