Creating Dummy variable with a condition based on the previous 3 years

[Mohamed Mahmoud]

Dear Stata members,

I have a question please , I want to create a new dummy variable =1 if a firm has an (amount) in any of the past 3 years and equal zero otherwise

My data looks like

* Example generated by -dataex-. For more info, type help dataex
clear
input double fyear str6 gvkey float amount
2015 "137135"     0
2009 "147329"     0
2010 "147329"     0
2011 "147329"     0
2012 "147329"     0
2013 "147329"     0
2014 "147329"     0
2015 "147329"     0
2016 "147329"     0
2017 "147329"     0
2018 "147329"     0
2019 "147329"     0
2020 "147329"     0
2021 "147329"     0
2011 "178698"     0
2012 "178698"     0
2013 "178698"  17.6
2014 "178698"  21.1
2015 "178698"   1.9
2016 "178698"   3.9
2017 "178698"   3.2
2018 "178698"  96.2
2020 "039527"     0
2021 "039527" 206.7
2009 "021542"     0
2010 "021542"     0
2011 "021542"     0
2012 "021542"     0
2013 "021542"     0
2014 "021542"     0
2015 "021542"     0
2016 "021542"     0
2017 "021542"     0
2018 "021542"     0
2019 "021542"     0
2020 "021542"     0
2021 "021542"     0
2009 "001004"     0
2010 "001004"     0
2011 "001004"     0
2012 "001004"     0
2013 "001004"     0
2014 "001004"     0
2015 "001004"     0
2016 "001004"     0
2017 "001004"     0
2018 "001004"     0
2019 "001004"     0
2020 "001004"     0
2021 "001004"     0
2009 "210418"  2876
2010 "210418"  2901
2011 "210418"  2576
2012 "210418"  2749
2013 "210418"  2685
2014 "210418"  2200
2015 "210418"  2144
2016 "210418"  1622
2017 "210418"  1708
2018 "210418"  2153
2019 "210418"  1999
2020 "210418"  3033
2021 "210418"  2170
2009 "164506" 354.3
2010 "164506" 385.9
2011 "164506" 390.7
2012 "164506" 404.5
2013 "164506" 334.6
2014 "164506" 257.5
2015 "164506" 172.1
end

Any one have any idea how to create this variable ?

[Ken Chui]

bysort gvkey (fyear): gen wanted = (amount[_n-3] > 0 & amount[_n-3] < .) if _n > 3

may do the job.

[Joro Kolev]

Or maybe this one:

bysort gvkey (fyear): gen dummy = max(amount[_n-1],amount[_n-2],amount[_n-3])>0 & !missing(max(amount[_n-1],amount[_n-2],amount[_n-3]))