matching counties based on first two digits for finding similar state

[Tariq Abdullah]

Hello,

I have a data which shows distance between all counties from another county. This includes all the counties from census. However, my aim is to find the distance between counties only where both counties ( county1 and county2 ) belong to the same state. May anyone kindly guide me how I can do it? The county1 and county2 variable are constructed where the first two digits are the indicator of state. So, if I can match it , then I'll be able to find my desired outcome. But, I don't know how I can do it.

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 county1 double mi_to_county str5 county2
"01001" 22.462994302208628 "01021"
"01001" 26.844686566998814 "01085"
"01001"  29.51758494098288 "01051"
"01001" 30.776370841805658 "01047"
"01001" 34.493442726438765 "01101"
"01001"  35.89275856373744 "01037"
"01001"  38.46086856066846 "01105"
"01001"  43.40325272873116 "01007"
"01001"  50.23794560165572 "01117"
"01001"  53.85235728142641 "01131"
"01001"  54.07279881059125 "01123"
"01001" 54.262868563770404 "01013"
"01001"  56.42751293745245 "01087"
"01001"  58.69133403697369 "01041"
"01001" 101.67804794852763 "13145"
"01001" 102.08414383983288 "01029"
"01001" 102.46631237320776 "01053"
"01001" 103.10412245380951 "13215"
"01001"  104.5617424700897 "01057"
"01001"   106.367621532817 "13239"
"01001"  141.6540499512556 "28095"
"01001" 143.09524309514657 "28105"
"01001" 143.14762563679008 "01079"
"01001" 143.92170699307306 "28159"
"01001" 144.50693930791743 "28101"
end

[Øyvind Snilsberg]

gen wanted = substr(county1,1,2) == substr(county2,1,2)

[Tariq Abdullah]

Thanks a ton! That worked perfectly.

I have a master dataset with which I need to combine this using dataset ( distance between counties ).

My master dataset state and counties look like the following. To merge this using dataset county with the master dataset, I need to convert this county1 and county2 variable like the master dataset county variable.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte statefip long county
17 1
39 13
23 14
54 09
17 23
18 11
35 01
end

Could you kindly tell me how I can match the using data ( county 1 and county 2 variables ) like the master data county variable ?

[Øyvind Snilsberg]

use using.dta, clear
gen county = real(substr(county1,1,2))
save using.dta, replace

use master.dta, clear
joinby county using using.dta

[Tariq Abdullah]

When I'm using either of the following snippet of codes on my data it's giving me this error:

gen county = real(substr(county1))

gen county = real(substr(county1,1,2))

invalid syntax
r(198);

[Øyvind Snilsberg]

is county1 in your data different from county1 in the dataex example? can you show a new dataex example?

[Tariq Abdullah]

As far as I'm concerned I think they are similar unless I did some silly mistake. Here, is data sample from my data again for your convenience

* Example generated by -dataex-. For more info, type help dataex
clear
input str5(county1 county2)
"01001" "01021"
"01001" "01085"
"01001" "01051"
"01001" "01047"
"01001" "01101"
"01001" "01037"
"01001" "01105"
"01001" "01007"
"01001" "01117"
"01001" "01131"
"01001" "01123"
"01001" "01013"
"01001" "01087"
"01001" "01041"
"01001" "01065"
"01001" "01011"
"01001" "01121"
"01005" "12059"
"01005" "13095"
"01005" "13177"
"01005" "01039"
"01005" "01013"
end

[Øyvind Snilsberg]

it is difficult to give advice because the code runs without error on the data example. I don't see how -gen county = real(substr(county1,1,2))- returns 'invalid syntax' because the syntax is correct.

[Tariq Abdullah]

my sincerest apologies. Maybe I have done something wrong. But the above command works.

After using this command suggested by you -gen county = real(substr(county1,1,2))- - is giving me the output as county as shown below. The problem is I want the whole thing 17187 , 17185, 21163 as it is in county column without this being the string variable. When I'm destringing it , this just gives me the initial two digits of my county1 variable - which is the indicator of state. The last three digits of the county1 variable is the indicator of county.

input str5 county1 float county str3 county_s
"17187" 17 "017"
"17185" 17 "017"
"17189" 17 "017"
"21161" 21 "021"
"21163" 21 "021"
end

[Tariq Abdullah]

The following command worked. Thanks, Mr. Snilsberg for your time and patience with my naive coding. Means a lot.

 destring  county1, replace ignore(" ")

[Øyvind Snilsberg]

no worries, did you manage to combine the datasets?

[Tariq Abdullah]

Thanks so much for your kind gesture, Mr. Snilsberg. Unless I'm changing my using data in a certain way I cannot merge it with my master. I'm still working on it. However, there is no wrong with merging or any other coding. Nonetheless, very kind of you for checking up on that. Highly obliged !