How to drop sequence of observations when there are missing values in the observation!

[Thomas Hunfeld]

Dear fellow stata-users. To perform a 4-factor Fama and French regression, i have to make yearly book-to-market portfolio's, based on the MarketCapitalization in June = (JuneMKT) and the Bookvalue of December of the previous year June of the current year. Therefore i did create (JundecBook). So as you can see below, i can calculate the book to market (JundecBook/JuneMKT) on the row of June (number month == 6).

NOTE: Indicate the Market Cap in june


. gen JuneMKT =MKTvaltCRSP if numbermonth ==6
(256,144 missing values generated)


NOTE: Indicate the bookvalue in December

gen DecBook = ADJCEQ if numbermonth ==12
(256,585 missing values generated)


NOTE: creating a laf for DecBook to have it on the same row as JuneMKT
by permno: gen JuneDecBook = DecBook[_n-6]
(258,691 missing values generated)

And than

. gen bTmDecJun = JunedecBook / JuneMKT
(256,144 missing values generated)

But i do endure the problem that during merging datasets previous to this stage, i deleted some observations making some monthly observations being also dropped, resulting in missing month for some years per company.
Either the month 6 is missing, which is critical for identification of the JuneMKT or month 12 indicating the DecBook. See below

Which code can help me to identify if all 12 month are present in a year, and thus also the accounting data needed for sorting stocks into the portfolios? Of course i can group the months into year per company, but how do i assign a dropping of those with less than 12 months? This should be necessary as otherwise the book-to-market value of the previous year complete with 12 months will continue to present the unfollowing incomplete year.

When i want to represent the book value from june till june:

NOTE: need to fill in the missing values in the rest of the sample year. Previous "11 month it applies to"

. replace bTmDecJun = bTmDecJun[_n-1] if bTmDecJun >= .
(256,137 real changes made)

But this don't work when there are missing values, because the complete B-t-M is than also applied to the next incomplete year without any new DecBook or JuneMKT value. See below!

So how can i identify that in a sequence of 12 months, some sequences are incomplete?

So how can i identify that in a sequence of 12 months, some sequences are incomplete?

Thank you in advance!

Yours sincerely,

Thomas Hunfeld

[Clyde Schechter]

Let me start by suggesting you read the entire Forum FAQ for excellent advice on how to post in ways that maximize your chances of getting a helpful and timely response. Among the things you will learn there are that screenshots are the least helpful of all ways to show example data, because they are often unreadable, and even when readable, cannot be imported into Stata if someone wants to test out some code. You will also be reminded that this is a multi-disciplinary forum and that a post that is sprinkled with discipline-specific jargon is likely to be ignored by people who might well be able to easily answer your Stata/statistical question but don't work in your field.

Now, it seems you have a data set with a variable called month ranging from 1 through 12, and you have sequences of this that are identified by year (and perhaps by some firm ID or asset ID or something like that). You want to be able to identify those sequences that have gaps, and then drop them. Your screenshots are readable on my computer, as it happens, but they don't seem to show any variables that fulfill the year and ID roles. So I'll just assume you have such variables somewhere in your data, called year and id, respectively. Then it's fairly simple:

by ID year (month), sort: gen gap = (month != _n)
by ID year (gap), sort: replace gap = gap[_N]
drop if gap

You seem to have asked another question about filling in gaps, but since you are removing the gaps, I don't understand what you are getting at here. I suppose I have missed something.

Finally, having year and month in separate variables is probably going to be, at best, inconvenient as you proceed with whatever you are doing. You will be better off combining them into a single Stata internal format monthly variable:

gen month_yr = mofd(mdy(month, 1, year))
format month_yr %tm

[Nick Cox]

Clyde gives excellent advice as always. I add another way to get a monthly date variable:

gen month_yr = ym(year, month)
format month_yr %tm

[Thomas Hunfeld]

Clyde Schechter: The first code you provided did the the trick indeed, despite of the unnecessary jargon you understood me and i am glad with your awnser. I also understand that in the future it would come in more handy to attach a small example dataset. Than three last questions about the previous topic, because i am concerned that i might drop to many incomplete sequences, as they maybe still contain the 6th month, needed for forming portfolios.

What should i do when I:

only wanted to drop the sequence, per 12 months, if the 6th month (number month ==6) is missing
I am having difficulty with it because it has to be done by every sequence year.

And the 2nd thing that i am worried about is although the 6th month would be available, if the 3rd is missing in the sequence for example, than my code to forward the December'book" towards next year June with:

by permno: gen JuneDecBook = DecBook[_n-6]
(258,691 missing values generated)

would refer to to the wrong month [_n-6]. As [_n-6] refers to July if march is missing.

All i need is to get the December"book" on the row with JuneMKT (numbermonth==6) in an incomplete sequence

Maybe by identifying that JuneMKT is a value in the sequence, while all the other months contain missing values. Because, JuneMKT only contains a value for june, the rest of the months are empty/missing values.
So would there be an code to place December"book"[_n-6] next to the JuneMKT. The DecBook has to be the value of the previous December, so the values that need to be pasted next to the JuneMKT vary per year.

I can also just copy the JuneMKT values into the empty spots as in the end it needs to be representing a whole year, but if in the next sequence the JuneMKT value for June (numbermonth==6) is missing; the previous year JuneMKT will just be pasted in the new year as well, and this is of course not representative for the new year.

Summing: i am glad to know how to delete incomplete sequences and really appreciate your efforts.

Due to realization that i can still keep the sequence if the JuneMKT has an value in June, how can past de previous year "DecBook" next to this value, regardless if it is the 6th or 7th up-following observation, but conditional to the fact that it contains a value and the rest of the other months don't?

OR is there another way around to get the Decbook in line with the JuneMKT when the sequence is incomplete?

And how can i delete incomplete sequences on the condition that June (numbermonth==6) is missing?

For convenience i will gladly upload an example CVS-file, but somehow an: error uploading image is displayed. Which should work looking at:

https://www.statalist.org/forums/for...tach-csv-files

Nick Cox: thank you for your response as well, i did not knew that kind of coding, but maybe it comes in handy one day and I appreciate your efforts. For now it is not really applicable as i actually separated a month-year variable into those 2 categories for the purpose of identification by year alone, but i now know how to revers this process.

Yours sincerely,

Thomas Hunfeld

[Clyde Schechter]

I don't really follow what you're doing here. But I can answer some of your discrete questions.

1. How to drop a sequence if the June observation (numbermonth= 6) is missing

