Hey!

I have some difficulties to write a proper code to clean my data. The goal is to keep only firms that have at least 8 consecutive quarters of complete data and that never have more than 2 quarters of negative sales growth. This means, if a firm has more than 2 consecutive quarters of negative sales growth, all observation of this firm should be dropped.

I have already dropped all firms with less than 8 consecutive quarters with the following code:
sort PERMNO qdate
tsset PERMNO qdate
tsspell, f(L.qdate == .)
egen length = max(_seq), by(PERMNO _spell)
keep if length >= 8

However, I am struggling to drop e.g. the firm with PERMNO 10016, which has 3 consecutive quarters with negative sales growth (sg).

I´ve already looked at other posts but I couldnt figure out a code that works for my data.

I would really appreciate any advice to solve my problem!

Many thanks and kind regards.


* Example generated by -dataex-. To install: ssc install dataex
clear
input double PERMNO float qdate double saleq float sg
10016 154 45.964 -.005409616
10016 155 43.197 -.06019929
10016 156 41.733 -.033891242
10016 157 48.753 .1682122
10016 158 50.193 .029536644
10016 159 49.998 -.003885004
10016 160 52.597 .05198208
10016 161 61.752 .17405936
10016 162 66.135 .07097746
10016 163 66.919 .01185454
10016 164 69.175 .0337124
10039 151 1.266 .013610888
10039 152 1.273 .005529226
10039 153 1.198 -.05891595
10039 154 1.358 .13355593
10039 155 1.312 -.033873342
10039 156 1.394 .0625
10039 157 1.314 -.05738881
10039 158 1.375 .04642314
10039 159 1.346 -.02109091
10039 160 1.542 .14561664
10039 161 1.265 -.17963684
10039 162 1.221 -.034782607
10051 150 36.644 .18397415
10051 151 38.839 .05990067
10051 152 39.165 .008393625
10051 153 40.75 .04046981
10051 154 46.9 .15092024
10051 155 48.776 .04
10051 156 51.444 .05469903
10051 157 49.145 -.04468937
10051 158 56.417 .14797029
10094 140 84.187 1.1670313
10094 141 29.958 -.6441494
10094 142 47.977 .6014754
10094 143 38.24 -.2029514
10094 144 94.09 1.4605125
10094 145 35.74 -.6201509
10094 146 54.286 .5189144
10094 147 48.537 -.10590208
10094 148 118.193 1.4351114
10094 149 47.514 -.5979965
10094 150 67.377 .4180452
10094 151 64.167 -.04764237
10108 170 623.328 .02483464
10108 171 659.915 .05869623
10108 172 701.771 .06342635
10108 173 674.561 -.03877333
10108 174 733.963 .08806024
10108 175 742.427 .011531916
10108 176 804.301 .08334018
10108 177 840.649 .04519204
10145 140 3534 .1363344
10145 141 3419 -.03254103
10145 142 3630 .06171395
10145 143 3499 -.036088154
10145 144 3798 .08545299
10145 145 3778 -.005265929
10145 146 3347 -.11408152
10145 147 3348 .000298775
10145 148 3498 .04480287
10145 149 3327 -.04888508
10145 150 3578 .07544334
10145 151 3657 .022079375
10145 152 3910 .06918239
10145 153 3646 -.06751918
10145 154 3869 .06116292
10145 155 3741 -.033083484
10145 162 6309 .04384514
10145 163 6216 -.014740846
10145 164 6454 .03828829
10145 165 5944 -.07902076
10145 166 6066 .0205249
10145 167 5789 -.04566436
10145 168 5853 .01105545
10145 169 5199 -.11173757
10145 170 5651 .0869398
10145 171 5569 -.014510706
10145 172 5855 .05135572
10145 173 5399 -.07788215
10145 174 5749 .06482682
10145 175 5768 .0033049225
10145 176 6187 .07264216
10145 177 6178 -.001454663
10145 178 6388 .033991583
10145 179 6395 .0010958046
10145 180 6640 .03831118
10145 181 6449 -.02876506
10145 182 7028 .08978136
10145 183 6900 -.018212862
10145 184 7275 .05434782
10145 185 7241 -.00467354
10145 186 7898 .09073333
10145 187 7952 .006837174
10145 188 8276 .04074447
10145 189 8041 -.02839536
10145 190 8538 .06180823
10145 191 8735 .02307332
10145 192 9275 .06182026
10147 146 545.017 .04512097
end
format %tq qdate
[/CODE]