Panel Data, not sorted issue [simple but confusing]

[Xiaoke Ye]

Hello Everyone

I have panel data, which looks like the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long directorid str16 securitydescription float(date sellD)
2009 "Com" 462 0
2009 "Com" 527 1
2009 "Com" 532 1
2009 "Com" 559 0
2010 "Com" 462 0
2010 "Com" 577 1
2010 "Com" 578 1
2163 "Com" 672 0
3688 "Com" 545 1
3688 "Com" 550 1
3689 "Com" 399 0
3689 "Com" 400 0
3689 "Com" 405 0
3689 "Com" 410 0
3689 "Com" 438 0
3689 "Com" 440 0
3689 "Com" 447 0
3689 "Com" 450 0
3689 "Com" 459 0
3689 "Com" 480 0
3689 "Com" 481 0
3689 "Com" 490 0
3689 "Com" 493 0
3689 "Com" 502 0
3689 "Com" 504 0
3689 "Com" 505 0
3689 "Com" 510 0
3689 "Com" 512 0
3689 "Com" 516 0
3689 "Com B" 526 0
end
format %tm date

I am trying to use the following code to generate a dummy

 bys directorid  securitydescription date : gen D=1 if sellD==1&L12.sellD==1&L24.sellD==1&l48.sellD==1

In plain English, I am trying to see, whether there is the same value of "sellD" for the same text of "securitydescription" for the same "directorid" in the last three years in the same month.

But the code does not work. Stata reports "not sorted". I know Stata may not be able to handle two cross-section ids, but what is the most efficient way to achieve the same effect?

[Nick Cox]

Using lag operators isn't consistent with any sort order but that defined by tsset or xtset, here presumably panel identifier and time variable.

You're looking back 12, 24 and 48 months it seems, but

what happened to 36?

your data seem very gappy!

Why "in the same month" therefore?

[Xiaoke Ye]

Using lag operators isn't consistent with any sort order but that defined by tsset or xtset, here presumably panel identifier and time variable.

You're looking back 12, 24 and 48 months it seems, but

what happened to 36?

your data seem very gappy!

Why "in the same month" therefore?

Thank you for your reply Nick

Yes, I forget I should put 36 instead of 48. Confession.
However, I was meant to set date as time variable and directorid as cross-section id. But there is repeated time values within panels because same directorid can have 2 or 3 different value of "securitytype" in the same month. But when I generate the D, I want to treat each securitytype as independent, so there will be no repeated observation within a "directorid date" panel
By "in the same month", I was trying to say. If sellD==1 in Jan 2019, I want to check if it is equal to one in Jan 2018, Jan 2017, Jan 2016.

Sorry, for the data example, but I am working with a confidential data set. The following is a longer one, but there are half million observations in my master file and different "securitytype" for the same directorid in the same month may not be shown in the example.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long personid float date str16 securitydescription float sellD
 30940 462 "Com" 0
 30940 527 "Com" 1
 30940 532 "Com" 1
 30940 559 "Com" 0
 30941 462 "Com" 0
 30941 577 "Com" 1
 30941 578 "Com" 1
 26840 672 "Com" 0
 27411 545 "Com" 1
 27411 550 "Com" 1
 27618 399 "Com" 0
 27618 400 "Com" 0
 27618 405 "Com" 0
 27618 410 "Com" 0
 27618 438 "Com" 0
 27618 440 "Com" 0
 27618 447 "Com" 0
 27618 450 "Com" 0
 27618 459 "Com" 0
 27618 480 "Com" 0
 27618 481 "Com" 0
 27618 490 "Com" 0
 27618 493 "Com" 0
 27618 502 "Com" 0
 27618 504 "Com" 0
 27618 505 "Com" 0
 27618 510 "Com" 0
 27618 512 "Com" 0
 27618 516 "Com" 0
 27618 526 "Com" 0
 27618 531 "Com" 0
 27618 536 "Com" 0
 27618 537 "Com" 0
 27618 544 "Com" 0
 27618 546 "Com" 0
 27623 546 "Com" 0
 27623 571 "Com" 0
 27871 481 "Com" 0
 27871 484 "Com" 0
 27871 516 "Com" 0
 27871 526 "Com" 0
 27871 541 "Com" 0
 27871 550 "Com" 0
 27871 556 "Com" 0
 27871 557 "Com" 0
 27871 563 "Com" 0
 27871 571 "Com" 0
 27871 581 "Com" 0
 27871 582 "Com" 0
 27871 588 "Com" 0
 27871 589 "Com" 0
 27874 544 "Com" 0
 27874 574 "Com" 0
 27874 582 "Com" 0
 27874 588 "Com" 0
 27874 589 "Com" 0