by id year, sort: egen  to_drop = min(numbermonth!= 6)
drop if to_drop

2. How to create a new variable that equals the value of the preceding Decbook

// CREATE A FILE WITH JUST THE Decbook VALUES AND CORRESPONDING YEARS
preserve
keep if !missing(DecBook)
keep year DecBook
tempfile dec_books
save `dec_books'
restore
rangejoin year -1 -1 using `dec_books', prefix(prior_) by(id)
by id year, sort: replace prior_DecBook = . if missing(JuneMkt)

The dataset will now have a variable prior_DecBook that satisfies this.

Notes:

1. As no data that could be imported to Stata was provided, these codes are not tested. I believe they are right, but they may not be, and they may also contain typographical errors.

2. To run the second code, you need to install rangejoin.ado and rangestat.ado. The former is written by Robert Picard, and the latter by Robert Picard, Nick Cox, & Roberto Ferrer. Both are available from SSC.

Let me also point out that file attachments are a deprecated way of sharing data here. Downloading data from a stranger risks infecting your computer with malware. Many of us, myself included, will not do it. The way to share example data is by using the -dataex- command, as explained in FAQ #12. As I suggested earlier, please read the forum FAQ in its entirety, and especially #12, and take its advice to heart. Those guidelines are there to make the entire process of using Statalist more efficient for both questioners and responders.

[Thomas Hunfeld]

Dear Clyde Schechter, both codes work very well. I can understand that you are maybe missing the point of my reasons for performing these commands. For myself i still have to consider in which way i can implement them at best, main concern is dropping those sequences that can not produce a b-t-m, with keeping as much b-t-m data as possible, on which they need to be sorted in the next step. I am very glad full for the command codes you provided so that the coding is not the issue. I was unaware of sharing datasets by the -dataex- command and I should have read the manual indeed. Despite that i didn't you still helped me out for which i am grateful. Will keep it in mind if necessary in the future!

Greetings

Thomas

[Rodrigo Saurin]

Dear Clyde,

I have a similar issue. I tried playing with your code but could not achieve what I wanted (dataex below). I want to delete from my datatset for a given period between july year t and june year t+1 (designated by the variable panel) tickers that either do not have price in december t-1 or june year t or both (all part of panel _n-1). But the thing is I just want to remove the ticker for the following panel (year) since there might be the case that I can observe the stock price again in the near future and hence have the stock part of other panels. I tried with no success also the following since I end up also excluding ticker unnecessarily:

gen missingdec=0
gen missingjun=0
replace missingdec=1 if price==. & mon==12
replace missingjun=1 if price==. & mon==6
bysort ticker: gen dropdec=sum(missingdec)
drop if dropdec=>1
bysort ticker: gen dropjun=sum(missingjun)
drop if dropjun=>1

I would appreciate any help on this matter.

