Gaps between investment types. Detecting patterns

Question

I want to count gaps between investment types in my panel data. For example, I know when a firm invested positively, negatively or was inactive. What I would like to do is to sum up number of years between negative investment and positive, and potentially another count would be between negative or inactive and positive. In other words, I want to count how many negative investments occurred before a positive investment: the counter resets each time a positive investment occurs, and similarly with inactivity and negative in the second calculation. In all cases I want to ignore missing values. Here's an example of how I would like my table to look like:

id  Year    invType invType GapNegPos   GapAllPos
1   2000    1   Negative        
1   2001    1   Negative        
1   2002    1   Negative        
1   2003    2   Positive    3             3
1   2004    1   Negative        
1   2005    1   Negative        
1   2006    2   Positive    2             2
1   2007    1   Negative        
1   2008    1   Negative        
1   2009    1   Negative        
1   2010    3   Inactivity      
1   2011    1   Negative        
1   2012    1   Negative        
1   2013    1   Positive    2             6
2   2000    .           
2   2001    .           
2   2002    .           
2   2003    .           
2   2004    2   Positive    0             0
2   2005    2   Positive    0             0
2   2006    .           
2   2007    .           
2   2008    3   Positive    0             0
2   2009    2   Inactivity      
2   2010    1   Negative        
2   2011    1   Negative        
2   2012    2   Positive    2             3
2   2013    1   Negative        

Answer 1

Here is one of your desired new variables. The other seems to need just a variation on the code

See https://journals.sagepub.com/doi/pdf/10.1177/1536867X0700700209 for some related discussion.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int Year byte invType1 str10 invType2 byte(GapNegPos GapAllPos)
1 2000 1 "Negative"   . .
1 2001 1 "Negative"   . .
1 2002 1 "Negative"   . .
1 2003 2 "Positive"   3 3
1 2004 1 "Negative"   . .
1 2005 1 "Negative"   . .
1 2006 2 "Positive"   2 2
1 2007 1 "Negative"   . .
1 2008 1 "Negative"   . .
1 2009 1 "Negative"   . .
1 2010 3 "Inactivity" . .
1 2011 1 "Negative"   . .
1 2012 1 "Negative"   . .
1 2013 1 "Positive"   2 6
2 2000 . ""           . .
2 2001 . ""           . .
2 2002 . ""           . .
2 2003 . ""           . .
2 2004 2 "Positive"   0 0
2 2005 2 "Positive"   0 0
2 2006 . ""           . .
2 2007 . ""           . .
2 2008 3 "Positive"   0 0
2 2009 2 "Inactivity" . .
2 2010 1 "Negative"   . .
2 2011 1 "Negative"   . .
2 2012 2 "Positive"   2 3
2 2013 1 "Negative"   . .
end

bysort id (Year) : gen negcount = 1 if invType2 == "Negative" & invType2[_n-1] != "Negative"
by id: replace negcount = negcount[_n-1] + 1 if invType2 == "Negative" & negcount == . 
by id: gen wanted1 = negcount[_n-1] if invType2 == "Positive"
replace wanted1 = 0 if invType2 == "Positive" & wanted1 == . 

list, sepby(id)

     +------------------------------------------------------------------------------+
     | id   Year   invType1     invType2   GapNeg~s   GapAll~s   negcount   wanted1 |
     |------------------------------------------------------------------------------|
  1. |  1   2000          1     Negative          .          .          1         . |
  2. |  1   2001          1     Negative          .          .          2         . |
  3. |  1   2002          1     Negative          .          .          3         . |
  4. |  1   2003          2     Positive          3          3          .         3 |
  5. |  1   2004          1     Negative          .          .          1         . |
  6. |  1   2005          1     Negative          .          .          2         . |
  7. |  1   2006          2     Positive          2          2          .         2 |
  8. |  1   2007          1     Negative          .          .          1         . |
  9. |  1   2008          1     Negative          .          .          2         . |
 10. |  1   2009          1     Negative          .          .          3         . |
 11. |  1   2010          3   Inactivity          .          .          .         . |
 12. |  1   2011          1     Negative          .          .          1         . |
 13. |  1   2012          1     Negative          .          .          2         . |
 14. |  1   2013          1     Positive          2          6          .         2 |
     |------------------------------------------------------------------------------|
 15. |  2   2000          .                       .          .          .         . |
 16. |  2   2001          .                       .          .          .         . |
 17. |  2   2002          .                       .          .          .         . |
 18. |  2   2003          .                       .          .          .         . |
 19. |  2   2004          2     Positive          0          0          .         0 |
 20. |  2   2005          2     Positive          0          0          .         0 |
 21. |  2   2006          .                       .          .          .         . |
 22. |  2   2007          .                       .          .          .         . |
 23. |  2   2008          3     Positive          0          0          .         0 |
 24. |  2   2009          2   Inactivity          .          .          .         . |
 25. |  2   2010          1     Negative          .          .          1         . |
 26. |  2   2011          1     Negative          .          .          2         . |
 27. |  2   2012          2     Positive          2          3          .         2 |
 28. |  2   2013          1     Negative          .          .          1         . |
     +------------------------------------------------------------------------------+
```