I have some data about bankrupt firms (see below for one hypothetical example). What I am trying to do is two things:
1. Notice that the bankruptcy year is 2003 for this firm, but the financial data only goes to 2000. I am trying to come up with a bankruptcy prediction model using survival analysis (hazard model). This bankrupt firm does not have data for 2001 and 2002 on compustat. I want to create two more lines of data (2001 and 2002) and carry information from 2000 to 2001 and 2002 (the year before bankruptcy). I have codes to carry information from 2000 over to 2001 and 2002, but I need codes to create gvkey 030057 and fyear 2001 and 2002 as well as to create empty fields of ROA, ETL, and LTA. I am thinking the best way to do this is using do loop, but I am not very familiar with it. (gvkey is company ID).
gvkey fyear bankruptcy_year ROA ETL LTA
030057 1996 2003 -0.42753 -0.00023 0.9514
030057 1997 2003 . . .
030057 1998 2003 -0.82566 -0.04087 0.952
030057 1999 2003 0.01165 0.11386 0.699
030057 2000 2003 0.01759 0.12694 0.7242
transform to below
gvkey fyear bankruptcy_year ROA ETL LTA
030057 1996 2003 -0.42753 -0.00023 0.9514
030057 1997 2003 -0.42753 -0.00023 0.9514
030057 1998 2003 -0.82566 -0.04087 0.952
030057 1999 2003 0.01165 0.11386 0.699
030057 2000 2003 0.01759 0.12694 0.7242
030057 2001 2003 0.01759 0.12694 0.7242
030057 2002 2003 0.01759 0.12694 0.7242
2. The following example is for a non-bankrupt firm that has probably merged with another firm in 2001 and therefore doesn’t have available data from 2001 onward. I want to delete lines for 2001 and 2001 (and I don’t have a field for merge year), but I want to preserve 1997 so I can carry information from 1996 to 1997. (see transformation).
gvkey fyear ROA ETL LTA
000100 1996 -0.42753 -0.00023 0.9514
000100 1997 . . .
000100 1998 -0.82566 -0.04087 0.952
000100 1999 0.01165 0.11386 0.699
000100 2000 0.01759 0.12694 0.7242
000100 2001 . . .
000100 2002 . . .
transform to below:
gvkey fyear ROA ETL LTA
000100 1996 -0.42753 -0.00023 0.9514
000100 1997 -0.42753 -0.00023 0.9514
000100 1998 -0.82566 -0.04087 0.952
000100 1999 0.01165 0.11386 0.699
000100 2000 0.01759 0.12694 0.7242
Any help would be greatly appreciated! Thanks!!
1. Notice that the bankruptcy year is 2003 for this firm, but the financial data only goes to 2000. I am trying to come up with a bankruptcy prediction model using survival analysis (hazard model). This bankrupt firm does not have data for 2001 and 2002 on compustat. I want to create two more lines of data (2001 and 2002) and carry information from 2000 to 2001 and 2002 (the year before bankruptcy). I have codes to carry information from 2000 over to 2001 and 2002, but I need codes to create gvkey 030057 and fyear 2001 and 2002 as well as to create empty fields of ROA, ETL, and LTA. I am thinking the best way to do this is using do loop, but I am not very familiar with it. (gvkey is company ID).
gvkey fyear bankruptcy_year ROA ETL LTA
030057 1996 2003 -0.42753 -0.00023 0.9514
030057 1997 2003 . . .
030057 1998 2003 -0.82566 -0.04087 0.952
030057 1999 2003 0.01165 0.11386 0.699
030057 2000 2003 0.01759 0.12694 0.7242
transform to below
gvkey fyear bankruptcy_year ROA ETL LTA
030057 1996 2003 -0.42753 -0.00023 0.9514
030057 1997 2003 -0.42753 -0.00023 0.9514
030057 1998 2003 -0.82566 -0.04087 0.952
030057 1999 2003 0.01165 0.11386 0.699
030057 2000 2003 0.01759 0.12694 0.7242
030057 2001 2003 0.01759 0.12694 0.7242
030057 2002 2003 0.01759 0.12694 0.7242
2. The following example is for a non-bankrupt firm that has probably merged with another firm in 2001 and therefore doesn’t have available data from 2001 onward. I want to delete lines for 2001 and 2001 (and I don’t have a field for merge year), but I want to preserve 1997 so I can carry information from 1996 to 1997. (see transformation).
gvkey fyear ROA ETL LTA
000100 1996 -0.42753 -0.00023 0.9514
000100 1997 . . .
000100 1998 -0.82566 -0.04087 0.952
000100 1999 0.01165 0.11386 0.699
000100 2000 0.01759 0.12694 0.7242
000100 2001 . . .
000100 2002 . . .
transform to below:
gvkey fyear ROA ETL LTA
000100 1996 -0.42753 -0.00023 0.9514
000100 1997 -0.42753 -0.00023 0.9514
000100 1998 -0.82566 -0.04087 0.952
000100 1999 0.01165 0.11386 0.699
000100 2000 0.01759 0.12694 0.7242
Any help would be greatly appreciated! Thanks!!