Many thanks!!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 ticker double price float(month monthly_return) long Ticker str19 Name str14 EmployerIDnumber float(mon year ME panel BEME to_drop)
"ABEV3" .08074382692575455 419     -.05048478 3 "Ambev S/A" "07526557000100" 12 1994   535705600 1 1.8755586 0
"ABEV3"        .0838712221 420      .04609925 3 "Ambev S/A" "07526557000100"  1 1995   556454720 1         . 0
"ABEV3" .07676349580287933 421     -.08474572 3 "Ambev S/A" "07526557000100"  2 1995   509297568 1         . 0
"ABEV3"        .0770478034 422     .022641506 3 "Ambev S/A" "07526557000100"  3 1995   518147488 1         . 0
"ABEV3" .08387122303247452 423      .09259254 3 "Ambev S/A" "07526557000100"  4 1995   573692352 1         . 0
"ABEV3"        .0892706617 424      .03333328 3 "Ambev S/A" "07526557000100"  5 1995   610625344 1         . 0
"ABEV3"        .0950300622 425      .06796126 3 "Ambev S/A" "07526557000100"  6 1995   650020544 1         . 0
"ABEV3"        .0964699102 426      .04684225 3 "Ambev S/A" "07526557000100"  7 1995   659869376 2         . 0
"ABEV3"        .1059700303 427      .08232353 3 "Ambev S/A" "07526557000100"  8 1995   724851648 2         . 0
"ABEV3" .10460293292999268 428    -.018263174 3 "Ambev S/A" "07526557000100"  9 1995   715500480 2         . 0
"ABEV3" .11477266252040863 429      .03947375 3 "Ambev S/A" "07526557000100" 10 1995   785063040 2         . 0
"ABEV3"        .1147726593 430      .06756764 3 "Ambev S/A" "07526557000100" 11 1995   785063040 2         . 0
"ABEV3" .11913111060857773 431      .03797462 3 "Ambev S/A" "07526557000100" 12 1995   814875520 2  1.357333 0
"ABEV3"        .1365649324 432      .14634141 3 "Ambev S/A" "07526557000100"  1 1996   934125632 2         . 0
"ABEV3"        .1600184779 433       .1792664 3 "Ambev S/A" "07526557000100"  2 1996  1094551552 2         . 0
"ABEV3"        .1632841579 434     .020408137 3 "Ambev S/A" "07526557000100"  3 1996  1129136384 2         . 0
"ABEV3"  .1683180332183838 435     .030808264 3 "Ambev S/A" "07526557000100"  4 1996  1152415104 2         . 0
"ABEV3"        .1967010761 436      .15728164 3 "Ambev S/A" "07526557000100"  5 1996  1346743936 2         . 0
"ABEV3" .20627209544181824 437      .05042009 3 "Ambev S/A" "07526557000100"  6 1996  1382946944 2         . 0
"ABEV3" .20462192595005035 438    -.015872946 3 "Ambev S/A" "07526557000100"  7 1996  1371883392 3         . 0
"ABEV3" .21122261881828308 439     .032257993 3 "Ambev S/A" "07526557000100"  8 1996  1416137600 3         . 0
"ABEV3" .21011197566986084 440    -.005258164 3 "Ambev S/A" "07526557000100"  9 1996  1549096832 3         . 0
"ABEV3"        .2111093224 441     .004746753 3 "Ambev S/A" "07526557000100" 10 1996  1556449920 3         . 0
"ABEV3"        .2044602161 442     -.03904742 3 "Ambev S/A" "07526557000100" 11 1996  1507243648 3         . 0
"ABEV3" .19947338104248047 443    -.032258037 3 "Ambev S/A" "07526557000100" 12 1996  1442136704 3  .9190912 0
"ABEV3"         .219420725 444      .08196729 3 "Ambev S/A" "07526557000100"  1 1997  1586350464 3         . 0
"ABEV3"        .2430250682 445      .10757568 3 "Ambev S/A" "07526557000100"  2 1997  1757003264 3         . 0
"ABEV3"        .2349174975 446    -.032036934 3 "Ambev S/A" "07526557000100"  3 1997  1706031232 3         . 0
"ABEV3" .24481052160263062 447              0 3 "Ambev S/A" "07526557000100"  4 1997  1777876992 3         . 0
"ABEV3"        .2619137285 448      .04832214 3 "Ambev S/A" "07526557000100"  5 1997  1902084864 3         . 0
"ABEV3"        .2756633634 449       .0675326 3 "Ambev S/A" "07526557000100"  6 1997  2002819456 3         . 0
"ABEV3"        .2749926383 450              0 3 "Ambev S/A" "07526557000100"  7 1997  1997946368 4         . 0
"ABEV3"        .2548712248 451     -.05000007 3 "Ambev S/A" "07526557000100"  8 1997  1851755136 4         . 0
"ABEV3"        .2729394372 452      .08517013 3 "Ambev S/A" "07526557000100"  9 1997  1983028992 4         . 0
"ABEV3"  .2515789568424225 453     -.08168316 3 "Ambev S/A" "07526557000100" 10 1997  1827835392 4         . 0
"ABEV3"        .2339480861 454    -.007194242 3 "Ambev S/A" "07526557000100" 11 1997  1690380672 4         . 0
"ABEV3" .23564335703849792 455     .007246374 3 "Ambev S/A" "07526557000100" 12 1997  1655037824 4  .8481617 0
"ABEV3"        .2509008501 456      .05714296 3 "Ambev S/A" "07526557000100"  1 1998  1762198656 4         . 0
"ABEV3"        .2576819476 457     .027026895 3 "Ambev S/A" "07526557000100"  2 1998  1809825536 4         . 0
"ABEV3"  .2712441682815552 458      .04849282 3 "Ambev S/A" "07526557000100"  3 1998  1928445056 4         . 0
"ABEV3"        .2333641841 459     -.13749643 3 "Ambev S/A" "07526557000100"  4 1998  1650710016 4         . 0
"ABEV3"        .2299323611 460    -.069444455 3 "Ambev S/A" "07526557000100"  5 1998  1627492736 4         . 0
"ABEV3"        .2196368719 461    -.030156095 3 "Ambev S/A" "07526557000100"  6 1998  1538359040 4         . 0
"ABEV3"        .2299323611 462      .04687508 3 "Ambev S/A" "07526557000100"  7 1998  1610469760 5         . 0
"ABEV3" .18875044584274292 463     -.17786516 3 "Ambev S/A" "07526557000100"  8 1998  1322027392 5         . 0
"ABEV3" .15786400437355042 464     -.14814818 3 "Ambev S/A" "07526557000100"  9 1998  1088115584 5         . 0
"ABEV3"         .156842988 465      .02272725 3 "Ambev S/A" "07526557000100" 10 1998  1081078016 5         . 0
"ABEV3"        .1812407843 466       .1555555 3 "Ambev S/A" "07526557000100" 11 1998  1249245568 5         . 0
"ABEV3" .18472617864608765 467    -.009345905 3 "Ambev S/A" "07526557000100" 12 1998  1270501248 5 1.1637956 0
"ABEV3"        .2164853223 468       .1719255 3 "Ambev S/A" "07526557000100"  1 1999  1488932736 5         . 0
"ABEV3"        .2168437585 469              0 3 "Ambev S/A" "07526557000100"  2 1999  1491398016 5         . 0
"ABEV3" .22580423951148987 470      .04999996 3 "Ambev S/A" "07526557000100"  3 1999  1560957440 5         . 0
"ABEV3" .21863585710525513 471     -.02243585 3 "Ambev S/A" "07526557000100"  4 1999  1511403264 5         . 0
"ABEV3"  .2284923791885376 472      .06249993 3 "Ambev S/A" "07526557000100"  5 1999  1579540224 5         . 0
"ABEV3"        .2487394934 473      .08859468 3 "Ambev S/A" "07526557000100"  6 1999  1717708160 5         . 0
"ABEV3"        .2861104828 474    -.032416835 3 "Ambev S/A" "07526557000100"  7 1999  1975779200 6         . 0
"ABEV3"        .2824888131 475    -.017632298 3 "Ambev S/A" "07526557000100"  8 1999  1950769152 6         . 0
"ABEV3"         .293353779 476      .05882352 3 "Ambev S/A" "07526557000100"  9 1999  2025798912 6         . 0
"ABEV3"        .3042187234 477        .063291 3 "Ambev S/A" "07526557000100" 10 1999  2100828416 6         . 0
"ABEV3"        .3150836894 478     .035714347 3 "Ambev S/A" "07526557000100" 11 1999  2177093120 6         . 0
"ABEV3"  .3060295581817627 479     -.03977276 3 "Ambev S/A" "07526557000100" 12 1999  2114532992 6 1.4921588 0
"ABEV3"        .3247873161 480     .035294063 3 "Ambev S/A" "07526557000100"  1 2000  2244141056 6         . 0
"ABEV3"         .295268572 481  .000024931056 3 "Ambev S/A" "07526557000100"  2 2000  2040179200 6         . 0
"ABEV3"        .3332760763 482      .10121945 3 "Ambev S/A" "07526557000100"  3 2000  2302141440 6         . 0
"ABEV3"        .3506226683 483      .04395594 3 "Ambev S/A" "07526557000100"  4 2000  2474887424 6         . 0
"ABEV3"        .3506226683 484 -.000031703352 3 "Ambev S/A" "07526557000100"  5 2000  2474887424 6         . 0
"ABEV3"        .4297895808 485      .26576084 3 "Ambev S/A" "07526557000100"  6 2000  3033691136 6         . 0
"ABEV3"        .5167070878 486      .18644065 3 "Ambev S/A" "07526557000100"  7 2000  3660118016 7         . 0
"ABEV3"        .5351609391 487     .035714425 3 "Ambev S/A" "07526557000100"  8 2000  3790836736 7         . 0
"ABEV3"        .5803393843 488      .07699279 3 "Ambev S/A" "07526557000100"  9 2000  4485568512 7         . 0
"ABEV3"        .7068236262 489       .2179488 3 "Ambev S/A" "07526557000100" 10 2000 27315961856 7         . 0
"ABEV3"        .7235641922 490     .001286974 3 "Ambev S/A" "07526557000100" 11 2000 27962918912 7         . 0
"ABEV3"  .8834404349327087 491      .20546217 3 "Ambev S/A" "07526557000100" 12 2000 34141509632 7 .10509714 0
"ABEV3"        .9774234525 492      .10638294 3 "Ambev S/A" "07526557000100"  1 2001 37773586432 7         . 0
"ABEV3"        .9210336896 493     -.09259267 3 "Ambev S/A" "07526557000100"  2 2001 35594342400 7         . 0
"ABEV3"        .9210336896 494    -.019941157 3 "Ambev S/A" "07526557000100"  3 2001 35864223744 7         . 0
"ABEV3"        .9774234525 495        .050505 3 "Ambev S/A" "07526557000100"  4 2001 38059986944 7         . 0
"ABEV3"        .9586268648 496              0 3 "Ambev S/A" "07526557000100"  5 2001 37328064512 7         . 0
"ABEV3"         .969904795 497              0 3 "Ambev S/A" "07526557000100"  6 2001 37594275840 7         . 0
"ABEV3"        .9210336896 498      -.0841122 3 "Ambev S/A" "07526557000100"  7 2001 35699990528 8         . 0
"ABEV3"        .8572532244 499     -.05183346 3 "Ambev S/A" "07526557000100"  8 2001 33227810816 8         . 0
"ABEV3"         .681260854 500     -.20529807 3 "Ambev S/A" "07526557000100"  9 2001 26406207488 8         . 0
"ABEV3"         .738051485 501       .0833611 3 "Ambev S/A" "07526557000100" 10 2001 28995825664 8         . 0
"ABEV3"        .7664184326 502              0 3 "Ambev S/A" "07526557000100" 11 2001 30110279680 8         . 0
"ABEV3"   .809943437576294 503      .07000004 3 "Ambev S/A" "07526557000100" 12 2001 31280764928 8 .11032628 0
"ABEV3"        .7657093666 504     -.05882357 3 "Ambev S/A" "07526557000100"  1 2002 29572405248 8         . 0
"ABEV3"        .7848520822 505      .03797463 3 "Ambev S/A" "07526557000100"  2 2002 30311714816 8         . 0
"ABEV3"  .7657093405723572 506     -.02439026 3 "Ambev S/A" "07526557000100"  3 2002 29285054464 8         . 0
"ABEV3"        .7810235277 507      .04081634 3 "Ambev S/A" "07526557000100"  4 2002 29870755840 8         . 0
"ABEV3"        .7733664757 508     -.03117504 3 "Ambev S/A" "07526557000100"  5 2002 29867976704 8         . 0
"ABEV3"        .7197667692 509      -.0806846 3 "Ambev S/A" "07526557000100"  6 2002 27745959936 8         . 0
"ABEV3"        .7235953238 510    -.005263211 3 "Ambev S/A" "07526557000100"  7 2002 27893544960 9         . 0
"ABEV3"        .7465665939 511     .026315724 3 "Ambev S/A" "07526557000100"  8 2002 28779053056 9         . 0
"ABEV3"        .7350809874 512      -.0278481 3 "Ambev S/A" "07526557000100"  9 2002 28299509760 9         . 0
"ABEV3"        .9060062156 513      .19820216 3 "Ambev S/A" "07526557000100" 10 2002 34879873024 9         . 0
"ABEV3"        .9320852372 514      .02659573 3 "Ambev S/A" "07526557000100" 11 2002 35883876352 9         . 0
"ABEV3"  .9233922362327576 515     .006315801 3 "Ambev S/A" "07526557000100" 12 2002 3.53272e+10 9 .11909568 0
"ABEV3"        .8673511923 516     -.04470214 3 "Ambev S/A" "07526557000100"  1 2003 33183178752 9         . 0
"ABEV3"         .850644707 517     -.01928338 3 "Ambev S/A" "07526557000100"  2 2003 32544020480 9         . 0
"ABEV3"        .9353153247 518      .10955382 3 "Ambev S/A" "07526557000100"  3 2003 35651330048 9         . 0
end
format %tm month
label values Ticker Tickers
label def Tickers 3 "ABEV3", modify

