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
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 . |
+------------------------------------------------------------------------------+
```