745822 532 "Com" 0
745822 554 "Com" 1
 18687 589 "Com" 0
 27878 541 "Com" 0
 27878 588 "Com" 0
 27878 589 "Com" 0
 27879 641 "Com" 1
 27882 583 "Com" 0
 27882 588 "Com" 0
 27883 537 "Com" 1
 27883 542 "Com" 1
 27883 548 "Com" 1
 27883 561 "Com" 1
 27883 574 "Com" 0
 27883 580 "Com" 0
 27883 588 "Com" 0
 29108 549 "Com" 1
 29108 552 "Com" 1
 29108 555 "Com" 1
 29108 557 "Com" 1
 29108 558 "Com" 1
 29108 567 "Com" 1
 29108 571 "Com" 1
 29108 588 "Com" 0
 29108 589 "Com" 0
 29108 607 "Com" 0
 30693 554 "Com" 1
 30693 588 "Com" 0
 30693 673 "Com" 0
 30693 691 "Com" 1
 31004 503 "Com" 1
 31004 530 "Com" 1
 29458 506 "Com" 1
 29458 549 "Com" 0
 29462 526 "Com" 0
 29462 573 "Com" 1
 27376 559 "Com" 0
 27376 573 "Com" 1
 16155 513 "Com" 0
 16155 524 "Com" 0
 16155 535 "Com" 0
 16155 536 "Com" 0
 16155 545 "Com" 0
 16155 564 "Com" 0
end
format %tm date

[Nick Cox]

I plotted your data. If they're typical then looking 12, 24, 36 months before will be futile. Most of the values will be missing.

Here's a graph.

Here for the record is how I got it.

egen id = group(personid), label

* ssc install stripplot

stripplot date, over(id) ms(|) ytitle("") ysc(reverse) scheme(s1color)

What is easy: for each identifier, look at the previous 36 months and find the minimum and maximum,which you can compare with the present value. That doesn't assume the same structure as Stata's panel data.

. ssc inst rangestat

. rangestat (min) sellD (max) sellD if sec == "Com", interval(date -36 -1) by(personid)

