Generating a binary variable whose value is dependent on another binary variable in given years. Is there a more elegant solution?

[Nabuu Shergill]

Hello, I want to create a series of 5 variables from a data set that looks like the below:

ID	Year	Bin
7493	2001	1
9624	2003	0
6843	2005	1
2548	2004	1
7054	2001	0
2147	2006	1

The condition for each variable is the following:
Exp-2t: a binary variable that is 1 when Bin==1 in year=2006, and Bin==0 in 2003, 2004, 2005
Exp-1t: a binary variable that is 1 when Bin==1 in year=2005, 2006 and Bin==0 in 2003, 2004
Exp0t: a binary variable that is 1 when Bin==1 in year=2004, 2005, 2006, and Bin==0 in 2002, 2003
Exp1t: a binary variable that is 1 when Bin==1 in year=2003, 2004 and Bin==0 in 2002, 2001
Exp2t: a binary variable that is 1 when Bin==1 in year=2002, 2003, 2004 and Bin==0 in 2001

I know how to do this for each variable one by one. For example for Exp0t:
gen Exp0=1 if Bin==1 & year>=2004
replace Exp0=0 if Export0==.
gen Exp01=1 if Bin==0 & year>2001 & year<2004
gen Exp0t=1 if Exp0==1 & Exp01==1

But there must be an easier, more elegant way to do this. Copy and pasting the above 4 more times and making minor changes seems very convoluted. Is there a way to run a loop that can do this? Is there any other more elegant solution that I'm just not seeing?

[Andrew Musau]

forval i=0/2{
     gen exp`i'= Year>=(2004-`i') & Bin
     gen expm`i'= Year>=(2004+`i') & Bin
}

[Nabuu Shergill]

Hi Andrew,

Thank you so much for the elegant solution. I unfortunately made a mistake in my thinking, and now I'm trying to get this to run the code over every value of ID, but I keep getting an error.

ID appears repeatedly over years, and I want to run the code such that I can obtain the variables:

The condition for each variable is the following:

Exp-2t: a binary variable that is 1 when Bin==1 in year=2006, and Bin==0 in 2003, 2004, 2005, for every repeat of ID

Exp-1t: a binary variable that is 1 when Bin==1 in year=2005, 2006 and Bin==0 in 2003, 2004, for every repeat of ID

Exp0t: a binary variable that is 1 when Bin==1 in year=2004, 2005, 2006, and Bin==0 in 2002, 2003, for every repeat of ID

Exp1t: a binary variable that is 1 when Bin==1 in year=2003, 2004 and Bin==0 in 2002, 2001, for every repeat of ID

Exp2t: a binary variable that is 1 when Bin==1 in year=2002, 2003, 2004 and Bin==0 in 2001, for every repeat of ID

I have tried to create the following loop, but I keep getting an error message:

foreach var of varlist ID {
forval i=0/2{
gen exp`i'= Year>=(2004-`i') & Bin
gen expm`i'= Year>=(2004+`i') & Bin
}}
program error: code follows on the same line as close brace

Is there a reason this is happening? Am I unable to run a loop within a loop?

[Andrew Musau]

I'm trying to get this to run the code over every value of ID

These conditions are independent of the variable "ID", so I am not sure what you mean here. In the code below

forval i=0/2{
             gen exp`i'= Year>=(2004-`i') & Bin
             gen expm`i'= Year>=(2004+`i') & Bin
}

the gen commands evaluate each observation when creating the dummies. So whether we group by ID or not makes no difference. When you state

Exp-2t: a binary variable that is 1 when Bin==1 in year=2006, and Bin==0 in 2003, 2004, 2005, for every repeat of ID

do you mean that you want the dummy to be created only if ID is repeated? If so

forval i=0/2{
            bys ID: gen exp`i'= Year>=(2004-`i') & Bin if _N>1
            bys ID: gen expm`i'= Year>=(2004+`i') & Bin if _N>1
}

If the above is not what you want, give an example of what you mean.


EDIT:

ID appears repeatedly over years, and I want to run the code such that I can obtain the variables:

Another interpretation is that you have duplicates of ID and Year, and you want the dummies to be created only for the duplicate observations.

forval i=0/2{
            bys ID Year: gen exp`i'= Year>=(2004-`i') & Bin if _n>1
            bys ID Year: gen expm`i'= Year>=(2004+`i') & Bin if _n>1
}

[Nabuu Shergill]

Hi Andrew,

If I sort my data by ID and Year:

ID	Year	Bin
5674	2003	0
5674	2004	0
5674	2005	1
5674	2006	1
7689	2002	0
7689	2003	0
7689	2004	1
7689	2005	1
7689	2006	1

What I mean is that the new variable Exp-1t would be 1 for all values of 5674, because when ID=5674,

Bin==1 in year=2005, 2006 and Bin==0 in 2003, 2004

Another binary variable named Exp0t would be 1 for all values of 7689, because when ID-7689, Bin==1 in year==2004,2005,2006 and Bin==0 in 2002, 2003

That's what I mean that the condition for the binary variables is:

Exp-2t: a binary variable that is 1 when Bin==1 in year=2006, and Bin==0 in 2003, 2004, 2005, for every repeat of ID
Exp-1t: a binary variable that is 1 when Bin==1 in year=2005, 2006 and Bin==0 in 2003, 2004, for every repeat of ID
Exp0t: a binary variable that is 1 when Bin==1 in year=2004, 2005, 2006, and Bin==0 in 2002, 2003, for every repeat of ID
Exp1t: a binary variable that is 1 when Bin==1 in year=2003, 2004 and Bin==0 in 2002, 2001, for every repeat of ID
Exp2t: a binary variable that is 1 when Bin==1 in year=2002, 2003, 2004 and Bin==0 in 2001, for every repeat of ID

My apologies for the confusion.

[Andrew Musau]

forval i=0/2{
        bys ID: egen exp`i'=max(cond(Year>=(2004-`i') & Bin, 1, 0))
        bys ID: egen expm`i'= max(cond(Year>=(2004+`i') & Bin, 1, 0))
 }

See https://www.stata.com/support/faqs/d...ble-recording/ for an elaboration of the technique.