generate a dummy indicating if a situation happens before the specific date

[Xion Lin]

Hello everyone in the community! I'm having some problems with the data, thanks in advance for all your help!

A project (identified by pid) may submit its business plan to investors with different status (identified by investor_h_position: dummy, 1 for high-status) multiple times (each different submission is identified by v1), and (the submission date is marked by bp_submission_date).

I would like to create a variable high_position_before, which divides the submissions with the same pid into a group, and identifies that, in each submissions, whether the project has been submitted to high-status investor before this submission date, and if it has been submitted to high-status investor before, the variable is 1, otherwise it is 0.

Since I've found difficult to deal with the date data, I hope that I could get suggestions and guidance from you. Thanks in advance!

Here is my sample data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(v1 pid) str10 bp_submission_date float investor_h_position
59536 10134 "2016-07-14" 1
59908 10134 "2016-07-03" 0
59866 10134 "2016-06-27" 0
 7782 10134 "2016-04-26" 1
59929 10134 "2016-08-01" 1
59861 10134 "2016-06-25" 0
60190 10134 "2016-07-29" 1
62622 10134 "2017-03-22" 1
60196 10134 "2016-08-28" 0
59943 10134 "2016-07-12" 0
62228 10137 "2016-12-28" 1
62230 10137 "2017-01-27" 1
62229 10137 "2016-12-30" 1
62231 10137 "2017-01-27" 1
62232 10137 "2017-01-27" 0
62234 10137 "2017-01-27" 1
62403 10137 "2017-02-16" 0
62233 10137 "2017-01-27" 1
53934 10155 "2016-01-29" 1
53942 10155 "2016-01-19" 1
53935 10155 "2016-01-19" 1
54917 10155 "2016-02-17" 1
53936 10155 "2016-01-21" 1
53938 10155 "2016-04-26" 1
62525 10155 "2017-03-16" 1
53937 10155 "2016-04-26" 1
59078 10157 "2016-06-15" 1
61380 10159 "2016-10-31" 0
62738 10161 "2017-04-05" 0
62739 10161 "2017-04-06" 1
60493 10162 "2016-09-10" 0
60559 10162 "2016-09-17" 1
60503 10162 "2016-09-10" 0
60487 10162 "2016-08-12" 1
60542 10162 "2016-09-17" 0
59237 10162 "2016-05-31" 0
60546 10162 "2016-09-17" 1
60502 10162 "2016-09-10" 1
60499 10162 "2016-09-10" 0
59227 10162 "2016-06-01" 0
56144 10162 "2016-03-06" 0
56186 10162 "2016-03-08" 0
60563 10162 "2016-09-17" 1
56143 10162 "2016-03-11" 1
56140 10162 "2016-03-06" 1
56182 10162 "2016-04-26" 1
60508 10162 "2016-09-10" 0
60488 10162 "2016-08-11" 0
60506 10162 "2016-09-10" 0
60548 10162 "2016-09-17" 0
60498 10162 "2016-08-29" 0
56184 10162 "2016-04-26" 0
60507 10162 "2016-09-10" 0
56162 10162 "2016-04-26" 1
56146 10162 "2016-03-07" 0
60565 10162 "2016-09-17" 0
56181 10162 "2016-04-26" 1
56151 10162 "2016-04-16" 1
56157 10162 "2016-03-07" 0
56177 10162 "2016-04-15" 0
59229 10162 "2016-06-12" 1
60485 10162 "2016-08-11" 1
59232 10162 "2016-06-28" 0
56171 10162 "2016-03-13" 0
56164 10162 "2016-04-26" 1
60564 10162 "2016-09-17" 0
60505 10162 "2016-09-10" 1
60495 10162 "2016-09-10" 0
56153 10162 "2016-04-26" 0
60504 10162 "2016-09-10" 1
60551 10162 "2016-09-17" 1
60555 10162 "2016-09-17" 1
60484 10162 "2016-08-11" 0
60501 10162 "2016-09-10" 1
56190 10162 "2016-04-26" 0
56148 10162 "2016-04-26" 1
56169 10162 "2016-04-26" 1
59236 10162 "2016-06-09" 1
56141 10162 "2016-03-06" 1
56189 10162 "2016-04-26" 1
56142 10162 "2016-03-09" 1
56166 10162 "2016-04-26" 1
56172 10162 "2016-04-26" 1
56154 10162 "2016-03-18" 1
60544 10162 "2016-09-17" 1
59235 10162 "2016-06-28" 1
56147 10162 "2016-03-08" 1
59238 10162 "2016-05-31" 0
56188 10162 "2016-04-26" 0
56139 10162 "2016-03-06" 1
56187 10162 "2016-03-06" 1
60483 10162 "2016-08-11" 0
56150 10162 "2016-03-10" 0
56145 10162 "2016-03-07" 0
60494 10162 "2016-09-10" 0
60550 10162 "2016-09-17" 0
56167 10162 "2016-04-26" 1
60489 10162 "2016-08-16" 1
60545 10162 "2016-09-17" 1
59240 10162 "2016-06-28" 0
end

