Creating a new variable in panel data that meets multiple conditions

[Cassie Reedman]

Hi,

I am having some trouble with my coding for STATA currently. I have panel data that has multiple observations for each individual (sometimes 2, sometimes more) and another variable in the dataset that codes as a 1 or 0 if the individual was positive for this variable (called A). I am trying to create a couple more variables, one that is a 1 for the individual if they were A==1 at their first observation (so time ==1) and 0 if not and then another variable that is a 1 for the individual if they were A==1 at their last observation in the data set (which for some individuals is at time==2 and for some it is time ==11).

This is the coding I have tried so far with A1first being the new variable if the individual was A=1 at their first observation:
by id (time), sort: egen A1first = min(cond(A==1, time, 0))
replace A1first = 1 if A1first!=0

For the most part this works, but if the individual was A=1 on their first observation and A=0 on their last then STATA seems to be coding that individual as A1first=0 rather than A1first=1 which is what I would like it to do. I having the same problem when I attempt to create the variable A1last:

by id (time), sort: egen A1last = max(cond(A==1, time, 0))
replace A1last = 1 if A1last!=0

This works unless that individual was A=1 on their first observation but not their last, then it codes the individual in this case as Alast =1 rather than Alast=0.

Any help would be greatly appreciated.

[Ken Chui]

Welcome to Statalist.

Try:

bysort id A (time): A1first = 1 if _n ==  1 & A == 1
bysort id A (time): A1last  = 1 if _n == _N & A == 1

replace A1first = 0 if A1first == .
replace A1last  = 0 if A1last  == .

This is not tested. If you'd like more certain answers, consider following the FAQ (http://www.statalist.org/forums/help) and use -dataex- to post some sample so that users here can test their codes.

[Cassie Reedman]

Hi, thank you for the help! This works to get a single 1 in the new variable column at the specific time that A was 1 but not a 1 for the entire individual. Do you know how I'd go about that? I used generate with this code you posed but would there by a way to use egen? Would that solve this problem?

[Ken Chui]

Hi, thank you for the help! This works to get a single 1 in the new variable column at the specific time that A was 1 but not a 1 for the entire individual. Do you know how I'd go about that? I used generate with this code you posed but would there by a way to use egen? Would that solve this problem?

.

Sorry about that, I did miss the gen.

bysort id A (time): gen A1first = 1 if _n ==  1 & A == 1
bysort id A (time): gen A1last  = 1 if _n == _N & A == 1

replace A1first = 0 if A1first == .
replace A1last  = 0 if A1last  == .

As for how this code does not work for you, I really do not get what you want. Please understand that as someone looking at the data for days/weeks, things that are very clear to you may not be as apparent to us. Verbal description of data is really not the way to go. It'd REALLY be great if you can heed my advice, show some sample data, and also add the variables that you wish to generate. That would really speed things up.

[Cassie Reedman]

Hi again,

I ended up sorting this out. My apologies, I am new to the forum so next time I have a question I will post sample data.

I was able to create my new variables using this code:

bysort id (time): egen A1first = min(cond(A==1 & _n==1, time, .))
replace A1first = 0 if A1first==.
replace A1first = 1 if A1first !=0

bysort id (id): egen A1last = max(cond(A==1 & _n==_N, time, .))
replace A1last = 0 if A1last==.
replace A1last = 1 if A1last !=0

Your tip with using the _n and _N proved to be very helpful so thank you again.

[Nick Cox]

The help for egen warns against using references to _n or _N. This problem can, I think, be addressed more directly

bysort id (time) : gen A1first = A[1] == 1 

by id: gen A1last = A{_N] == 1

[Ken Chui]

The help for egen warns against using references to _n or _N. This problem can, I think, be addressed more directly

bysort id (time) : gen A1first = A[1] == 1

by id: gen A1last = A{_N] == 1

Thanks, Nick! Good to know about the warning.

[Cassie Reedman]

The help for egen warns against using references to _n or _N. This problem can, I think, be addressed more directly

bysort id (time) : gen A1first = A[1] == 1

by id: gen A1last = A{_N] == 1

Thank you for the information! This is very helpful moving forward.