Calculating Loss Probability over the past 4 years

[Daniel Wilmink]

Dear Statalist members,

I am trying to code a new variable into stata which calculates the loss probability per firm. I estimate the loss probability as the proportion of years for which a firm reports a negative income before extraordinary items (variable ib) over the past 4 fiscal years. I.e. when in 4 fiscal years a firm reports a negative income once, the loss probability (loss_prob variable) should be 1/4=0.25. Though this might seem intuitively easy, I am struggling with the fact that not every firm (ID=gvkey) has four prior years (fyear) available. Could anyone help me out on how to code this? Below is some examplary data!

P.S. the firms are allowed to have a value for loss_prob if only 2 or 3 quarters are available, I will later remove these myself if deemed necessary through the use of . drop if count<=3 for instance.

gvkey fyear ib loss_prob
001004 2001 -58.939
001004 2002 -12.41
001004 2003 3.504
001004 2004 18.572
001004 2005 35.163
001004 2006 59.447
001004 2007 75.74500000000001
001004 2008 80.59999999999999
001004 2009 44.628
001004 2010 73.139
001004 2011 67.723
001004 2012 55
001004 2013 72.90000000000001
001004 2014 -54.5
001004 2015 40.5
001013 2001 -1287.7
001013 2002 -1145
001013 2003 -76.7
001013 2004 31.3
001013 2005 85.5
001013 2006 94.2
001013 2007 113.3
001013 2008 -44.4
001013 2009 -465.7
001013 2010 77.2
001034 2001 -35.674
001034 2002 -93.56699999999999
001034 2003 19.444
001034 2004 -314.737
001034 2005 62.176
001034 2006 59.992

Many thanks!

[Nick Cox]

Thanks for the data example but please use CODE markers and show dataex (SSC) code. http://www.statalist.org/forums/help#stata

This also yields to rangestat (SSC; Robert Picard and team) as in your previous thread. http://www.statalist.org/forums/foru...st-16-quarters It's made easier by creating an indicator variable beforehand. Then we can count the number of pertinent observations and get the mean at the same time.

clear
input gvkey fyear ib
001004 2001 -58.939
001004 2002 -12.41
001004 2003 3.504
001004 2004 18.572
001004 2005 35.163
001004 2006 59.447
001004 2007 75.74500000000001
001004 2008 80.59999999999999
001004 2009 44.628
001004 2010 73.139
001004 2011 67.723
001004 2012 55
001004 2013 72.90000000000001
001004 2014 -54.5
001004 2015 40.5
001013 2001 -1287.7
001013 2002 -1145
001013 2003 -76.7
001013 2004 31.3
001013 2005 85.5
001013 2006 94.2
001013 2007 113.3
001013 2008 -44.4
001013 2009 -465.7
001013 2010 77.2
001034 2001 -35.674
001034 2002 -93.56699999999999
001034 2003 19.444
001034 2004 -314.737
001034 2005 62.176
001034 2006 59.992
end

gen isneg = ib < 0
rangestat (count) count=isneg (mean) pneg=isneg , int(fyear -4 -1) by(gvkey)

list, sepby(gvkey)

     +------------------------------------------------------+
     | gvkey   fyear         ib   isneg   count        pneg |
     |------------------------------------------------------|
  1. |  1004    2001    -58.939       1       .           . |
  2. |  1004    2002     -12.41       1       1           1 |
  3. |  1004    2003      3.504       0       2           1 |
  4. |  1004    2004     18.572       0       3   .66666667 |
  5. |  1004    2005     35.163       0       4          .5 |
  6. |  1004    2006     59.447       0       4         .25 |
  7. |  1004    2007     75.745       0       4           0 |
  8. |  1004    2008       80.6       0       4           0 |
  9. |  1004    2009     44.628       0       4           0 |
 10. |  1004    2010     73.139       0       4           0 |
 11. |  1004    2011     67.723       0       4           0 |
 12. |  1004    2012         55       0       4           0 |
 13. |  1004    2013       72.9       0       4           0 |
 14. |  1004    2014      -54.5       1       4           0 |
 15. |  1004    2015       40.5       0       4         .25 |
     |------------------------------------------------------|
 16. |  1013    2001    -1287.7       1       .           . |
 17. |  1013    2002      -1145       1       1           1 |
 18. |  1013    2003      -76.7       1       2           1 |
 19. |  1013    2004       31.3       0       3           1 |
 20. |  1013    2005       85.5       0       4         .75 |
 21. |  1013    2006       94.2       0       4          .5 |
 22. |  1013    2007      113.3       0       4         .25 |
 23. |  1013    2008      -44.4       1       4           0 |
 24. |  1013    2009     -465.7       1       4         .25 |
 25. |  1013    2010       77.2       0       4          .5 |
     |------------------------------------------------------|
 26. |  1034    2001    -35.674       1       .           . |
 27. |  1034    2002    -93.567       1       1           1 |
 28. |  1034    2003     19.444       0       2           1 |
 29. |  1034    2004   -314.737       1       3   .66666667 |
 30. |  1034    2005     62.176       0       4         .75 |
 31. |  1034    2006     59.992       0       4          .5 |
     +------------------------------------------------------+

[Daniel Wilmink]

Man I wish I was as genius as you, would make my thesis-life a lot easier. Thanks a lot and next time I will incorporate those codes!