. list, sepby(personid)

     +----------------------------------------------------------------------+
     | personid      date   securi~n   sellD       id   sellD_~n   sellD_~x |
     |----------------------------------------------------------------------|
  1. |    30940    1998m7        Com       0    30940          .          . |
  2. |    30940   2003m12        Com       1    30940          .          . |
  3. |    30940    2004m5        Com       1    30940          1          1 |
  4. |    30940    2006m8        Com       0    30940          1          1 |
     |----------------------------------------------------------------------|
  5. |    30941    1998m7        Com       0    30941          .          . |
  6. |    30941    2008m2        Com       1    30941          .          . |
  7. |    30941    2008m3        Com       1    30941          1          1 |
     |----------------------------------------------------------------------|
  8. |    26840    2016m1        Com       0    26840          .          . |
     |----------------------------------------------------------------------|
  9. |    27411    2005m6        Com       1    27411          .          . |
 10. |    27411   2005m11        Com       1    27411          1          1 |
     |----------------------------------------------------------------------|
 11. |    27618    1993m4        Com       0    27618          .          . |
 12. |    27618    1993m5        Com       0    27618          0          0 |
 13. |    27618   1993m10        Com       0    27618          0          0 |
 14. |    27618    1994m3        Com       0    27618          0          0 |
 15. |    27618    1996m7        Com       0    27618          0          0 |
 16. |    27618    1996m9        Com       0    27618          0          0 |
 17. |    27618    1997m4        Com       0    27618          0          0 |
 18. |    27618    1997m7        Com       0    27618          0          0 |
 19. |    27618    1998m4        Com       0    27618          0          0 |
 20. |    27618    2000m1        Com       0    27618          0          0 |
 21. |    27618    2000m2        Com       0    27618          0          0 |
 22. |    27618   2000m11        Com       0    27618          0          0 |
 23. |    27618    2001m2        Com       0    27618          0          0 |
 24. |    27618   2001m11        Com       0    27618          0          0 |
 25. |    27618    2002m1        Com       0    27618          0          0 |
 26. |    27618    2002m2        Com       0    27618          0          0 |
 27. |    27618    2002m7        Com       0    27618          0          0 |
 28. |    27618    2002m9        Com       0    27618          0          0 |
 29. |    27618    2003m1        Com       0    27618          0          0 |
 30. |    27618   2003m11        Com       0    27618          0          0 |
 31. |    27618    2004m4        Com       0    27618          0          0 |
 32. |    27618    2004m9        Com       0    27618          0          0 |
 33. |    27618   2004m10        Com       0    27618          0          0 |
 34. |    27618    2005m5        Com       0    27618          0          0 |
 35. |    27618    2005m7        Com       0    27618          0          0 |
     |----------------------------------------------------------------------|
 36. |    27623    2005m7        Com       0    27623          .          . |
 37. |    27623    2007m8        Com       0    27623          0          0 |
     |----------------------------------------------------------------------|
 38. |    27871    2000m2        Com       0    27871          .          . |
 39. |    27871    2000m5        Com       0    27871          0          0 |
 40. |    27871    2003m1        Com       0    27871          0          0 |
 41. |    27871   2003m11        Com       0    27871          0          0 |
 42. |    27871    2005m2        Com       0    27871          0          0 |
 43. |    27871   2005m11        Com       0    27871          0          0 |
 44. |    27871    2006m5        Com       0    27871          0          0 |
 45. |    27871    2006m6        Com       0    27871          0          0 |
 46. |    27871   2006m12        Com       0    27871          0          0 |
 47. |    27871    2007m8        Com       0    27871          0          0 |
 48. |    27871    2008m6        Com       0    27871          0          0 |
 49. |    27871    2008m7        Com       0    27871          0          0 |
 50. |    27871    2009m1        Com       0    27871          0          0 |
 51. |    27871    2009m2        Com       0    27871          0          0 |
     |----------------------------------------------------------------------|
 52. |    27874    2005m5        Com       0    27874          .          . |
 53. |    27874   2007m11        Com       0    27874          0          0 |
 54. |    27874    2008m7        Com       0    27874          0          0 |
 55. |    27874    2009m1        Com       0    27874          0          0 |
 56. |    27874    2009m2        Com       0    27874          0          0 |
     |----------------------------------------------------------------------|
 57. |   745822    2004m5        Com       0   745822          .          . |
 58. |   745822    2006m3        Com       1   745822          0          0 |
     |----------------------------------------------------------------------|
 59. |    18687    2009m2        Com       0    18687          .          . |
     |----------------------------------------------------------------------|
 60. |    27878    2005m2        Com       0    27878          .          . |
 61. |    27878    2009m1        Com       0    27878          .          . |
 62. |    27878    2009m2        Com       0    27878          0          0 |
     |----------------------------------------------------------------------|
 63. |    27879    2013m6        Com       1    27879          .          . |
     |----------------------------------------------------------------------|
 64. |    27882    2008m8        Com       0    27882          .          . |
 65. |    27882    2009m1        Com       0    27882          0          0 |
     |----------------------------------------------------------------------|
 66. |    27883   2004m10        Com       1    27883          .          . |
 67. |    27883    2005m3        Com       1    27883          1          1 |
 68. |    27883    2005m9        Com       1    27883          1          1 |
 69. |    27883   2006m10        Com       1    27883          1          1 |
 70. |    27883   2007m11        Com       0    27883          1          1 |
 71. |    27883    2008m5        Com       0    27883          0          1 |
 72. |    27883    2009m1        Com       0    27883          0          1 |
     |----------------------------------------------------------------------|
 73. |    29108   2005m10        Com       1    29108          .          . |
 74. |    29108    2006m1        Com       1    29108          1          1 |
 75. |    29108    2006m4        Com       1    29108          1          1 |
 76. |    29108    2006m6        Com       1    29108          1          1 |
 77. |    29108    2006m7        Com       1    29108          1          1 |
 78. |    29108    2007m4        Com       1    29108          1          1 |
 79. |    29108    2007m8        Com       1    29108          1          1 |
 80. |    29108    2009m1        Com       0    29108          1          1 |
 81. |    29108    2009m2        Com       0    29108          0          1 |
 82. |    29108    2010m8        Com       0    29108          0          1 |
     |----------------------------------------------------------------------|
 83. |    30693    2006m3        Com       1    30693          .          . |
 84. |    30693    2009m1        Com       0    30693          1          1 |
 85. |    30693    2016m2        Com       0    30693          .          . |
 86. |    30693    2017m8        Com       1    30693          0          0 |
     |----------------------------------------------------------------------|
 87. |    31004   2001m12        Com       1    31004          .          . |
 88. |    31004    2004m3        Com       1    31004          1          1 |
     |----------------------------------------------------------------------|
 89. |    29458    2002m3        Com       1    29458          .          . |
 90. |    29458   2005m10        Com       0    29458          .          . |
     |----------------------------------------------------------------------|
 91. |    29462   2003m11        Com       0    29462          .          . |
 92. |    29462   2007m10        Com       1    29462          .          . |
     |----------------------------------------------------------------------|
 93. |    27376    2006m8        Com       0    27376          .          . |
 94. |    27376   2007m10        Com       1    27376          0          0 |
     |----------------------------------------------------------------------|
 95. |    16155   2002m10        Com       0    16155          .          . |
 96. |    16155    2003m9        Com       0    16155          0          0 |
 97. |    16155    2004m8        Com       0    16155          0          0 |
 98. |    16155    2004m9        Com       0    16155          0          0 |
 99. |    16155    2005m6        Com       0    16155          0          0 |