[Nick Cox]

This may help. The most crucial detail to fix is that string dates are awkward to work with even when formatted so their order would be as desired.

See also Section 9 in https://www.stata-journal.com/articl...article=dm0055

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(v1 pid) str10 bp_submission_date float investor_h_position
59536 10134 "2016-07-14" 1
59908 10134 "2016-07-03" 0
59866 10134 "2016-06-27" 0
 7782 10134 "2016-04-26" 1
59929 10134 "2016-08-01" 1
59861 10134 "2016-06-25" 0
60190 10134 "2016-07-29" 1
62622 10134 "2017-03-22" 1
60196 10134 "2016-08-28" 0
59943 10134 "2016-07-12" 0
62228 10137 "2016-12-28" 1
62230 10137 "2017-01-27" 1
62229 10137 "2016-12-30" 1
62231 10137 "2017-01-27" 1
62232 10137 "2017-01-27" 0
62234 10137 "2017-01-27" 1
62403 10137 "2017-02-16" 0
62233 10137 "2017-01-27" 1
53934 10155 "2016-01-29" 1
53942 10155 "2016-01-19" 1
53935 10155 "2016-01-19" 1
54917 10155 "2016-02-17" 1
53936 10155 "2016-01-21" 1
53938 10155 "2016-04-26" 1
62525 10155 "2017-03-16" 1
53937 10155 "2016-04-26" 1
59078 10157 "2016-06-15" 1
61380 10159 "2016-10-31" 0
62738 10161 "2017-04-05" 0
62739 10161 "2017-04-06" 1
60493 10162 "2016-09-10" 0
60559 10162 "2016-09-17" 1
60503 10162 "2016-09-10" 0
60487 10162 "2016-08-12" 1
60542 10162 "2016-09-17" 0
59237 10162 "2016-05-31" 0
60546 10162 "2016-09-17" 1
60502 10162 "2016-09-10" 1
60499 10162 "2016-09-10" 0
59227 10162 "2016-06-01" 0
56144 10162 "2016-03-06" 0
56186 10162 "2016-03-08" 0
60563 10162 "2016-09-17" 1
56143 10162 "2016-03-11" 1
56140 10162 "2016-03-06" 1
56182 10162 "2016-04-26" 1
60508 10162 "2016-09-10" 0
60488 10162 "2016-08-11" 0
60506 10162 "2016-09-10" 0
60548 10162 "2016-09-17" 0
60498 10162 "2016-08-29" 0
56184 10162 "2016-04-26" 0
60507 10162 "2016-09-10" 0
56162 10162 "2016-04-26" 1
56146 10162 "2016-03-07" 0
60565 10162 "2016-09-17" 0
56181 10162 "2016-04-26" 1
56151 10162 "2016-04-16" 1
56157 10162 "2016-03-07" 0
56177 10162 "2016-04-15" 0
59229 10162 "2016-06-12" 1
60485 10162 "2016-08-11" 1
59232 10162 "2016-06-28" 0
56171 10162 "2016-03-13" 0
56164 10162 "2016-04-26" 1
60564 10162 "2016-09-17" 0
60505 10162 "2016-09-10" 1
60495 10162 "2016-09-10" 0
56153 10162 "2016-04-26" 0
60504 10162 "2016-09-10" 1
60551 10162 "2016-09-17" 1
60555 10162 "2016-09-17" 1
60484 10162 "2016-08-11" 0
60501 10162 "2016-09-10" 1
56190 10162 "2016-04-26" 0
56148 10162 "2016-04-26" 1
56169 10162 "2016-04-26" 1
59236 10162 "2016-06-09" 1
56141 10162 "2016-03-06" 1
56189 10162 "2016-04-26" 1
56142 10162 "2016-03-09" 1
56166 10162 "2016-04-26" 1
56172 10162 "2016-04-26" 1
56154 10162 "2016-03-18" 1
60544 10162 "2016-09-17" 1
59235 10162 "2016-06-28" 1
56147 10162 "2016-03-08" 1
59238 10162 "2016-05-31" 0
56188 10162 "2016-04-26" 0
56139 10162 "2016-03-06" 1
56187 10162 "2016-03-06" 1
60483 10162 "2016-08-11" 0
56150 10162 "2016-03-10" 0
56145 10162 "2016-03-07" 0
60494 10162 "2016-09-10" 0
60550 10162 "2016-09-17" 0
56167 10162 "2016-04-26" 1
60489 10162 "2016-08-16" 1
60545 10162 "2016-09-17" 1
59240 10162 "2016-06-28" 0
end

