Merge rows with unique ID in stata

Question

I have a dataset where I need unique county FIPS codes that need to be merged. The dataset looks like:

FIPS yr1990 yr2000 yr2010
1001 1      0      1
1002 1      1      0
1003 1      0      0
1004 0      0      0
1005 0      0      1

County boundaries have changed and I need to merge several FIPS codes together. Essentially, I need the dataset to look like:

FIPS         yr1990 yr2000 yr2010
1001/1003   1       1      1
1002        1       1      0
1004/1005   0       0      1

Is there a way to select specific FIPS to be merged over rows?

Answer 1

This solution might not scale to very large datasets as writing the replace statements must be done manually. But it keeps the exact format you are using in your example. And a more scalable way might be difficult if there is no system in how the FIPS codes were combined.

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 FIPS byte(yr1990 yr2000 yr2010)
"1001" 1 0 1
"1002" 1 1 0
"1003" 1 0 0
"1004" 0 0 0
"1005" 0 0 1
end

*Combine the FIPS codes
replace FIPS = "1001/1003" if inlist(FIPS,"1001","1003")
replace FIPS = "1004/1005" if inlist(FIPS,"1004","1005")

*Collapse rows by FIPS value, use max value for each var on format yr????
collapse (max) yr???? , by(FIPS)