100. |    16155    2007m1        Com       0    16155          0          0 |
     +----------------------------------------------------------------------+

[Xiaoke Ye]

I plotted your data. If they're typical then looking 12, 24, 36 months before will be futile. Most of the values will be missing.

Here's a graph. [ATTACH=CONFIG]n1508308[/ATTACH]



Here for the record is how I got it.

egen id = group(personid), label

* ssc install stripplot

stripplot date, over(id) ms(|) ytitle("") ysc(reverse) scheme(s1color)

What is easy: for each identifier, look at the previous 36 months and find the minimum and maximum,which you can compare with the present value. That doesn't assume the same structure as Stata's panel data.

. ssc inst rangestat

. rangestat (min) sellD (max) sellD if sec == "Com", interval(date -36 -1) by(personid)

. list, sepby(personid)

+----------------------------------------------------------------------+
| personid date securi~n sellD id sellD_~n sellD_~x |
|----------------------------------------------------------------------|
1. | 30940 1998m7 Com 0 30940 . . |
2. | 30940 2003m12 Com 1 30940 . . |
3. | 30940 2004m5 Com 1 30940 1 1 |
4. | 30940 2006m8 Com 0 30940 1 1 |
|----------------------------------------------------------------------|
5. | 30941 1998m7 Com 0 30941 . . |
6. | 30941 2008m2 Com 1 30941 . . |
7. | 30941 2008m3 Com 1 30941 1 1 |
|----------------------------------------------------------------------|
8. | 26840 2016m1 Com 0 26840 . . |
|----------------------------------------------------------------------|
9. | 27411 2005m6 Com 1 27411 . . |
10. | 27411 2005m11 Com 1 27411 1 1 |
|----------------------------------------------------------------------|
11. | 27618 1993m4 Com 0 27618 . . |
12. | 27618 1993m5 Com 0 27618 0 0 |
13. | 27618 1993m10 Com 0 27618 0 0 |
14. | 27618 1994m3 Com 0 27618 0 0 |
15. | 27618 1996m7 Com 0 27618 0 0 |
16. | 27618 1996m9 Com 0 27618 0 0 |
17. | 27618 1997m4 Com 0 27618 0 0 |
18. | 27618 1997m7 Com 0 27618 0 0 |
19. | 27618 1998m4 Com 0 27618 0 0 |
20. | 27618 2000m1 Com 0 27618 0 0 |
21. | 27618 2000m2 Com 0 27618 0 0 |
22. | 27618 2000m11 Com 0 27618 0 0 |
23. | 27618 2001m2 Com 0 27618 0 0 |
24. | 27618 2001m11 Com 0 27618 0 0 |
25. | 27618 2002m1 Com 0 27618 0 0 |
26. | 27618 2002m2 Com 0 27618 0 0 |
27. | 27618 2002m7 Com 0 27618 0 0 |
28. | 27618 2002m9 Com 0 27618 0 0 |
29. | 27618 2003m1 Com 0 27618 0 0 |
30. | 27618 2003m11 Com 0 27618 0 0 |
31. | 27618 2004m4 Com 0 27618 0 0 |
32. | 27618 2004m9 Com 0 27618 0 0 |
33. | 27618 2004m10 Com 0 27618 0 0 |
34. | 27618 2005m5 Com 0 27618 0 0 |
35. | 27618 2005m7 Com 0 27618 0 0 |
|----------------------------------------------------------------------|
36. | 27623 2005m7 Com 0 27623 . . |
37. | 27623 2007m8 Com 0 27623 0 0 |
|----------------------------------------------------------------------|
38. | 27871 2000m2 Com 0 27871 . . |
39. | 27871 2000m5 Com 0 27871 0 0 |
40. | 27871 2003m1 Com 0 27871 0 0 |
41. | 27871 2003m11 Com 0 27871 0 0 |
42. | 27871 2005m2 Com 0 27871 0 0 |
43. | 27871 2005m11 Com 0 27871 0 0 |
44. | 27871 2006m5 Com 0 27871 0 0 |
45. | 27871 2006m6 Com 0 27871 0 0 |
46. | 27871 2006m12 Com 0 27871 0 0 |
47. | 27871 2007m8 Com 0 27871 0 0 |
48. | 27871 2008m6 Com 0 27871 0 0 |
49. | 27871 2008m7 Com 0 27871 0 0 |
50. | 27871 2009m1 Com 0 27871 0 0 |
51. | 27871 2009m2 Com 0 27871 0 0 |
|----------------------------------------------------------------------|
52. | 27874 2005m5 Com 0 27874 . . |
53. | 27874 2007m11 Com 0 27874 0 0 |
54. | 27874 2008m7 Com 0 27874 0 0 |
55. | 27874 2009m1 Com 0 27874 0 0 |
56. | 27874 2009m2 Com 0 27874 0 0 |
|----------------------------------------------------------------------|
57. | 745822 2004m5 Com 0 745822 . . |
58. | 745822 2006m3 Com 1 745822 0 0 |
|----------------------------------------------------------------------|
59. | 18687 2009m2 Com 0 18687 . . |
|----------------------------------------------------------------------|
60. | 27878 2005m2 Com 0 27878 . . |
61. | 27878 2009m1 Com 0 27878 . . |
62. | 27878 2009m2 Com 0 27878 0 0 |
|----------------------------------------------------------------------|
63. | 27879 2013m6 Com 1 27879 . . |
|----------------------------------------------------------------------|
64. | 27882 2008m8 Com 0 27882 . . |
65. | 27882 2009m1 Com 0 27882 0 0 |
|----------------------------------------------------------------------|
66. | 27883 2004m10 Com 1 27883 . . |
67. | 27883 2005m3 Com 1 27883 1 1 |
68. | 27883 2005m9 Com 1 27883 1 1 |
69. | 27883 2006m10 Com 1 27883 1 1 |
70. | 27883 2007m11 Com 0 27883 1 1 |
71. | 27883 2008m5 Com 0 27883 0 1 |
72. | 27883 2009m1 Com 0 27883 0 1 |
|----------------------------------------------------------------------|
73. | 29108 2005m10 Com 1 29108 . . |
74. | 29108 2006m1 Com 1 29108 1 1 |
75. | 29108 2006m4 Com 1 29108 1 1 |
76. | 29108 2006m6 Com 1 29108 1 1 |
77. | 29108 2006m7 Com 1 29108 1 1 |
78. | 29108 2007m4 Com 1 29108 1 1 |
79. | 29108 2007m8 Com 1 29108 1 1 |
80. | 29108 2009m1 Com 0 29108 1 1 |
81. | 29108 2009m2 Com 0 29108 0 1 |
82. | 29108 2010m8 Com 0 29108 0 1 |
|----------------------------------------------------------------------|
83. | 30693 2006m3 Com 1 30693 . . |
84. | 30693 2009m1 Com 0 30693 1 1 |
85. | 30693 2016m2 Com 0 30693 . . |
86. | 30693 2017m8 Com 1 30693 0 0 |
|----------------------------------------------------------------------|
87. | 31004 2001m12 Com 1 31004 . . |
88. | 31004 2004m3 Com 1 31004 1 1 |
|----------------------------------------------------------------------|
89. | 29458 2002m3 Com 1 29458 . . |
90. | 29458 2005m10 Com 0 29458 . . |
|----------------------------------------------------------------------|
91. | 29462 2003m11 Com 0 29462 . . |
92. | 29462 2007m10 Com 1 29462 . . |
|----------------------------------------------------------------------|
93. | 27376 2006m8 Com 0 27376 . . |
94. | 27376 2007m10 Com 1 27376 0 0 |
|----------------------------------------------------------------------|
95. | 16155 2002m10 Com 0 16155 . . |
96. | 16155 2003m9 Com 0 16155 0 0 |
97. | 16155 2004m8 Com 0 16155 0 0 |
98. | 16155 2004m9 Com 0 16155 0 0 |
99. | 16155 2005m6 Com 0 16155 0 0 |
100. | 16155 2007m1 Com 0 16155 0 0 |
+----------------------------------------------------------------------+

Thank Nick, thanks for your time and your help

The 100 observations are unlikely representative, so I won't be bothered if the main idea behind the code does not really apply to the particular sample. The idea was to test if there is the same observation which is sellD==1, in the last three years in the same calendar month.
I will try to play around with rangstat to see if the function provides a way to solve my problem.
Thanks a lot!

Kind regards

[Nick Cox]

 
 rangestat p1 = sellD if sec == "Com", interval(date -12 -12) by(personid)  rangestat p2 = sellD if sec == "Com", interval(date -24 -24) by(personid)  rangestat p3 = sellD if sec == "Com", interval(date -36 -36) by(personid)

[Xiaoke Ye]

rangestat p1 = sellD if sec == "Com", interval(date -12 -12) by(personid) rangestat p2 = sellD if sec == "Com", interval(date -24 -24) by(personid) rangestat p3 = sellD if sec == "Com", interval(date -36 -36) by(personid)

Thanks! Then I can simply check if all p1 p2 p3 equal to 1. This thought is very helpful I will keep it in mind in the future. Thanks a lot