gen sdate = daily(bp_submission_date, "YMD")
format sdate %td 

bysort pid (sdate) : egen hsfirst = min(cond(investor_h_position, sdate, .))
format hsfirst %td 

gen wanted = sdate > hsfirst 

list, sepby(pid)


    +------------------------------------------------------------------------+
     |    v1     pid   bp_submi~e   invest~n       sdate     hsfirst   wanted |
     |------------------------------------------------------------------------|
  1. |  7782   10134   2016-04-26          1   26apr2016   26apr2016        0 |
  2. | 59861   10134   2016-06-25          0   25jun2016   26apr2016        1 |
  3. | 59866   10134   2016-06-27          0   27jun2016   26apr2016        1 |
  4. | 59908   10134   2016-07-03          0   03jul2016   26apr2016        1 |
  5. | 59943   10134   2016-07-12          0   12jul2016   26apr2016        1 |
  6. | 59536   10134   2016-07-14          1   14jul2016   26apr2016        1 |
  7. | 60190   10134   2016-07-29          1   29jul2016   26apr2016        1 |
  8. | 59929   10134   2016-08-01          1   01aug2016   26apr2016        1 |
  9. | 60196   10134   2016-08-28          0   28aug2016   26apr2016        1 |
 10. | 62622   10134   2017-03-22          1   22mar2017   26apr2016        1 |
     |------------------------------------------------------------------------|
 11. | 62228   10137   2016-12-28          1   28dec2016   28dec2016        0 |
 12. | 62229   10137   2016-12-30          1   30dec2016   28dec2016        1 |
 13. | 62233   10137   2017-01-27          1   27jan2017   28dec2016        1 |
 14. | 62231   10137   2017-01-27          1   27jan2017   28dec2016        1 |
 15. | 62234   10137   2017-01-27          1   27jan2017   28dec2016        1 |
 16. | 62230   10137   2017-01-27          1   27jan2017   28dec2016        1 |
 17. | 62232   10137   2017-01-27          0   27jan2017   28dec2016        1 |
 18. | 62403   10137   2017-02-16          0   16feb2017   28dec2016        1 |
     |------------------------------------------------------------------------|
 19. | 53942   10155   2016-01-19          1   19jan2016   19jan2016        0 |
 20. | 53935   10155   2016-01-19          1   19jan2016   19jan2016        0 |
 21. | 53936   10155   2016-01-21          1   21jan2016   19jan2016        1 |
 22. | 53934   10155   2016-01-29          1   29jan2016   19jan2016        1 |
 23. | 54917   10155   2016-02-17          1   17feb2016   19jan2016        1 |
 24. | 53938   10155   2016-04-26          1   26apr2016   19jan2016        1 |
 25. | 53937   10155   2016-04-26          1   26apr2016   19jan2016        1 |
 26. | 62525   10155   2017-03-16          1   16mar2017   19jan2016        1 |
     |------------------------------------------------------------------------|
 27. | 59078   10157   2016-06-15          1   15jun2016   15jun2016        0 |
     |------------------------------------------------------------------------|
 28. | 61380   10159   2016-10-31          0   31oct2016           .        0 |
     |------------------------------------------------------------------------|
 29. | 62738   10161   2017-04-05          0   05apr2017   06apr2017        0 |
 30. | 62739   10161   2017-04-06          1   06apr2017   06apr2017        0 |
     |------------------------------------------------------------------------|
 31. | 56140   10162   2016-03-06          1   06mar2016   06mar2016        0 |
 32. | 56139   10162   2016-03-06          1   06mar2016   06mar2016        0 |
 33. | 56141   10162   2016-03-06          1   06mar2016   06mar2016        0 |
 34. | 56144   10162   2016-03-06          0   06mar2016   06mar2016        0 |
 35. | 56187   10162   2016-03-06          1   06mar2016   06mar2016        0 |
 36. | 56145   10162   2016-03-07          0   07mar2016   06mar2016        1 |
 37. | 56157   10162   2016-03-07          0   07mar2016   06mar2016        1 |
 38. | 56146   10162   2016-03-07          0   07mar2016   06mar2016        1 |
 39. | 56186   10162   2016-03-08          0   08mar2016   06mar2016        1 |
 40. | 56147   10162   2016-03-08          1   08mar2016   06mar2016        1 |
 41. | 56142   10162   2016-03-09          1   09mar2016   06mar2016        1 |
 42. | 56150   10162   2016-03-10          0   10mar2016   06mar2016        1 |
 43. | 56143   10162   2016-03-11          1   11mar2016   06mar2016        1 |
 44. | 56171   10162   2016-03-13          0   13mar2016   06mar2016        1 |
 45. | 56154   10162   2016-03-18          1   18mar2016   06mar2016        1 |
 46. | 56177   10162   2016-04-15          0   15apr2016   06mar2016        1 |
 47. | 56151   10162   2016-04-16          1   16apr2016   06mar2016        1 |
 48. | 56164   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 49. | 56182   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 50. | 56189   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 51. | 56166   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 52. | 56169   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 53. | 56184   10162   2016-04-26          0   26apr2016   06mar2016        1 |
 54. | 56188   10162   2016-04-26          0   26apr2016   06mar2016        1 |
 55. | 56162   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 56. | 56181   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 57. | 56190   10162   2016-04-26          0   26apr2016   06mar2016        1 |
 58. | 56167   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 59. | 56172   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 60. | 56153   10162   2016-04-26          0   26apr2016   06mar2016        1 |
 61. | 56148   10162   2016-04-26          1   26apr2016   06mar2016        1 |
 62. | 59238   10162   2016-05-31          0   31may2016   06mar2016        1 |
 63. | 59237   10162   2016-05-31          0   31may2016   06mar2016        1 |
 64. | 59227   10162   2016-06-01          0   01jun2016   06mar2016        1 |
 65. | 59236   10162   2016-06-09          1   09jun2016   06mar2016        1 |
 66. | 59229   10162   2016-06-12          1   12jun2016   06mar2016        1 |
 67. | 59240   10162   2016-06-28          0   28jun2016   06mar2016        1 |
 68. | 59232   10162   2016-06-28          0   28jun2016   06mar2016        1 |
 69. | 59235   10162   2016-06-28          1   28jun2016   06mar2016        1 |
 70. | 60488   10162   2016-08-11          0   11aug2016   06mar2016        1 |
 71. | 60483   10162   2016-08-11          0   11aug2016   06mar2016        1 |
 72. | 60484   10162   2016-08-11          0   11aug2016   06mar2016        1 |
 73. | 60485   10162   2016-08-11          1   11aug2016   06mar2016        1 |
 74. | 60487   10162   2016-08-12          1   12aug2016   06mar2016        1 |
 75. | 60489   10162   2016-08-16          1   16aug2016   06mar2016        1 |
 76. | 60498   10162   2016-08-29          0   29aug2016   06mar2016        1 |
 77. | 60493   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 78. | 60494   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 79. | 60507   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 80. | 60499   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 81. | 60503   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 82. | 60508   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 83. | 60506   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 84. | 60502   10162   2016-09-10          1   10sep2016   06mar2016        1 |
 85. | 60495   10162   2016-09-10          0   10sep2016   06mar2016        1 |
 86. | 60504   10162   2016-09-10          1   10sep2016   06mar2016        1 |
 87. | 60505   10162   2016-09-10          1   10sep2016   06mar2016        1 |
 88. | 60501   10162   2016-09-10          1   10sep2016   06mar2016        1 |
 89. | 60565   10162   2016-09-17          0   17sep2016   06mar2016        1 |
 90. | 60555   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 91. | 60559   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 92. | 60564   10162   2016-09-17          0   17sep2016   06mar2016        1 |
 93. | 60548   10162   2016-09-17          0   17sep2016   06mar2016        1 |
 94. | 60544   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 95. | 60563   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 96. | 60545   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 97. | 60542   10162   2016-09-17          0   17sep2016   06mar2016        1 |
 98. | 60551   10162   2016-09-17          1   17sep2016   06mar2016        1 |
 99. | 60550   10162   2016-09-17          0   17sep2016   06mar2016        1 |