[Clyde Schechter]

You've actually already done the hard part, which is creating that variable panel. With that in place, all you need is:

by Ticker panel, sort: egen to_drop = max(missing(price) & inlist(mon, 6, 12))
drop if to_drop

Note: Your example data does not actually contain any missing values of price, neither in the critical June or December, nor any others. I created a different set of data (not shown) by randomly replacing some values of price by missing value to test this code, so I'm confident it works (assuming I have correctly understood what you want to do.)

[Rodrigo Saurin]

Dear Clyde,

Thank you for your help. But the code did not work (I am pasting another dataex that displays the issue). If you look at ticker AESL3 on panel 8 one can see prices dec 2001 and jun 2002. However, the code assigns 1 to panel 9 when in fact it should be zero. On the other hand, in jun 2003 (panel 9) we have missing price, but the code assigns 0 to panel 10 when in fact this stock should be excluded.

In a nutshell, if a ticker is missing in december year t-1 or june of year t (same panel since each panel runs from jul-jun) that ticker should be excluded just for the following panel.

Many thanks,

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 ticker double price float(month monthly_return) long Ticker str19 Name str14 EmployerIDnumber float(mon year ME panel BEME to_drop)
"AESL3"   918.883544921875 480             0 11 "AES Sul" "02016440000162"  1 2000  4.935907e+11  6             . 0
"AESL3"                  . 483             0 11 "AES Sul" "02016440000162"  4 2000             .  6             . 0
"AESL3"                  . 482             0 11 "AES Sul" "02016440000162"  3 2000             .  6             . 0
"AESL3" 1199.6534423828125 484       .516129 11 "AES Sul" "02016440000162"  5 2000  644409458688  6             . 0
"AESL3"  969.9325561523438 481             0 11 "AES Sul" "02016440000162"  2 2000  5.210123e+11  6             . 0
"AESL3" 1659.0950927734375 485    -.05797107 11 "AES Sul" "02016440000162"  6 2000  8.912045e+11  6             . 0
"AESL3"  1914.340576171875 487             0 11 "AES Sul" "02016440000162"  8 2000  1.028313e+12  7             . 1
"AESL3"  3455.257568359375 496             0 11 "AES Sul" "02016440000162"  5 2001 1.8560365e+12  7             . 1
"AESL3"  2501.405029296875 491             0 11 "AES Sul" "02016440000162" 12 2000  1.343662e+12  7   .0000769829 1
"AESL3"                  . 495             0 11 "AES Sul" "02016440000162"  4 2001             .  7             . 1
"AESL3"   3494.08056640625 493             0 11 "AES Sul" "02016440000162"  2 2001  1.876891e+12  7             . 1
"AESL3"                  . 497             0 11 "AES Sul" "02016440000162"  6 2001             .  7             . 1
"AESL3"    1556.9970703125 486    -.10294116 11 "AES Sul" "02016440000162"  7 2000  8.363613e+11  7             . 1
"AESL3"   3494.08056640625 492      -.174312 11 "AES Sul" "02016440000162"  1 2001  1.876891e+12  7             . 1
"AESL3"                  . 490             0 11 "AES Sul" "02016440000162" 11 2000             .  7             . 1
"AESL3"  2169.586181640625 488             0 11 "AES Sul" "02016440000162"  9 2000 1.1654214e+12  7             . 1
"AESL3"                  . 494             0 11 "AES Sul" "02016440000162"  3 2001             .  7             . 1
"AESL3"                  . 489             0 11 "AES Sul" "02016440000162" 10 2000             .  7             . 1
"AESL3"                  . 502             0 11 "AES Sul" "02016440000162" 11 2001             .  8             . 0
"AESL3"  3727.019287109375 504     .19999993 11 "AES Sul" "02016440000162"  1 2002 2.0022105e+12  8             . 0
"AESL3"                  . 500             0 11 "AES Sul" "02016440000162"  9 2001             .  8             . 0
"AESL3"        3377.611342 505    -.12999998 11 "AES Sul" "02016440000162"  2 2002 1.8145034e+12  8             . 0
"AESL3"     4619.951171875 499     .41666675 11 "AES Sul" "02016440000162"  8 2001  2.481907e+12  8             . 0
"AESL3"   2911.73388671875 509             0 11 "AES Sul" "02016440000162"  6 2002  1.564227e+12  8             . 0
"AESL3"                  . 507             0 11 "AES Sul" "02016440000162"  4 2002             .  8             . 0
"AESL3"     3105.849609375 503    -.11111105 11 "AES Sul" "02016440000162" 12 2001  1.668509e+12  8  .00027586726 0
"AESL3"    5202.2978515625 498             0 11 "AES Sul" "02016440000162"  7 2001  2.794752e+12  8             . 0
"AESL3"                  . 501             0 11 "AES Sul" "02016440000162" 10 2001             .  8             . 0
"AESL3"     3105.849609375 506    -.09090903 11 "AES Sul" "02016440000162"  3 2002  1.668509e+12  8             . 0
"AESL3"   2911.73388671875 508    -.08536582 11 "AES Sul" "02016440000162"  5 2002  1.564227e+12  8             . 0
"AESL3"                  . 519             0 11 "AES Sul" "02016440000162"  4 2003             .  9             . 1
"AESL3"                  . 512             0 11 "AES Sul" "02016440000162"  9 2002             .  9             . 1
"AESL3"                  . 510             0 11 "AES Sul" "02016440000162"  7 2002             .  9             . 1
"AESL3"     3105.849609375 513    .012658268 11 "AES Sul" "02016440000162" 10 2002  1.668509e+12  9             . 1
"AESL3"   2329.38720703125 518             0 11 "AES Sul" "02016440000162"  3 2003 1.2513817e+12  9             . 1
"AESL3"                  . 517             0 11 "AES Sul" "02016440000162"  2 2003             .  9             . 1
"AESL3"                  . 516             0 11 "AES Sul" "02016440000162"  1 2003             .  9             . 1
"AESL3"   2911.73388671875 511             0 11 "AES Sul" "02016440000162"  8 2002  1.564227e+12  9             . 1
"AESL3"   2251.74072265625 520             0 11 "AES Sul" "02016440000162"  5 2003 1.2096689e+12  9             . 1
"AESL3"    3067.0263671875 515             0 11 "AES Sul" "02016440000162" 12 2002 1.6476525e+12  9  6.460877e-11 1
"AESL3"   2911.73388671875 514     -.0384615 11 "AES Sul" "02016440000162" 11 2002  1.564227e+12  9             . 1
"AESL3"                  . 521             0 11 "AES Sul" "02016440000162"  6 2003             .  9             . 1
"AESL3"      7725.80078125 527        1.4875 11 "AES Sul" "02016440000162" 12 2003  4.150416e+12 10 1.8706607e-10 0
"AESL3"   8152.85498046875 531     .22807015 11 "AES Sul" "02016440000162"  4 2004    1094958976 10             . 0
"AESL3"  1785.863525390625 522           .15 11 "AES Sul" "02016440000162"  7 2003  9.593926e+11 10             . 0
"AESL3"    8249.9130859375 533 -1.183723e-06 11 "AES Sul" "02016440000162"  6 2004    1105488384 10             . 0
"AESL3"        1941.155933 523      .3513513 11 "AES Sul" "02016440000162"  8 2003  1.042818e+12 10             . 0
"AESL3"  3261.141845703125 526             0 11 "AES Sul" "02016440000162" 11 2003  1.751934e+12 10             . 0
"AESL3"  4076.427490234375 528     -.4198895 11 "AES Sul" "02016440000162"  1 2004  2.189918e+12 10             . 0
"AESL3"        6988.161358 530      .9148936 11 "AES Sul" "02016440000162"  3 2004  3.754145e+12 10             . 0
"AESL3"  2887.459716796875 532             0 11 "AES Sul" "02016440000162"  5 2004     387796672 10             . 0
"AESL3" 1941.1558837890625 525             0 11 "AES Sul" "02016440000162" 10 2003  1.042818e+12 10             . 0
"AESL3"    3688.1962890625 529    -.04999997 11 "AES Sul" "02016440000162"  2 2004 1.9813543e+12 10             . 0
"AESL3"   1979.97900390625 524             0 11 "AES Sul" "02016440000162"  9 2003 1.0636744e+12 10             . 0
"AESL3"    9172.9326171875 538    .030081784 11 "AES Sul" "02016440000162" 11 2004    1229172992 11             . 1
"AESL3"                  . 541             0 11 "AES Sul" "02016440000162"  2 2005             . 11             . 1
"AESL3"      9785.26953125 543             0 11 "AES Sul" "02016440000162"  4 2005    1311226112 11             . 1
"AESL3"     8346.970703125 535             0 11 "AES Sul" "02016440000162"  8 2004    1118494080 11             . 1
"AESL3"                  . 542             0 11 "AES Sul" "02016440000162"  3 2005             . 11             . 1
"AESL3"   8008.23876953125 536     .01239263 11 "AES Sul" "02016440000162"  9 2004    1073104000 11             . 1
"AESL3"   8152.85498046875 534             0 11 "AES Sul" "02016440000162"  7 2004    1092482560 11             . 1
"AESL3"                  . 540             0 11 "AES Sul" "02016440000162"  1 2005             . 11             . 1
"AESL3"   8060.64990234375 537    .000602366 11 "AES Sul" "02016440000162" 10 2004    1080127104 11             . 1
"AESL3"                  . 545             0 11 "AES Sul" "02016440000162"  6 2005             . 11             . 1
"AESL3"     9317.548828125 539    -.01030926 11 "AES Sul" "02016440000162" 12 2004    1248551552 11   7.32725e-07 1
"AESL3"                  . 544             0 11 "AES Sul" "02016440000162"  5 2005             . 11             . 1
"AESL3"                  . 548             0 11 "AES Sul" "02016440000162"  9 2005             . 12             . 1
"AESL3"                  . 550             0 11 "AES Sul" "02016440000162" 11 2005             . 12             . 1
"AESL3"                  . 552             0 11 "AES Sul" "02016440000162"  1 2006             . 12             . 1
"AESL3"                  . 551             0 11 "AES Sul" "02016440000162" 12 2005             . 12             . 1
"AESL3"                  . 557             0 11 "AES Sul" "02016440000162"  6 2006             . 12             . 1
"AESL3"                  . 549             0 11 "AES Sul" "02016440000162" 10 2005             . 12             . 1
"AESL3"                  . 554             0 11 "AES Sul" "02016440000162"  3 2006             . 12             . 1
"AESL3"                  . 556             0 11 "AES Sul" "02016440000162"  5 2006             . 12             . 1
"AESL3"                  . 547             0 11 "AES Sul" "02016440000162"  8 2005             . 12             . 1
"AESL3"                  . 555             0 11 "AES Sul" "02016440000162"  4 2006             . 12             . 1
"AESL3"                  . 546             0 11 "AES Sul" "02016440000162"  7 2005             . 12             . 1
"AESL3"                  . 553             0 11 "AES Sul" "02016440000162"  2 2006             . 12             . 1
"AESL3"                  . 563             0 11 "AES Sul" "02016440000162" 12 2006             . 13             . 1
"AESL3"                  . 566             0 11 "AES Sul" "02016440000162"  3 2007             . 13             . 1
"AESL3"                  . 565             0 11 "AES Sul" "02016440000162"  2 2007             . 13             . 1
"AESL3"                  . 569             0 11 "AES Sul" "02016440000162"  6 2007             . 13             . 1
"AESL3"                  . 561             0 11 "AES Sul" "02016440000162" 10 2006             . 13             . 1
"AESL3"                  . 564             0 11 "AES Sul" "02016440000162"  1 2007             . 13             . 1
"AESL3"                  . 560             0 11 "AES Sul" "02016440000162"  9 2006             . 13             . 1
"AESL3"                  . 562             0 11 "AES Sul" "02016440000162" 11 2006             . 13             . 1
"AESL3"                  . 559             0 11 "AES Sul" "02016440000162"  8 2006             . 13             . 1
"AESL3"                  . 558             0 11 "AES Sul" "02016440000162"  7 2006             . 13             . 1
"AESL3"                  . 568             0 11 "AES Sul" "02016440000162"  5 2007             . 13             . 1
"AESL3"                  . 567             0 11 "AES Sul" "02016440000162"  4 2007             . 13             . 1
"AESL3"                  . 574             0 11 "AES Sul" "02016440000162" 11 2007             . 14             . 1
"AESL3"                  . 579             0 11 "AES Sul" "02016440000162"  4 2008             . 14             . 1
"AESL3"                  . 571             0 11 "AES Sul" "02016440000162"  8 2007             . 14             . 1
"AESL3"                  . 580             0 11 "AES Sul" "02016440000162"  5 2008             . 14             . 1
"AESL3"                  . 578             0 11 "AES Sul" "02016440000162"  3 2008             . 14             . 1
"AESL3"                  . 570             0 11 "AES Sul" "02016440000162"  7 2007             . 14             . 1
"AESL3"                  . 575             0 11 "AES Sul" "02016440000162" 12 2007             . 14             . 1
"AESL3"                  . 573             0 11 "AES Sul" "02016440000162" 10 2007             . 14             . 1
"AESL3"                  . 572             0 11 "AES Sul" "02016440000162"  9 2007             . 14             . 1
"AESL3"                  . 576             0 11 "AES Sul" "02016440000162"  1 2008             . 14             . 1
end
format %tm month
label values Ticker Tickers
label def Tickers 11 "AESL3", modify

