indexing based on occurrences of another column

[Dennis Wajda]

I have panel data and I'm trying to create an indexed column (here ET) based on the first occurrence in an adjacent column (E). The data looks like the following:

FirmID	CountryID	YEAR	F_RD	C_RD	T	E	ET
1	1	1995	1896209.822	1785573897	1	0	0
1	1	1996	1362147.818	1785573897	2	0	0
1	1	1997	2104438.406	1785573897	3	1	0
1	1	1998	1836605.362	1785573897	4	1	1
1	1	1999	1404572.073	1785573897	5	1	2
1	1	2000	1264150.002	1785573897	6	1	3
1	1	2001	2090266.546	1785573897	7	1	4

I've tried code in in stata manual: https://www.stata.com/support/faqs/d...t-occurrences/
but can't seem to figure out indexing to get ET column. Any help would be appreciated.

[Clyde Schechter]

It isn't clear what the rule relating et to e is. The simplest idea that I perceiveis that e is always either 0 or 1, and that you want et to be 0 until you hit the first observation with e = 1, and then start counting up observations from zero after that. But what if e goes back to 0 after some ones. What happens then? Does the value of et continue to increase as we go on? Or does it stay at its latest value until more e = 1's are encountered? Or does it go back to 0? Or something else?

And what if e is something other than 0 or 1? How is that handled?

And is all of this to be done separately for each CountryID? Or each FirmID? Or each combination of CountryID and FirmID? Or just one pass through the entire data set?

[Dennis Wajda]

Thanks for the response Clyde. My data covers the years 1995-2015. E is a "policy change" by country that affects firms that stays 1 once enacted through the time frame in the sample. ET is meant to model a growth curve once the policy change is implemented. So for example, if the policy change (E) is enacted in 1997, it will stay enacted through 2015. ET (treatment effect) will continue its count upwards one year following the policy change. Using Mr. Cox's article, I was able to get the first year of the treatment effect (ET) to code 1, but couldn't get ET to index through the data after.
Code I used was:
by id (year), sort: gen byte first = sum(E) == 1 //this marks the first year following the policy change but wouldn't sum or increment the (ET) years subsequent .

[Clyde Schechter]

You didn't answer my question about doing this by firm, or country, or neither or both, and from your description of the variables it isn't clear to me. So the code below does it by firmid. Make the change in the -by- prefix as needed if this was the wrong guess.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(firmid countryid) int year float f_rd long c_rd byte(t e wanted)
1 1 1995   1896210 1785573897 1 0 0
1 1 1996   1362148 1785573897 2 0 0
1 1 1997 2104438.5 1785573897 3 1 0
1 1 1998 1836605.4 1785573897 4 1 1
1 1 1999   1404572 1785573897 5 1 2
1 1 2000   1264150 1785573897 6 1 3
1 1 2001 2090266.5 1785573897 7 1 4
end

by firmid (year), sort: gen et = sum(e) - 1
replace et = 0 if et < 0

assert et == wanted

The data input is your example data, with your version of et renamed wanted. I generate a new et variable and at the end demonstrate that it matches what you wanted.

Note that I do not generate a variable marking the first year following the policy change, as it is not necessary for the purpose at hand. If you need such a variable for other reasons, the code you show is not quite right: it identifies the year in which the policy change began, not the first year following that. To get the first year following the start of the change it would be -by id (year), sort: gen byte first_following = sum(e) == 2-.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 15.1 or a fully updated version 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.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Dennis Wajda]

Thanks Clyde, appreciate the help and noted on the posting requirements.