Drop panel if condition of at least one observation of panel is true

[Lea Reifenroether]

Hi together,

i use stata 16.0 and have a panel data set including banks (unique identifier is the variabel "SNLInsititutionKey"), "quarters" from 200q1-2019q4 and "assets" (in thousand, $000):

My question is now, how i can delete all oberservations of a bank (e.g. SNLInstitutionKey) if at least one of its quarterly observations have assets that are lower than $100 million.

I tried: by SNLInsitutionKey: drop if assets<=100000
But it only deletes the obeservations where assets is equal/smaller than $100 million and not all obervations of the bank which may only show in one quarter lower than $100 million assets.

Snapshot from my dataset:

Thanks a lot for your help,
Lea

[Andrew Musau]

bys SNLInsititutionKey: egen todrop= max(assets<=100000)
drop if todrop

[Wouter Wakker]

bysort SNLInstitutionKey: egen tag = max(assets <= 100000)
drop if tag

or

bysort SNLInstitutionKey (assets): drop if assets[1] <= 100000

[Lea Reifenroether]

it works perfectly. Thanks a lot Andrew and Wouter.