100. | 60546   10162   2016-09-17          1   17sep2016   06mar2016        1 |
     +------------------------------------------------------------------------+

[Hemanshu Kumar]

gen submission_date_num = daily(bp_submission_date,"YMD")
format %td submission_date_num
gen x = _n
bysort pid (submission_date_num): gen high_status_sum = sum(investor_h_position)
replace high_status_sum = high_status_sum - investor_h_position // so that the count is only upto the time before this obs
gen byte wanted = (high_status_sum > 0)
sort x
drop x high_status_sum submission_date_num

[Xion Lin]

This may help. The most crucial detail to fix is that string dates are awkward to work with even when formatted so their order would be as desired.

See also Section 9 in https://www.stata-journal.com/articl...article=dm0055

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(v1 pid) str10 bp_submission_date float investor_h_position
59536 10134 "2016-07-14" 1
59908 10134 "2016-07-03" 0
59866 10134 "2016-06-27" 0
7782 10134 "2016-04-26" 1
59929 10134 "2016-08-01" 1
59861 10134 "2016-06-25" 0
60190 10134 "2016-07-29" 1
62622 10134 "2017-03-22" 1
60196 10134 "2016-08-28" 0
59943 10134 "2016-07-12" 0
62228 10137 "2016-12-28" 1
62230 10137 "2017-01-27" 1
62229 10137 "2016-12-30" 1
62231 10137 "2017-01-27" 1
62232 10137 "2017-01-27" 0
62234 10137 "2017-01-27" 1
62403 10137 "2017-02-16" 0
62233 10137 "2017-01-27" 1
53934 10155 "2016-01-29" 1
53942 10155 "2016-01-19" 1
53935 10155 "2016-01-19" 1
54917 10155 "2016-02-17" 1
53936 10155 "2016-01-21" 1
53938 10155 "2016-04-26" 1
62525 10155 "2017-03-16" 1
53937 10155 "2016-04-26" 1
59078 10157 "2016-06-15" 1
61380 10159 "2016-10-31" 0
62738 10161 "2017-04-05" 0
62739 10161 "2017-04-06" 1
60493 10162 "2016-09-10" 0
60559 10162 "2016-09-17" 1
60503 10162 "2016-09-10" 0
60487 10162 "2016-08-12" 1
60542 10162 "2016-09-17" 0
59237 10162 "2016-05-31" 0
60546 10162 "2016-09-17" 1
60502 10162 "2016-09-10" 1
60499 10162 "2016-09-10" 0
59227 10162 "2016-06-01" 0
56144 10162 "2016-03-06" 0
56186 10162 "2016-03-08" 0
60563 10162 "2016-09-17" 1
56143 10162 "2016-03-11" 1
56140 10162 "2016-03-06" 1
56182 10162 "2016-04-26" 1
60508 10162 "2016-09-10" 0
60488 10162 "2016-08-11" 0
60506 10162 "2016-09-10" 0
60548 10162 "2016-09-17" 0
60498 10162 "2016-08-29" 0
56184 10162 "2016-04-26" 0
60507 10162 "2016-09-10" 0
56162 10162 "2016-04-26" 1
56146 10162 "2016-03-07" 0
60565 10162 "2016-09-17" 0
56181 10162 "2016-04-26" 1
56151 10162 "2016-04-16" 1
56157 10162 "2016-03-07" 0
56177 10162 "2016-04-15" 0
59229 10162 "2016-06-12" 1
60485 10162 "2016-08-11" 1
59232 10162 "2016-06-28" 0
56171 10162 "2016-03-13" 0
56164 10162 "2016-04-26" 1
60564 10162 "2016-09-17" 0
60505 10162 "2016-09-10" 1
60495 10162 "2016-09-10" 0
56153 10162 "2016-04-26" 0
60504 10162 "2016-09-10" 1
60551 10162 "2016-09-17" 1
60555 10162 "2016-09-17" 1
60484 10162 "2016-08-11" 0
60501 10162 "2016-09-10" 1
56190 10162 "2016-04-26" 0
56148 10162 "2016-04-26" 1
56169 10162 "2016-04-26" 1
59236 10162 "2016-06-09" 1
56141 10162 "2016-03-06" 1
56189 10162 "2016-04-26" 1
56142 10162 "2016-03-09" 1
56166 10162 "2016-04-26" 1
56172 10162 "2016-04-26" 1
56154 10162 "2016-03-18" 1
60544 10162 "2016-09-17" 1
59235 10162 "2016-06-28" 1
56147 10162 "2016-03-08" 1
59238 10162 "2016-05-31" 0
56188 10162 "2016-04-26" 0
56139 10162 "2016-03-06" 1
56187 10162 "2016-03-06" 1
60483 10162 "2016-08-11" 0
56150 10162 "2016-03-10" 0
56145 10162 "2016-03-07" 0
60494 10162 "2016-09-10" 0
60550 10162 "2016-09-17" 0
56167 10162 "2016-04-26" 1
60489 10162 "2016-08-16" 1
60545 10162 "2016-09-17" 1
59240 10162 "2016-06-28" 0
end

