Transforming dataset to a panel format

[Naina Sharma]

My dataset looks is as below:

* 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

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.

[Andrew Musau]

* 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

bys firm_name bank_name: egen min= min(year)
bys firm_name bank_name: egen max= max(repayment_year)
by firm_name bank_name: gen toexpand= max-min+2 if _n==1
expand toexpand, g(new)
replace loan_amount=0 if new
bys firm_name bank_name new: replace year=min if _n==1 & new
by firm_name bank_name new: replace year=year[_n-1]+1 if _n>1 & new
bys firm_name bank_name year (loan_amount): keep if _n==_N
drop min max new repayment_year toexpand
sort firm_name bank_name year

Res.:

. l, sepby(firm_name bank_code)

     +---------------------------------------------------------------------------------+
     |          firm_name   co_code   bank_c~e             bank_name   loan_a~t   year |
     |---------------------------------------------------------------------------------|
  1. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.      1.135   2009 |
  2. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.       .483   2010 |
  3. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.          0   2011 |
  4. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.   4.706169   2012 |
  5. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.          0   2013 |
  6. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.          0   2014 |
  7. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.          0   2015 |
  8. | 20 MICRONS LIMITED        11     256066        AXIS BANK LTD.          0   2016 |
     |---------------------------------------------------------------------------------|
  9. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.         49   1995 |
 10. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.         85   1996 |
 11. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   1997 |
 12. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   1998 |
 13. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   1999 |
 14. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.         90   2000 |
 15. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2001 |
 16. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2002 |
 17. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2003 |
 18. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2004 |
 19. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2005 |
 20. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2006 |
 21. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2007 |
 22. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.         50   2008 |
 23. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2009 |
 24. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2010 |
 25. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2011 |
 26. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2012 |
 27. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2013 |
 28. | 20 MICRONS LIMITED        11     100283     I D B I BANK LTD.          0   2014 |
     |---------------------------------------------------------------------------------|
 29. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          6   1990 |
 30. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA        3.7   1991 |
 31. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1992 |
 32. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1993 |
 33. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA     1394.6   1994 |
 34. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1995 |
 35. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          9   1996 |
 36. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1997 |
 37. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1998 |
 38. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   1999 |
 39. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2000 |
 40. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2001 |
 41. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2002 |
 42. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2003 |
 43. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2004 |
 44. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2005 |
 45. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2006 |
 46. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2007 |
 47. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2008 |
 48. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2009 |
 49. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA        230   2010 |
 50. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2011 |
 51. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA     1394.6   2012 |
 52. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA     1280.4   2013 |
 53. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2014 |
 54. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2015 |
 55. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2016 |
 56. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2017 |
 57. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2018 |
 58. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2019 |
 59. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA         49   2020 |
 60. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2021 |
 61. | 20 MICRONS LIMITED        11     236328   STATE BANK OF INDIA          0   2022 |
     +---------------------------------------------------------------------------------+

[Andrew Musau]

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).

I missed this. Start with

replace repayment_year=2023 if missing(repayment_year)

and then #2. Also, looking closer at your data, you have multiple payments in the same year and bank, e.g.,

"20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." 1.135 2009 2013
"20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." .338 2009 2013

You will want to aggregate these payments before starting to create the panel.

collapse (sum) loan_amount, by(firm_name co_code bank_code bank_name year repayment_year)

[Naina Sharma]

Thank you so much. This worked very well.

I wanted to ask one more thing. In my data, as you mentioned in #3 I have multiple payments in the same year and bank. But for some instances, the repayment year is different. For example:

"20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." 1.135 2009 2013
"20 MICRONS LIMITED" 11 256066 "AXIS BANK LTD." .338 2009 2020

In this case, I want to aggregate the payments and keep the repayment year as the later year (2020). I am not sure how to do that.
I really appreciate the help. Thank you!

[Andrew Musau]

Before collapse:

ds repayment_year loan_amount, not
bys `r(varlist)' (repayment_year): replace repayment_year= repayment_year[_N]

[Naina Sharma]

Thank you so much! This worked very well!!