[Clyde Schechter]

Oh, sorry. I misunderstood your original request. I thought you wanted to drop the current panel if December of June prices were missing. You want to drop the following panel. That's a bit harder, but not too bad:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 ticker double price float(month monthly_return) long Ticker str19 Name str14 EmployerIDnumber float(mon year ME panel BEME)
"AESL3"   918.883544921875 480             0 11 "AES Sul" "02016440000162"  1 2000  4.935907e+11  6             .
"AESL3"                  . 483             0 11 "AES Sul" "02016440000162"  4 2000             .  6             .
"AESL3"                  . 482             0 11 "AES Sul" "02016440000162"  3 2000             .  6             .
"AESL3" 1199.6534423828125 484       .516129 11 "AES Sul" "02016440000162"  5 2000  644409458688  6             .
"AESL3"  969.9325561523438 481             0 11 "AES Sul" "02016440000162"  2 2000  5.210123e+11  6             .
"AESL3" 1659.0950927734375 485    -.05797107 11 "AES Sul" "02016440000162"  6 2000  8.912045e+11  6             .
"AESL3"  1914.340576171875 487             0 11 "AES Sul" "02016440000162"  8 2000  1.028313e+12  7             .
"AESL3"  3455.257568359375 496             0 11 "AES Sul" "02016440000162"  5 2001 1.8560365e+12  7             .
"AESL3"  2501.405029296875 491             0 11 "AES Sul" "02016440000162" 12 2000  1.343662e+12  7   .0000769829
"AESL3"                  . 495             0 11 "AES Sul" "02016440000162"  4 2001             .  7             .
"AESL3"   3494.08056640625 493             0 11 "AES Sul" "02016440000162"  2 2001  1.876891e+12  7             .
"AESL3"                  . 497             0 11 "AES Sul" "02016440000162"  6 2001             .  7             .
"AESL3"    1556.9970703125 486    -.10294116 11 "AES Sul" "02016440000162"  7 2000  8.363613e+11  7             .
"AESL3"   3494.08056640625 492      -.174312 11 "AES Sul" "02016440000162"  1 2001  1.876891e+12  7             .
"AESL3"                  . 490             0 11 "AES Sul" "02016440000162" 11 2000             .  7             .
"AESL3"  2169.586181640625 488             0 11 "AES Sul" "02016440000162"  9 2000 1.1654214e+12  7             .
"AESL3"                  . 494             0 11 "AES Sul" "02016440000162"  3 2001             .  7             .
"AESL3"                  . 489             0 11 "AES Sul" "02016440000162" 10 2000             .  7             .
"AESL3"                  . 502             0 11 "AES Sul" "02016440000162" 11 2001             .  8             .
"AESL3"  3727.019287109375 504     .19999993 11 "AES Sul" "02016440000162"  1 2002 2.0022105e+12  8             .
"AESL3"                  . 500             0 11 "AES Sul" "02016440000162"  9 2001             .  8             .
"AESL3"        3377.611342 505    -.12999998 11 "AES Sul" "02016440000162"  2 2002 1.8145034e+12  8             .
"AESL3"     4619.951171875 499     .41666675 11 "AES Sul" "02016440000162"  8 2001  2.481907e+12  8             .
"AESL3"   2911.73388671875 509             0 11 "AES Sul" "02016440000162"  6 2002  1.564227e+12  8             .
"AESL3"                  . 507             0 11 "AES Sul" "02016440000162"  4 2002             .  8             .
"AESL3"     3105.849609375 503    -.11111105 11 "AES Sul" "02016440000162" 12 2001  1.668509e+12  8  .00027586726
"AESL3"    5202.2978515625 498             0 11 "AES Sul" "02016440000162"  7 2001  2.794752e+12  8             .
"AESL3"                  . 501             0 11 "AES Sul" "02016440000162" 10 2001             .  8             .
"AESL3"     3105.849609375 506    -.09090903 11 "AES Sul" "02016440000162"  3 2002  1.668509e+12  8             .
"AESL3"   2911.73388671875 508    -.08536582 11 "AES Sul" "02016440000162"  5 2002  1.564227e+12  8             .
"AESL3"                  . 519             0 11 "AES Sul" "02016440000162"  4 2003             .  9             .
"AESL3"                  . 512             0 11 "AES Sul" "02016440000162"  9 2002             .  9             .
"AESL3"                  . 510             0 11 "AES Sul" "02016440000162"  7 2002             .  9             .
"AESL3"     3105.849609375 513    .012658268 11 "AES Sul" "02016440000162" 10 2002  1.668509e+12  9             .
"AESL3"   2329.38720703125 518             0 11 "AES Sul" "02016440000162"  3 2003 1.2513817e+12  9             .
"AESL3"                  . 517             0 11 "AES Sul" "02016440000162"  2 2003             .  9             .
"AESL3"                  . 516             0 11 "AES Sul" "02016440000162"  1 2003             .  9             .
"AESL3"   2911.73388671875 511             0 11 "AES Sul" "02016440000162"  8 2002  1.564227e+12  9             .
"AESL3"   2251.74072265625 520             0 11 "AES Sul" "02016440000162"  5 2003 1.2096689e+12  9             .
"AESL3"    3067.0263671875 515             0 11 "AES Sul" "02016440000162" 12 2002 1.6476525e+12  9  6.460877e-11
"AESL3"   2911.73388671875 514     -.0384615 11 "AES Sul" "02016440000162" 11 2002  1.564227e+12  9             .
"AESL3"                  . 521             0 11 "AES Sul" "02016440000162"  6 2003             .  9             .
"AESL3"      7725.80078125 527        1.4875 11 "AES Sul" "02016440000162" 12 2003  4.150416e+12 10 1.8706607e-10
"AESL3"   8152.85498046875 531     .22807015 11 "AES Sul" "02016440000162"  4 2004    1094958976 10             .
"AESL3"  1785.863525390625 522           .15 11 "AES Sul" "02016440000162"  7 2003  9.593926e+11 10             .
"AESL3"    8249.9130859375 533 -1.183723e-06 11 "AES Sul" "02016440000162"  6 2004    1105488384 10             .
"AESL3"        1941.155933 523      .3513513 11 "AES Sul" "02016440000162"  8 2003  1.042818e+12 10             .
"AESL3"  3261.141845703125 526             0 11 "AES Sul" "02016440000162" 11 2003  1.751934e+12 10             .
"AESL3"  4076.427490234375 528     -.4198895 11 "AES Sul" "02016440000162"  1 2004  2.189918e+12 10             .
"AESL3"        6988.161358 530      .9148936 11 "AES Sul" "02016440000162"  3 2004  3.754145e+12 10             .
"AESL3"  2887.459716796875 532             0 11 "AES Sul" "02016440000162"  5 2004     387796672 10             .
"AESL3" 1941.1558837890625 525             0 11 "AES Sul" "02016440000162" 10 2003  1.042818e+12 10             .
"AESL3"    3688.1962890625 529    -.04999997 11 "AES Sul" "02016440000162"  2 2004 1.9813543e+12 10             .
"AESL3"   1979.97900390625 524             0 11 "AES Sul" "02016440000162"  9 2003 1.0636744e+12 10             .
"AESL3"    9172.9326171875 538    .030081784 11 "AES Sul" "02016440000162" 11 2004    1229172992 11             .
"AESL3"                  . 541             0 11 "AES Sul" "02016440000162"  2 2005             . 11             .
"AESL3"      9785.26953125 543             0 11 "AES Sul" "02016440000162"  4 2005    1311226112 11             .
"AESL3"     8346.970703125 535             0 11 "AES Sul" "02016440000162"  8 2004    1118494080 11             .
"AESL3"                  . 542             0 11 "AES Sul" "02016440000162"  3 2005             . 11             .
"AESL3"   8008.23876953125 536     .01239263 11 "AES Sul" "02016440000162"  9 2004    1073104000 11             .
"AESL3"   8152.85498046875 534             0 11 "AES Sul" "02016440000162"  7 2004    1092482560 11             .
"AESL3"                  . 540             0 11 "AES Sul" "02016440000162"  1 2005             . 11             .
"AESL3"   8060.64990234375 537    .000602366 11 "AES Sul" "02016440000162" 10 2004    1080127104 11             .
"AESL3"                  . 545             0 11 "AES Sul" "02016440000162"  6 2005             . 11             .
"AESL3"     9317.548828125 539    -.01030926 11 "AES Sul" "02016440000162" 12 2004    1248551552 11   7.32725e-07
"AESL3"                  . 544             0 11 "AES Sul" "02016440000162"  5 2005             . 11             .
"AESL3"                  . 548             0 11 "AES Sul" "02016440000162"  9 2005             . 12             .
"AESL3"                  . 550             0 11 "AES Sul" "02016440000162" 11 2005             . 12             .
"AESL3"                  . 552             0 11 "AES Sul" "02016440000162"  1 2006             . 12             .
"AESL3"                  . 551             0 11 "AES Sul" "02016440000162" 12 2005             . 12             .
"AESL3"                  . 557             0 11 "AES Sul" "02016440000162"  6 2006             . 12             .
"AESL3"                  . 549             0 11 "AES Sul" "02016440000162" 10 2005             . 12             .
"AESL3"                  . 554             0 11 "AES Sul" "02016440000162"  3 2006             . 12             .
"AESL3"                  . 556             0 11 "AES Sul" "02016440000162"  5 2006             . 12             .
"AESL3"                  . 547             0 11 "AES Sul" "02016440000162"  8 2005             . 12             .
"AESL3"                  . 555             0 11 "AES Sul" "02016440000162"  4 2006             . 12             .
"AESL3"                  . 546             0 11 "AES Sul" "02016440000162"  7 2005             . 12             .
"AESL3"                  . 553             0 11 "AES Sul" "02016440000162"  2 2006             . 12             .
"AESL3"                  . 563             0 11 "AES Sul" "02016440000162" 12 2006             . 13             .
"AESL3"                  . 566             0 11 "AES Sul" "02016440000162"  3 2007             . 13             .
"AESL3"                  . 565             0 11 "AES Sul" "02016440000162"  2 2007             . 13             .
"AESL3"                  . 569             0 11 "AES Sul" "02016440000162"  6 2007             . 13             .
"AESL3"                  . 561             0 11 "AES Sul" "02016440000162" 10 2006             . 13             .
"AESL3"                  . 564             0 11 "AES Sul" "02016440000162"  1 2007             . 13             .
"AESL3"                  . 560             0 11 "AES Sul" "02016440000162"  9 2006             . 13             .
"AESL3"                  . 562             0 11 "AES Sul" "02016440000162" 11 2006             . 13             .
"AESL3"                  . 559             0 11 "AES Sul" "02016440000162"  8 2006             . 13             .
"AESL3"                  . 558             0 11 "AES Sul" "02016440000162"  7 2006             . 13             .
"AESL3"                  . 568             0 11 "AES Sul" "02016440000162"  5 2007             . 13             .
"AESL3"                  . 567             0 11 "AES Sul" "02016440000162"  4 2007             . 13             .
"AESL3"                  . 574             0 11 "AES Sul" "02016440000162" 11 2007             . 14             .
"AESL3"                  . 579             0 11 "AES Sul" "02016440000162"  4 2008             . 14             .
"AESL3"                  . 571             0 11 "AES Sul" "02016440000162"  8 2007             . 14             .
"AESL3"                  . 580             0 11 "AES Sul" "02016440000162"  5 2008             . 14             .
"AESL3"                  . 578             0 11 "AES Sul" "02016440000162"  3 2008             . 14             .
"AESL3"                  . 570             0 11 "AES Sul" "02016440000162"  7 2007             . 14             .
"AESL3"                  . 575             0 11 "AES Sul" "02016440000162" 12 2007             . 14             .
"AESL3"                  . 573             0 11 "AES Sul" "02016440000162" 10 2007             . 14             .
"AESL3"                  . 572             0 11 "AES Sul" "02016440000162"  9 2007             . 14             .
"AESL3"                  . 576             0 11 "AES Sul" "02016440000162"  1 2008             . 14             .
end
format %tm month
label values Ticker Tickers
label def Tickers 11 "AESL3", modify