gen sdate = daily(bp_submission_date, "YMD")
format sdate %td

bysort pid (sdate) : egen hsfirst = min(cond(investor_h_position, sdate, .))
format hsfirst %td

gen wanted = sdate > hsfirst

list, sepby(pid)


+------------------------------------------------------------------------+
| v1 pid bp_submi~e invest~n sdate hsfirst wanted |
|------------------------------------------------------------------------|
1. | 7782 10134 2016-04-26 1 26apr2016 26apr2016 0 |
2. | 59861 10134 2016-06-25 0 25jun2016 26apr2016 1 |
3. | 59866 10134 2016-06-27 0 27jun2016 26apr2016 1 |
4. | 59908 10134 2016-07-03 0 03jul2016 26apr2016 1 |
5. | 59943 10134 2016-07-12 0 12jul2016 26apr2016 1 |
6. | 59536 10134 2016-07-14 1 14jul2016 26apr2016 1 |
7. | 60190 10134 2016-07-29 1 29jul2016 26apr2016 1 |
8. | 59929 10134 2016-08-01 1 01aug2016 26apr2016 1 |
9. | 60196 10134 2016-08-28 0 28aug2016 26apr2016 1 |
10. | 62622 10134 2017-03-22 1 22mar2017 26apr2016 1 |
|------------------------------------------------------------------------|
11. | 62228 10137 2016-12-28 1 28dec2016 28dec2016 0 |
12. | 62229 10137 2016-12-30 1 30dec2016 28dec2016 1 |
13. | 62233 10137 2017-01-27 1 27jan2017 28dec2016 1 |
14. | 62231 10137 2017-01-27 1 27jan2017 28dec2016 1 |
15. | 62234 10137 2017-01-27 1 27jan2017 28dec2016 1 |
16. | 62230 10137 2017-01-27 1 27jan2017 28dec2016 1 |
17. | 62232 10137 2017-01-27 0 27jan2017 28dec2016 1 |
18. | 62403 10137 2017-02-16 0 16feb2017 28dec2016 1 |
|------------------------------------------------------------------------|
19. | 53942 10155 2016-01-19 1 19jan2016 19jan2016 0 |
20. | 53935 10155 2016-01-19 1 19jan2016 19jan2016 0 |
21. | 53936 10155 2016-01-21 1 21jan2016 19jan2016 1 |
22. | 53934 10155 2016-01-29 1 29jan2016 19jan2016 1 |
23. | 54917 10155 2016-02-17 1 17feb2016 19jan2016 1 |
24. | 53938 10155 2016-04-26 1 26apr2016 19jan2016 1 |
25. | 53937 10155 2016-04-26 1 26apr2016 19jan2016 1 |
26. | 62525 10155 2017-03-16 1 16mar2017 19jan2016 1 |
|------------------------------------------------------------------------|
27. | 59078 10157 2016-06-15 1 15jun2016 15jun2016 0 |
|------------------------------------------------------------------------|
28. | 61380 10159 2016-10-31 0 31oct2016 . 0 |
|------------------------------------------------------------------------|
29. | 62738 10161 2017-04-05 0 05apr2017 06apr2017 0 |
30. | 62739 10161 2017-04-06 1 06apr2017 06apr2017 0 |
|------------------------------------------------------------------------|
31. | 56140 10162 2016-03-06 1 06mar2016 06mar2016 0 |
32. | 56139 10162 2016-03-06 1 06mar2016 06mar2016 0 |
33. | 56141 10162 2016-03-06 1 06mar2016 06mar2016 0 |
34. | 56144 10162 2016-03-06 0 06mar2016 06mar2016 0 |
35. | 56187 10162 2016-03-06 1 06mar2016 06mar2016 0 |
36. | 56145 10162 2016-03-07 0 07mar2016 06mar2016 1 |
37. | 56157 10162 2016-03-07 0 07mar2016 06mar2016 1 |
38. | 56146 10162 2016-03-07 0 07mar2016 06mar2016 1 |
39. | 56186 10162 2016-03-08 0 08mar2016 06mar2016 1 |
40. | 56147 10162 2016-03-08 1 08mar2016 06mar2016 1 |
41. | 56142 10162 2016-03-09 1 09mar2016 06mar2016 1 |
42. | 56150 10162 2016-03-10 0 10mar2016 06mar2016 1 |
43. | 56143 10162 2016-03-11 1 11mar2016 06mar2016 1 |
44. | 56171 10162 2016-03-13 0 13mar2016 06mar2016 1 |
45. | 56154 10162 2016-03-18 1 18mar2016 06mar2016 1 |
46. | 56177 10162 2016-04-15 0 15apr2016 06mar2016 1 |
47. | 56151 10162 2016-04-16 1 16apr2016 06mar2016 1 |
48. | 56164 10162 2016-04-26 1 26apr2016 06mar2016 1 |
49. | 56182 10162 2016-04-26 1 26apr2016 06mar2016 1 |
50. | 56189 10162 2016-04-26 1 26apr2016 06mar2016 1 |
51. | 56166 10162 2016-04-26 1 26apr2016 06mar2016 1 |
52. | 56169 10162 2016-04-26 1 26apr2016 06mar2016 1 |
53. | 56184 10162 2016-04-26 0 26apr2016 06mar2016 1 |
54. | 56188 10162 2016-04-26 0 26apr2016 06mar2016 1 |
55. | 56162 10162 2016-04-26 1 26apr2016 06mar2016 1 |
56. | 56181 10162 2016-04-26 1 26apr2016 06mar2016 1 |
57. | 56190 10162 2016-04-26 0 26apr2016 06mar2016 1 |
58. | 56167 10162 2016-04-26 1 26apr2016 06mar2016 1 |
59. | 56172 10162 2016-04-26 1 26apr2016 06mar2016 1 |
60. | 56153 10162 2016-04-26 0 26apr2016 06mar2016 1 |
61. | 56148 10162 2016-04-26 1 26apr2016 06mar2016 1 |
62. | 59238 10162 2016-05-31 0 31may2016 06mar2016 1 |
63. | 59237 10162 2016-05-31 0 31may2016 06mar2016 1 |
64. | 59227 10162 2016-06-01 0 01jun2016 06mar2016 1 |
65. | 59236 10162 2016-06-09 1 09jun2016 06mar2016 1 |
66. | 59229 10162 2016-06-12 1 12jun2016 06mar2016 1 |
67. | 59240 10162 2016-06-28 0 28jun2016 06mar2016 1 |
68. | 59232 10162 2016-06-28 0 28jun2016 06mar2016 1 |
69. | 59235 10162 2016-06-28 1 28jun2016 06mar2016 1 |
70. | 60488 10162 2016-08-11 0 11aug2016 06mar2016 1 |
71. | 60483 10162 2016-08-11 0 11aug2016 06mar2016 1 |
72. | 60484 10162 2016-08-11 0 11aug2016 06mar2016 1 |
73. | 60485 10162 2016-08-11 1 11aug2016 06mar2016 1 |
74. | 60487 10162 2016-08-12 1 12aug2016 06mar2016 1 |
75. | 60489 10162 2016-08-16 1 16aug2016 06mar2016 1 |
76. | 60498 10162 2016-08-29 0 29aug2016 06mar2016 1 |
77. | 60493 10162 2016-09-10 0 10sep2016 06mar2016 1 |
78. | 60494 10162 2016-09-10 0 10sep2016 06mar2016 1 |
79. | 60507 10162 2016-09-10 0 10sep2016 06mar2016 1 |
80. | 60499 10162 2016-09-10 0 10sep2016 06mar2016 1 |
81. | 60503 10162 2016-09-10 0 10sep2016 06mar2016 1 |
82. | 60508 10162 2016-09-10 0 10sep2016 06mar2016 1 |
83. | 60506 10162 2016-09-10 0 10sep2016 06mar2016 1 |
84. | 60502 10162 2016-09-10 1 10sep2016 06mar2016 1 |
85. | 60495 10162 2016-09-10 0 10sep2016 06mar2016 1 |
86. | 60504 10162 2016-09-10 1 10sep2016 06mar2016 1 |
87. | 60505 10162 2016-09-10 1 10sep2016 06mar2016 1 |
88. | 60501 10162 2016-09-10 1 10sep2016 06mar2016 1 |
89. | 60565 10162 2016-09-17 0 17sep2016 06mar2016 1 |
90. | 60555 10162 2016-09-17 1 17sep2016 06mar2016 1 |
91. | 60559 10162 2016-09-17 1 17sep2016 06mar2016 1 |
92. | 60564 10162 2016-09-17 0 17sep2016 06mar2016 1 |
93. | 60548 10162 2016-09-17 0 17sep2016 06mar2016 1 |
94. | 60544 10162 2016-09-17 1 17sep2016 06mar2016 1 |
95. | 60563 10162 2016-09-17 1 17sep2016 06mar2016 1 |
96. | 60545 10162 2016-09-17 1 17sep2016 06mar2016 1 |
97. | 60542 10162 2016-09-17 0 17sep2016 06mar2016 1 |
98. | 60551 10162 2016-09-17 1 17sep2016 06mar2016 1 |
99. | 60550 10162 2016-09-17 0 17sep2016 06mar2016 1 |
100. | 60546 10162 2016-09-17 1 17sep2016 06mar2016 1 |
+------------------------------------------------------------------------+

Thanks, it really works!

[Xion Lin]

gen submission_date_num = daily(bp_submission_date,"YMD")
format %td submission_date_num
gen x = _n
bysort pid (submission_date_num): gen high_status_sum = sum(investor_h_position)
replace high_status_sum = high_status_sum - investor_h_position // so that the count is only upto the time before this obs
gen byte wanted = (high_status_sum > 0)
sort x
drop x high_status_sum submission_date_num

Thanks! Using _n to maintain its sequence is truly a good idea