Panel data and spells: Years after/before a spell

[Malte Hessenius]

Dear Statalisters,

first of all, thumbs up and an honest "thank you" for all the great contributions in this forum! A while ago I started working with Stata and since then I came accross this forum a dozen of times and it really helped me out with some issues.

However, I am now dealing with an issue I can't really grasp. I am working with panel data on country-year level in which the countries are exposed to a certain spell (e.g. policy measure XY). For further analysis, I want to calculate the following time trends:

1. Duration of a spell (already solved with: "tsspell Spell, cond(Spell==1)")
2. YearsAfter: Years after a spell (= years that passed since completion of last spell until time t or until the next spell is completed)
3. YearsBefore: Years before spell (= X years until next program starts)

Here some example data, I created in Excel. The columns YearsAfter (#2) and YearsBefore (#3) are created manually. So my basic question is: How do I replicate these with some code? I already run the following, which might provide some useful information. But I excluded the outcome in the data-snippet to reduce clutter.

// use tsspell which creates following columns: _seq _spell _end
ssc install tsspell
tsspell Spell, cond(Spell==1)

Here my artificial data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 Country int Year byte(Spell YearsAfter YearsBefore)
"A" 2000 0 0 3
"A" 2001 0 0 2
"A" 2002 0 0 1
"A" 2003 1 0 0
"A" 2004 1 0 0
"A" 2005 0 1 1
"A" 2006 1 0 0
"A" 2007 1 0 0
"A" 2008 0 1 4
"A" 2009 0 2 3
"A" 2010 0 3 2
"A" 2011 0 4 1
"A" 2012 1 5 0
"A" 2013 1 6 0
"A" 2014 1 7 0
"A" 2015 0 1 0
"B" 2000 1 0 0
"B" 2001 1 0 0
"B" 2002 0 1 5
"B" 2003 0 2 4
"B" 2004 0 3 3
"B" 2005 0 4 2
"B" 2006 0 5 1
"B" 2007 1 0 0
"B" 2008 1 0 0
"B" 2009 1 0 0
"B" 2010 0 1 4
"B" 2011 0 2 3
"B" 2012 0 3 2
"B" 2013 0 4 1
"B" 2014 1 5 0
"B" 2015 1 6 0
"C" 2000 0 1 1
"C" 2001 1 2 0
"C" 2002 1 3 0
"C" 2003 0 1 5
"C" 2004 0 2 4
"C" 2005 0 3 3
"C" 2006 0 4 2
"C" 2007 0 5 1
"C" 2008 1 6 0
"C" 2009 1 7 0
"C" 2010 1 8 0
"C" 2011 0 1 3
"C" 2012 0 2 2
"C" 2013 0 3 1
"C" 2014 1 4 0
"C" 2015 1 5 0
end
format %ty Year

Any suggestions are highly appreciated. I don't really have an idea how to tackle this problem. For instance about the column "YearsBefore" - how do I tell Stata to stop counting backwards if the next spell starts (Spell==1) ?

Best,
MH (who uses Stata 15 btw)

[Nick Cox]

Thanks for your data example and interesting questions. I have worked on spells, but I don't understand all your rules for what you want. Nevertheless here is some technique that may get you part of the way.

     +------------------------------------------------------------------------+
     | Country   Year   Spell   _spell   YearsB~e   Before   YearsA~r   After |
     |------------------------------------------------------------------------|
  1. |       A   2000       0        1          3        3          0       0 |
  2. |       A   2001       0        1          2        2          0       0 |
  3. |       A   2002       0        1          1        1          0       0 |
     |------------------------------------------------------------------------|
  4. |       A   2003       1        2          0        0          0       0 |
  5. |       A   2004       1        2          0        0          0       0 |
     |------------------------------------------------------------------------|
  6. |       A   2005       0        3          1        1          1       1 |
     |------------------------------------------------------------------------|
  7. |       A   2006       1        4          0        0          0       0 |
  8. |       A   2007       1        4          0        0          0       0 |
     |------------------------------------------------------------------------|
  9. |       A   2008       0        5          4        4          1       1 |
 10. |       A   2009       0        5          3        3          2       2 |
 11. |       A   2010       0        5          2        2          3       3 |
 12. |       A   2011       0        5          1        1          4       4 |
     |------------------------------------------------------------------------|
 13. |       A   2012       1        6          0        0          5       0 |
 14. |       A   2013       1        6          0        0          6       0 |
 15. |       A   2014       1        6          0        0          7       0 |
     |------------------------------------------------------------------------|
 16. |       A   2015       0        7          0        1          1       1 |
     |------------------------------------------------------------------------|
 17. |       B   2000       1        1          0        0          0       0 |
 18. |       B   2001       1        1          0        0          0       0 |
     |------------------------------------------------------------------------|
 19. |       B   2002       0        2          5        5          1       1 |
 20. |       B   2003       0        2          4        4          2       2 |
 21. |       B   2004       0        2          3        3          3       3 |
 22. |       B   2005       0        2          2        2          4       4 |
 23. |       B   2006       0        2          1        1          5       5 |
     |------------------------------------------------------------------------|
 24. |       B   2007       1        3          0        0          0       0 |
 25. |       B   2008       1        3          0        0          0       0 |
 26. |       B   2009       1        3          0        0          0       0 |
     |------------------------------------------------------------------------|
 27. |       B   2010       0        4          4        4          1       1 |
 28. |       B   2011       0        4          3        3          2       2 |
 29. |       B   2012       0        4          2        2          3       3 |
 30. |       B   2013       0        4          1        1          4       4 |
     |------------------------------------------------------------------------|
 31. |       B   2014       1        5          0        0          5       0 |
 32. |       B   2015       1        5          0        0          6       0 |
     |------------------------------------------------------------------------|
 33. |       C   2000       0        1          1        1          1       0 |
     |------------------------------------------------------------------------|
 34. |       C   2001       1        2          0        0          2       0 |
 35. |       C   2002       1        2          0        0          3       0 |
     |------------------------------------------------------------------------|
 36. |       C   2003       0        3          5        5          1       1 |
 37. |       C   2004       0        3          4        4          2       2 |
 38. |       C   2005       0        3          3        3          3       3 |
 39. |       C   2006       0        3          2        2          4       4 |
 40. |       C   2007       0        3          1        1          5       5 |
     |------------------------------------------------------------------------|
 41. |       C   2008       1        4          0        0          6       0 |
 42. |       C   2009       1        4          0        0          7       0 |
 43. |       C   2010       1        4          0        0          8       0 |
     |------------------------------------------------------------------------|
 44. |       C   2011       0        5          3        3          1       1 |
 45. |       C   2012       0        5          2        2          2       2 |
 46. |       C   2013       0        5          1        1          3       3 |
     |------------------------------------------------------------------------|
 47. |       C   2014       1        6          0        0          4       0 |
 48. |       C   2015       1        6          0        0          5       0 |
     +------------------------------------------------------------------------+

Now let's focus on the differences between what (you say) you want and what my code gives. Here notation such as A1 or B3 means "country A, _spell 1" or "country B, _spell 3".

1. For the after count, you have a clause "or until the next spell is completed" but I can't see that you apply it consistently.

So, for example, the after count continues after the previous spells of 0 in A6, but not in A4; and in B5 but not in B3. Spells for C have continuation.

My code doesn't attempt to work out what that means until you clarify what you want there.

2. For the before count I get a different result for A7, but I'd argue that the 1 I have there really should be missing. As you don't know from the data when the next spell of 1s begins. You have 0, but that does not make sense either. I haven't fixed the code for that detail.

[Malte Hessenius]

Thank you Nick for your answer!

To clarify things:

• In general: In my investigation I consider only one policy measure. Hence, the spell variable is a dummy. 1 = spell is in place. 0 = no spell. So from my understanding I only count consecutive years with Spell==1 as "country X being under spell". [Code: tsspell Spell, cond(Spell==1)]
• After Count: years that passed since completion of last spell until the next spell is completed. Whether it makes more sense to stop the counting variable when the next spell is in place is still up for discussion. But in this case, I could simply set the certain cells to zero [e.g.: replace YearsAfter = 0 if Spell==1]. I admit, I did a small mistake in this column. Sorry for that.
• Before Count: You are right that things are getting a bit squishy for the last year. But to keep it consistent, I'd prefer to treat the dataset as begin- and end-period of the policy measure. Otherwise you could also question the validity of the after count for the first year (e.g. spells could have happened in 1996).

Here my corrected dataset:

 

Country
countr~D
Year
Spell
_spell
_end
YearsA~r
YearsB~e











1.
A
1
2000
0
0
0
0
3

2.
A
1
2001
0
0
0
0
2

3.
A
1
2002
0
0
0
0
1

4.
A
1
2003
1
1
0
0
0

5.
A
1
2004
1
1
1
0
0











6.
A
1
2005
0
0
0
1
1

7.
A
1
2006
1
2
0
2
0

8.
A
1
2007
1
2
1
3
0

9.
A
1
2008
0
0
0
1
4

10.
A
1
2009
0
0
0
2
3











11.
A
1
2010
0
0
0
3
2

12.
A
1
2011
0
0
0
4
1

13.
A
1
2012
1
3
0
5
0

14.
A
1
2013
1
3
0
6
0

15.
A
1
2014
1
3
1
7
0











16.
A
1
2015
0
0
0
1
0

17.
B
2
2000
1
1
0
2
0

18.
B
2
2001
1
1
1
3
0

19.
B
2
2002
0
0
0
1
5

20.
B
2
2003
0
0
0
2
4











21.
B
2
2004
0
0
0
3
3

22.
B
2
2005
0
0
0
4
2

23.
B
2
2006
0
0
0
5
1

24.
B
2
2007
1
2
0
6
0

25.
B
2
2008
1
2
0
7
0











26.
B
2
2009
1
2
1
8
0

27.
B
2
2010
0
0
0
1
4

28.
B
2
2011
0
0
0
2
3

29.
B
2
2012
0
0
0
3
2

30.
B
2
2013
0
0
0
4
1











31.
B
2
2014
1
3
0
5
0

32.
B
2
2015
1
3
1
6
0

33.
C
3
2000
0
0
0
1
1

34.
C
3
2001
1
1
0
2
0

35.
C
3
2002
1
1
1
3
0











36.
C
3
2003
0
0
0
1
5

37.
C
3
2004
0
0
0
2
4

38.
C
3
2005
0
0
0
3
3

39.
C
3
2006
0
0
0
4
2

40.
C
3
2007
0
0
0
5
1











41.
C
3
2008
1
2
0
6
0

42.
C
3
2009
1
2
0
7
0

43.
C
3
2010
1
2
1
8
0

44.
C
3
2011
0
0
0
1
3

45.
C
3
2012
0
0
0
2
2











46.
C
3
2013
0
0
0
3
1

47.
C
3
2014
1
3
0
4
0

48.
C
3
2015
1
3
1
5
0

[Nick Cox]

In terms of your three points:

In general: In my investigation I consider only one policy measure. Hence, the spell variable is a dummy. 1 = spell is in place. 0 = no spell. So from my understanding I only count consecutive years with Spell==1 as "country X being under spell". [Code: tsspell Spell, cond(Spell==1)]

> OK, but for what you're asking for, tsspell with a different definition of spell gets you part way to the variables you want.

After Count: years that passed since completion of last spell until the next spell is completed. Whether it makes more sense to stop the counting variable when the next spell is in place is still up for discussion. But in this case, I could simply set the certain cells to zero [e.g.: replace YearsAfter = 0 if Spell==1]. I admit, I did a small mistake in this column. Sorry for that.

> I don't know where that leaves my code.

Before Count: You are right that things are getting a bit squishy for the last year. But to keep it consistent, I'd prefer to treat the dataset as begin- and end-period of the policy measure. Otherwise you could also question the validity of the after count for the first year (e.g. spells could have happened in 1996).

> I don't know where that leaves my code.

[Nick Cox]

Very sorry, but I left out my code in #2. That was stupid. It's on another computer, and I will post later.

[Nick Cox]

Here it is:

egen Id = group(Country), label 
tsset Id Year 

* spells defined just by Spell being constant 1 or 0 
tsspell Spell

* Before is reversed number in sequence for spells of 0 
egen Length  = max(_seq), by(Id _spell) 
gen Before = cond(Spell == 1, 0, Length - _seq + 1) 

* After here number in sequence for spells of 0, except not the first such spell 
gen After = cond(Spell == 0, _seq, 0) 
by Id : replace After = 0 if _spell==1 & Spell[1] == 0 

list Country  Year Spell _spell *Before  *After, sepby(Country _spell)

[Romalpa Akzo]

Other way to take you to the target, without using -tsspell-. In addition, I do support the suggestion of Nick that: YearAfter for starting year(s) and YearsBefore for last year(s) (with Spell ==0) should be missing (i.e. unknown) instead of zero(s). This will keep the logic more consistent.

gen After = 0 if Spell == 1
gen Before = 0 if Spell ==1

gsort Country -Year
by Country: replace Before = Before[_n-1] + 1 if Spell == 0
bys Country (Year): replace After = After[_n-1] + 1 if Spell == 0

by Country: replace After = After[_n-1] + 1 if Spell == 1
* Omit this line if you want stop counting until the end of next Spell period  

mvencode After Before, mv(.=0) override
* I recommend to omit the last line then missing values are logically maintained..

[Malte Hessenius]

Thank you both for your help! I applied both codes on my large panel data and both of them worked.

Romalpa Akzo I really liked your solution. I couldn't think about reversing the order of the years. Will keep that in mind for the future.

[Nick Cox]

Romalpa Akzo's code is very nice and direct, a reminder (to me) that just because you see a nail, the hammer in your hand is not necessarily the best tool.

For reversing time, etc., see also e.g. https://www.stata.com/support/faqs/d...issing-values/

[Romalpa Akzo]

Nick Cox sensei, thanks for your encouragement.

And well, you are right about the hammer (:-)) Sometimes, the side-effects of being well equipped is that we tend to use the strongest weapon as immediately being sure that it will be working. The limited clipper of knowledge (like mine) sometimes (but rarely) happens to be favorable in reminding how to deal directly with the nail.

Indeed, almost of my suggestions for readers are originated from your lessons, somewhere in the Stata journals or this Forum. I do admire you (and some other “gurus”) very much. Thanks for your endless passion and great achievement to the Stata world.