Conditional drop if command within a certain firm year level

[Jur Schut]

Hi,

I am working on my master thesis and I am currently busy with the data cleaning process. One of my independent variables is the annual stock return. Annual stock returns are to be calculated based on montly stock returns obtained from CRSP. As stock return data is not available every month for each firm, some studies require half of the yearly observations to be available, otherwise the firm year should be dropped. Therefore, I would like like to have a firm year level dropped if more than 6 months or more are missing (.). My data is structured as follows (note that FYPERMCO in an the identifier which is firm-year specific):

permno date permco ret fy FYPERMCO
12165 30nov2001 10043 .044108737 2001 89682
12165 31dec2001 10043 .037037145 2001 89682
12166 31oct1988 9999 . 1988 89354
12166 30nov1988 9999 . 1988 89354
12166 30dec1988 9999 0 1988 89354
12166 31jan1989 9999 -.047945205 1988 89354
12166 28feb1989 9999 -.007194245 1988 89354
12166 31mar1989 9999 -.021739131 1988 89354

Could somebody help me on this command? Help is highly appreciated. Thank you!

[William Lisowski]

Here is some code that seems to do what you want. I have expanded your example data to a third set which has 6 non-missing observations to demonstrate that it correctly does not drop that year.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(permno date permco ret fy FYPERMCO)
12165 15309 10043   .04410874 2001 89682
12165 15340 10043  .037037145 2001 89682
12166 10531  9999           . 1988 89354
12166 10561  9999           . 1988 89354
12166 10591  9999           0 1988 89354
12166 10623  9999   -.0479452 1988 89354
12166 10651  9999 -.007194245 1988 89354
12166 10682  9999  -.02173913 1988 89354
92166 10531  9999           9 1988 99354
92166 10561  9999           9 1988 99354
92166 10591  9999           0 1988 99354
92166 10623  9999   -.0479452 1988 99354
92166 10651  9999 -.007194245 1988 99354
92166 10682  9999  -.02173913 1988 99354
end
format %td date
bysort FYPERMCO: egen nm = count(ret)
generate todrop = (12-nm) > 6
sort permno date
list, sepby(FYPERMCO) noobs
drop if todrop
drop nm todrop

. list, sepby(FYPERMCO) noobs

  +-------------------------------------------------------------------------+
  | permno        date   permco         ret     fy   FYPERMCO   nm   todrop |
  |-------------------------------------------------------------------------|
  |  12165   30nov2001    10043    .0441087   2001      89682    2        1 |
  |  12165   31dec2001    10043    .0370371   2001      89682    2        1 |
  |-------------------------------------------------------------------------|
  |  12166   31oct1988     9999           .   1988      89354    4        1 |
  |  12166   30nov1988     9999           .   1988      89354    4        1 |
  |  12166   30dec1988     9999           0   1988      89354    4        1 |
  |  12166   31jan1989     9999   -.0479452   1988      89354    4        1 |
  |  12166   28feb1989     9999   -.0071942   1988      89354    4        1 |
  |  12166   31mar1989     9999   -.0217391   1988      89354    4        1 |
  |-------------------------------------------------------------------------|
  |  92166   31oct1988     9999           9   1988      99354    6        0 |
  |  92166   30nov1988     9999           9   1988      99354    6        0 |
  |  92166   30dec1988     9999           0   1988      99354    6        0 |
  |  92166   31jan1989     9999   -.0479452   1988      99354    6        0 |
  |  92166   28feb1989     9999   -.0071942   1988      99354    6        0 |
  |  92166   31mar1989     9999   -.0217391   1988      99354    6        0 |
  +-------------------------------------------------------------------------+

[Jur Schut]

Thanks a lot, it's exactly what I needed!