If results

[Samantha Will]

Hello,

* Example generated by -dataex-. For more info, type help dataex
clear
input float(fundnr month year mf_tna)
1  9 2011   3310683
1 12 2011   4788197
1  7 2011    332705
1 11 2011   4469605
1 10 2011   4415153
1  8 2011    585448
1 12 2012   7535032
1  3 2012   6181172
1  5 2012   6197676
1  2 2012   5536767
1 11 2012   7386680
1  8 2012   6896009
1  6 2012   6568930
1  1 2012   5309536
1  4 2012   6107277
1 10 2012   7049998
1  9 2012   7094395
1  7 2012   6547141
1 10 2013  11854977
1  8 2013   9636066
1  7 2013   9685646
1  5 2013   9234421
1  1 2013   7967994
1 12 2013  12355125
1  9 2013  10393426
1  2 2013   8394749
1  3 2013   8797938
1  4 2013   9055238
1  6 2013   9048571
1 11 2013  12046746
1  2 2014  12347357
1 11 2014  12799221
1  6 2014  12090967
1  5 2014  11895378
1  4 2014  11571634
1  8 2014  12476517
1 10 2014  12372551
1  7 2014  11905682
1  3 2014  12179222
1  9 2014  12041317
1 12 2014  12430416
1  1 2014  11812615
1  1 2015  11578330
1  4 2015   1992258
1  2 2015  12507539
1  3 2015  12236630
2 12 2011 105889224
2 10 2011 100448240
2  9 2011  84169200
2  8 2011  87244584
2 11 2011 105600840
2  8 2012 147010192
2  9 2012 150647104
2  1 2012 114061704
2 11 2012 141535680
2 10 2012 143428512
2  3 2012 122008768
2  7 2012 141863920
2  6 2012 140861424
2  5 2012 139329360
2 12 2012 140473664
2  2 2012 116286928
2  4 2012 151342272
2 12 2013 204117072
2  6 2013 168639840
2 10 2013 193704496
2  7 2013 172776848
2 11 2013 200032736
2  4 2013 165520800
2  3 2013 165389888
2  9 2013 178086480
2  2 2013 163146032
2  8 2013 170596928
2  1 2013 156159856
2  5 2013 173733088
2  2 2014 204216000
2  1 2014 199165248
2  5 2014 199261040
2  3 2014 197005888
2  4 2014 192773888
2 12 2014 202054384
2  9 2014 196861248
2  7 2014 197467552
2 11 2014 213074560
2  6 2014 202175984
2  8 2014 202034368
2 10 2014 205145072
2  9 2015 186629120
2  4 2015 202369072
2 11 2015 203256080
2  5 2015 203269840
2  3 2015 209169472
2  2 2015 212148640
2  7 2015 204622400
2  8 2015 197307392
2 10 2015 202451872
2 12 2015 198461248
2  6 2015 198188672
2  1 2015 198214128
2 12 2016 117706872
end

I want to extract live funds from this data which are defined as funds that have observations in the year 2018. The problem is, if I use 'keep if year == 2018' I lose the informations of these funds from the previous years. For example: As you can see fundnr 1 is not a live fund so I do not need it in my database. What you can not see here is that fundnr 2 has observations up to 2018 so that means fundnr 2 is a live fund and I want to have all months, years and mf_tna up until 2018.
So the results on this extract should be: Fundnr 1 with not at least one observations in 2018 should be deleted and fundnr 2 should contain all informations up until 2018

Many thanks!

[Clyde Schechter]

I think you want

by fundnr (year month), sort: egen byte live_fund = max(year == 2018)
keep if live_fund & year <= 2018

As your example data does not actually contain any observations with year == 2018, as applied to your example the result is an empty data set. But it should work in your real data set.

In the future, though, when posting example data, please create an example that can properly demonstrate the problem you are trying to solve. Stata cannot work with data that it "cannot see."

[Samantha Will]

I think you want

by fundnr (year month), sort: egen byte live_fund = max(year == 2018)
keep if live_fund & year <= 2018

As your example data does not actually contain any observations with year == 2018, as applied to your example the result is an empty data set. But it should work in your real data set.

In the future, though, when posting example data, please create an example that can properly demonstrate the problem you are trying to solve. Stata cannot work with data that it "cannot see."

The code seems to be working perfectly, thank you very much. I will do that from now on forward.

[Samantha Will]

Would you perhaps know how to extract the dead funds (which is basically the contrary definition: all fundnrs that have ONLY observations < 2018) My problem is that if I try to get only the dead funds I automatically get information on the live funds that happen to have observations < 2018 . Basically any information on fundnr 2 should not be returned, because it has at least one observation in 2018, thus making it a live fund.

[Clyde Schechter]

by fundnr (year month), sort: egen dead_fund = min(year < 2018)
keep if dead_fund

Added: I interpreted "extract the dead funds" as meaning you wanted to keep all the dead funds. But it dawns on me that you may actually mean you want to -drop- them. The change to the above code in that case is obvious.

[Samantha Will]

by fundnr (year month), sort: egen dead_fund = min(year < 2018)
keep if dead_fund

Added: I interpreted "extract the dead funds" as meaning you wanted to keep all the dead funds. But it dawns on me that you may actually mean you want to -drop- them. The change to the above code in that case is obvious.

Thank you! Your initial interpretation was completely correct, works perfectly