county-border pairs

[Ruaidhri McLaughlin-Dowd]

I have a dataset with these variables: ICPSRST (=2 digit code for state) ICPSRCTY (=3 digit code for county) name of county, name of state, GISJOIN (the code for the county), adj_GISJOIN (code for adjoining county). The dataset has the matching of each county with it's bordering counties. I want to generate additional variables which give me the state name and county name for the adjoining county. At the moment I only have the reference number. Do you have any idea how I can fill in this information automatically?

[Nick Cox]

Cross-posting https://www.reddit.com/r/stata/comme...e_countypairs/

[Mike Lacy]

Seeing a small sample of your data for the relevant variables for a few counties would facilitate an answer. To learn about how to present an example, check out the StataList FAQ for new members with particular attention to -dataex-.

[Ruaidhri McLaughlin-Dowd]

Seeing a small sample of your data for the relevant variables for a few counties would facilitate an answer. To learn about how to present an example, check out the StataList FAQ for new members with particular attention to -dataex-.

[Clyde Schechter]

I'm afraid your example is not well chosen for the purpose at hand, nor is it well presented. The code for doing this is going to have some level of complexity and most of those who want to help you would want to test it in the example data before releasing it to you. You can't import data from a screenshot. That's why you were explicitly asked to use the -dataex- command for this. If you are running version 17, 16 or a fully updated version 15.1 or 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. The more you help those who want to help you, the better the results will be for everyone involved.

As for well chosenness, to coin a word, clearly the way to find the county and state names for an adjoined county will be to find the same value of adj_GISJOIN in some observation's value of GISJOIN. But your example doesn't have that happen. A more helpful example would include some matches of that nature.

Added: The following is probably at least a good approximation to what you need:

frame put ICPSRNAM STATENAM GISJOIN, into(index)
frame index: duplicates drop

frlink m:1 adj_GISJOIN, frame(index GISJOIN)
frget JOIN_ICPSRNAM = ICPSRNAM JOIN_STATENAM = STATENAM, from(index)

As it is untested, for reasons discussed, use at your own risk.