preserve
keep ticker price mon panel
keep if inlist(mon, 6, 12)
by ticker panel (price), sort: gen byte dropper = missing(price[_N])
by ticker panel: keep if _n == 1
keep ticker panel dropper
tempfile droppers
save `droppers'

restore
rangejoin panel -1 -1 using `droppers', by(ticker)
drop if dropper == 1

So this code begins by pulling out from the file the June and December observations within each panel, and then designating that panel as a dropper (meaning not that it will be dropped but that it will cause the next panel to be dropped) if either one has a missing value of price. This is reduced to one observation per panel and stored in a temporary file.

The original data is then restored and the -rangejoin- command matches up each observation in the original data with the information in the temporary file from the preceding panel. Then we drop those panels that matched up with dropper == 1. Note that you cannot in this case abbreviate -drop if dropper == 1- to -drop if dropper-. The reason for this is that the first panel within each ticker will not match with anything under -rangejoin- (because there is, necessarily, no previous panel in the data). Consequently the first panel will always have a missing value for dropper, and -drop if dropper- would then cause the first panel to always drop. (Remember that in Stata, missing value is interpreted as true in logical expressions.)

-rangejoin- is written by Robert Picard and is available from SSC.

[Rodrigo Saurin]

Many thanks Clyde!!! It works!!!

[Forster Shitsi]

I have a similar challenge.
I have a panel dataset with lots of missing values for an important variable.
In the data, I have ID, Year, and Variables (say, V1, V2, V3, ...)

So let's say this variable of concern is V1.
The Year is from 2010 to 2015 (that makes the time period 6, hence, 50% missing means that it has missing values for at least 3 years).
I want to drop all companies (IDs) that have missing values for V1 for at least 3 years.

I will be glad for a code to accomplish this.
Thanks

[Nick Cox]

#12

bysort ID : egen nmissing = total(missing(V1)) 
drop in nmissing >= 3

[Forster Shitsi]

Thanks Nick. It worked.