Need help with creating a new variable based on several conditions please

[Yifan Lu]

Hi all, I'm recently compling a data set that captures data about how car plants affect its surroundings. I have panel data and would like to create a new variable that captures whether if a car plant exist at certain year.

These are examples of my two data sets.

Economy data set:

zip	year	data
1	2018
1	2019
1	2020
2	2018
2	2019
2	2020
3	2018
3	2019
3	2020

Car plant data set:

zip	built_date	year
1	1998	2020
2	2016	2020

(I didn't have year 2020 before but added it so I can do merge 1:1 zip year)

What I have after the merge is something like this:

zip	year	data	built_date
1	2018		.
1	2019		.
1	2020		1998
2	2018		.
2	2019		.
2	2020		2016
3	2018		.
3	2019		.
3	2020		.

I was hoping to create a new variable, if_Exist, that captures whether if the plant exist at year, andl perhaps also fill in the built_date for the empty cells. And I want to assign value 1 for plants with year >= built_date, and 0 if year < built_date.

However I tried various methods but the codes just won't run due to multiple syntax errors.

Can anyone please enlighten me how to get the codes that converts table 3 into the way I wanted? Filling the built_date part is now a must, but I really need the if_Exist varible to exist

Thank you!!

[Nick Cox]

The problem would be easier if you had done a one-to-many merge on zip, but that is easily fixed.

egen when_built = max(built_date), by(zip)
gen wanted = year >= when_built if !missing(year, when_built)

[William Lisowski]

This crossed with Nick's post #2 and demonstrates using a one-to-many merge instead of merging on two variables where one of them was arbitrarily added to the plant dataset.

// read in example plant data
* Example generated by -dataex-. For more info, type help dataex
clear
input byte zip int(built_date year)
1 1998 2020
2 2016 2020
end
save "~/Downloads/plant", replace

// read in example economy data
* Example generated by -dataex-. For more info, type help dataex
clear
input byte zip int year byte data
1 2018 21
1 2019 18
1 2020 14
2 2018 10
2 2019 37
2 2020 21
3 2018 12
3 2019 20
3 2020 27
end
save "~/Downloads/economy", replace

// start here
use "~/Downloads/plant"
// I do not understand what purpose year serves
drop year
// confirm that there is just one observation per plant
isid zip // this will fail if an id appears more than once

// merge each observation in plant to every observation of the same plant in economy
merge 1:m zip using "~/Downloads/economy"
sort zip year

list, sepby(zip) abbreviate(12) noobs

. list, sepby(zip) abbreviate(12) noobs

  +-------------------------------------------------+
  | zip   built_date   year   data           _merge |
  |-------------------------------------------------|
  |   1         1998   2018     21      Matched (3) |
  |   1         1998   2019     18      Matched (3) |
  |   1         1998   2020     14      Matched (3) |
  |-------------------------------------------------|
  |   2         2016   2018     10      Matched (3) |
  |   2         2016   2019     37      Matched (3) |
  |   2         2016   2020     21      Matched (3) |
  |-------------------------------------------------|
  |   3            .   2018     12   Using only (2) |
  |   3            .   2019     20   Using only (2) |
  |   3            .   2020     27   Using only (2) |
  +-------------------------------------------------+