Drop observations under multiple conditions in a sequence of years

[Fleur Ponne]

Dear Statalist users,

I hope you can help me with the following:
I have an unbalanced panel dataset consisting of data on firms for the years 2012-2017. For each firm I have data on whether they make use of Assurance Services in a certain year or not. (take note: Not all firms have data in each of the five years). See data sample below!
I want to see if the use of External Assurance has an effect on Return on Assets (roa).

Now I would like to drop all observation where a "No" follows a "Yes" for External Assurance in a subsequent year for a firm. (See ABN AMRO Holding and Acea in sample below for examples)
That is because I would like to test the treatment effect of external assurance. I can only use a firm in the treatment group if it first has "No" in at least one year, followed by "Yes" in at least one subsequent year, to see if treatment has an effect on return on assets.

I have tried for instance the following command: drop if (year==2015 & ExternalAssurancedum==1 & year==2016 & ExternalAssurancedum==0)
However this does not delete any observations, whereas you can see that this is the case for instance for ABN AMRO Holding.
I am thus looking for a command in which I can tell Stata do drop observations if ExternalAssurancedum==0 follows ExternalAssurancedum==1 in a firmid group, so to say.

One more important thing to mention is that I do not wish to drop all years for a firm that has a "No" following a "Yes", but I only want to drop the years starting from the year that has a "No" if a previous year for that same firm had a "Yes".

Does anyone have tips?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 name long gvkey float(firmid year) str3 ExternalAssurance float(ExternalAssurancedum roa)
"AAREAL BANK"        251978 579 2013 "No"  0   .002722133
"AAREAL BANK"        251978 579 2014 "No"  0   .005892205
"AAREAL BANK"        251978 579 2015 "No"  0   .006525756
"AAREAL BANK"        251978 579 2016 "Yes" 1   .004171208
"ABENGOA"            221636 449 2012 "Yes" 1   .006104115
"ABENGOA"            221636 449 2013 "Yes" 1   .004823938
"ABENGOA"            221636 449 2014 "Yes" 1   .005850611
"ABENGOA"            221636 449 2015 "Yes" 1   -.07189515
"ABENGOA"            221636 449 2016 "Yes" 1    -.4312886
"ABERDEEN ASSET MGT" 200015 290 2012 "No"  0    .05487056
"ABERDEEN ASSET MGT" 200015 290 2013 "No"  0    .07364739
"ABERDEEN ASSET MGT" 200015 290 2014 "No"  0    .05608954
"ABERDEEN ASSET MGT" 200015 290 2015 "No"  0    .06265346
"ABERDEEN ASSET MGT" 200015 290 2016 "No"  0   .034271345
"ABN AMRO HOLDING"    15504  21 2012 "No"  0  -.014079545
"ABN AMRO HOLDING"    15504  21 2013 "No"  0  -.003391278
"ABN AMRO HOLDING"    15504  21 2014 "Yes" 1   -.00488302
"ABN AMRO HOLDING"    15504  21 2015 "Yes" 1 -.0017520806
"ABN AMRO HOLDING"    15504  21 2016 "No"  0   .006722689
"ACCOR"              100001 108 2013 "No"  0   .017705383
"ACCOR"              100001 108 2014 "No"  0    .02592804
"ACCOR"              100001 108 2015 "Yes" 1   .023232436
"ACCOR"              100001 108 2016 "Yes" 1  .0088503035
"ACEA"               233257 504 2012 "Yes" 1   .009964245
"ACEA"               233257 504 2013 "Yes" 1   .020027226
"ACEA"               233257 504 2014 "Yes" 1   .023505114
"ACEA"               233257 504 2015 "Yes" 1    .02609106
"ACEA"               233257 504 2016 "No"  0    .03799535
"ACEA"               233257 504 2017 "No"  0     .0244575
end
label var name "Company Name"
label var gvkey "Global Company Key"
label var firmid "group(gvkey)"
label var year "year"
label var ExternalAssurance "External Assurance"
label var ExternalAssurancedum "Dummy External Assurance"
label var roa "Return on Assets"

Kind regards,
Fleur

[Phil Bromiley]

drop if (year==2015 & ExternalAssurancedum==1 & year==2016 & ExternalAssurancedum==0)

This doesn't drop anything becuase you never have year=2015 and year=2016 or ExternalAssurancedum = 0 and 1 in the same observation. Those if statements in Stata select observations and are relative to the current observation..

First, you're generally better off if you xtset your data. This let's you use lag operators. You could do it without xtset:

drop if ExternalAssurancedum==1 & ExternalAssurancdum[_n-1]==0 & (name==name[_n-1])

Of if you really only want it for 2016
drop if ExternalAssurancedum==1 & ExternalAssurancdum[_n-1]==0 & year==2016 & year[_n-1]=2015 & (name==name[_n-1])


or, if you xtset your data

drop if ExternalAssurancedum==1 & L.ExternalAssurancdum==0

xtset means Stata won't lag across firms - for the first observation, the lagged value will be missing.