generate indicator vatiable for having a certain status in any one or more past years

[Flora Yin]

Dear statalist,

I have a dataset from 2009 to 2019, the main variables are Year, Symbol, and status. Status takes three values, =1 for mandatory, =2 for voluntary, =3 for non. I want to create an indicator variable, voluntary_past_any, equals one if a firm has been in status 2 (voluntary) in any one or more of the past years. Say now is 2012, firm A was in status 1 in 2009, status 2 in 2010-2011, and status 3 in 2012. So firm A should be coded 1 for voluntary_past_any in 2012, as it used to be in status 2 in at least one of the years prior to the current year (i.e., 2012). If now is 2011, firm A should also be coded 1 for voluntary_past_any, but not so if now is 2010. I wonder how to write code to generate this variable.

Btw, in my dataset, it is quite often that firms switch from one status to another over the sample period, even though the firm may not be in status 2 in the current year, as long as it has ever been in status 2 for at least once prior to the current year, voluntary_past_any should be coded 1 for the current year.

I tried:

by Symbol (Year), sort: gen byte voluntary_past_any = (Year[_n-1] > Year-10) if _n > 1 & status == 2

but it doesn't work as I want.

Thanks a lot in advance for any kind help.

[Clyde Schechter]

I believe this will do it:

by Symbol (Year), sort: gen voluntary_past_any = min(sum(status == 2), 1)

Note: As no example data was provided, this code is untested and may contain typos or other errors.

In the future, always show example data when asking for help with code, and use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Flora Yin]

Hi Clyde,

Thanks a lot for your reply! Sorry about not providing an example data.
I tried your code and think probably status == 2 should be status[_n-1] == 2?

by Symbol (Year), sort: gen voluntary_past_any = min(sum(status[_n-1] == 2), 1)

So the result looks like this:

Year    Symbol    status    voluntary_past_any    voluntary_past_any111
2010    2505    3    0    0
2011    2505    3    0    0
2012    2505    3    0    0
2013    2505    3    0    0
2014    2505    3    0    0
2015    2505    3    0    0
2016    2505    3    0    0
2017    2505    2    0    1
2018    2505    2    1    1
2019    2505    3    1    1
2009    3027    3    0    0
2010    3027    3    0    0
2011    3027    3    0    0
2012    3027    3    0    0
2013    3027    3    0    0
2014    3027    1    0    0
2015    3027    1    0    0
2016    3027    1    0    0
2017    3027    2    0    1
2018    3027    2    1    1
2019    3027    2    1    1

voluntary_past_any is the one with status[_n-1] == 2 and voluntary_past_any111 is the one with status == 2. So for firm 2505 in 2017, this is the first year it changed to status 2, so there is no prior year of being in status 2 before 2017, then the indicator variable should be coded 0 rather than 1. Same for firm 3027 in 2017.

[Clyde Schechter]

Yes, of course. I misunderstood your question in that I took prior to mean prior or current year. But you did actually explain carefully in your examples that you did not want to include the current year. Glad you were able to fix that.