March 19, 2010 at 2:36 pm
Austin FraktAn important question has not yet been addressed on this blog: can I get Stata code into a post? Yes I can! What is truly excellent is that the same post also addresses another need: what else can one do to pass the time while trying to fall asleep?
If for that purpose, or otherwise, you need a list that for each U.S. state specifies which other states neighbor (border on, are closest to) it then this post is for you. And it fills a void on the internet. I needed such a list for some analysis I’m doing and couldn’t find one so I made my own.
Since I do my analysis in Stata, the list below is really Stata code that constructs one indicator for each state (call it the “target” state) that has the value one (1) for observations in states neighboring the target state and zero (0) otherwise. So, if you need to do precisely this in Stata too, this is your lucky day. I just saved you 30 minutes of work.
If you don’t read Stata just notice that the capital letters on the left hand side of the equations below identify a state and the ones on the right are its neighbors. Ignore all other characters (and especially those promising to cut deficits without health reform).
I define CA to be the neighbor of HI and WA to be the neighbor of AK. Just interpret “neighbor” as “nearest” and these make sense. Other than these exceptions and the case of states sharing a corner (AZ, CO, NM, UT), which I consider neighbors, a target and its neighboring state must share more than zero miles of border (this is arbitrary but good enough for my needs).
In the list below, “sXXn” is shorthand for “state XX neighbors” where XX is the state’s two letter code. Anyone who finds an error in my list wins one hundred thousand units of The Incidental Economist credit, to be delivered by telepathy into the ledger I keep in an undisclosed location and which can be redeemed for your choice of fanciful prize of whimsy and mirth. (I’m really in a good mood in anticipation of my representative not coming home this weekend and instead doing something substantial and important in DC.)
gen sAKn = inlist(stateab,”WA”)
gen sALn = inlist(stateab,”TN”,”GA”,”FL”,”MS”)
gen sARn = inlist(stateab,”MO”,”TN”,”MS”,”LA”,”TX”,”OK”)
gen sAZn = inlist(stateab,”UT”,”CO”,”NM”,”CA”,”NV”)
gen sCAn = inlist(stateab,”OR”,”NV”,”AZ”,”HI”)
gen sCOn = inlist(stateab,”WY”,”NE”,”KS”,”OK”,”NM”,”AZ”,”UT”)
gen sCTn = inlist(stateab,”MA”,”RI”,”NY”)
gen sDCn = inlist(stateab,”MD”,”VA”)
gen sDEn = inlist(stateab,”PA”,”NJ”,”MD”)
gen sFLn = inlist(stateab,”GA”,”AL”)
gen sGAn = inlist(stateab,”NC”,”SC”,”FL”,”AL”,”TN”)
gen sHIn = inlist(stateab,”CA”)
gen sIAn = inlist(stateab,”MN”,”WI”,”IL”,”MO”,”NE”,”SD”)
gen sIDn = inlist(stateab,”MT”,”WY”,”UT”,”NV”,”OR”,”WA”)
gen sILn = inlist(stateab,”WI”,”IN”,”KY”,”MO”,”IA”)
gen sINn = inlist(stateab,”MI”,”OH”,”KY”,”IL”)
gen sKSn = inlist(stateab,”NE”,”MO”,”OK”,”CO”)
gen sKYn = inlist(stateab,”OH”,”WV”,”VA”,”TN”,”MO”,”IL”,”IN”)
gen sLAn = inlist(stateab,”AR”,”MS”,”TX”)
gen sMAn = inlist(stateab,”NH”,”RI”,”CT”,”NY”,”VT”)
gen sMDn = inlist(stateab,”PA”,”DE”,”DC”,”VA”,”WV”)
gen sMEn = inlist(stateab,”NH”)
gen sMIn = inlist(stateab,”OH”,”IN”,”WI”)
gen sMNn = inlist(stateab,”WI”,”IA”,”SD”,”ND”)
gen sMOn = inlist(stateab,”IA”,”IL”,”KY”,”TN”,”AR”,”OK”,”KS”,”NE”)
gen sMSn = inlist(stateab,”TN”,”AL”,”LA”,”AR”)
gen sMTn = inlist(stateab,”ND”,”SD”,”WY”,”ID”)
gen sNCn = inlist(stateab,”VA”,”SC”,”GA”,”TN”)
gen sNDn = inlist(stateab,”MN”,”SD”,”MT”)
gen sNEn = inlist(stateab,”SD”,”IA”,”MO”,”KS”,”CO”,”WY”)
gen sNHn = inlist(stateab,”ME”,”MA”,”VT”)
gen sNJn = inlist(stateab,”NY”,”DE”,”PA”)
gen sNMn = inlist(stateab,”CO”,”OK”,”TX”,”AZ”,”UT”)
gen sNVn = inlist(stateab,”ID”,”UT”,”AZ”,”CA”,”OR”)
gen sNYn = inlist(stateab,”VT”,”MA”,”CT”,”NJ”,”PA”)
gen sOHn = inlist(stateab,”PA”,”WV”,”KY”,”IN”,”MI”)
gen sOKn = inlist(stateab,”KS”,”MO”,”AR”,”TX”,”NM”,”CO”)
gen sORn = inlist(stateab,”WA”,”ID”,”NV”,”CA”)
gen sPAn = inlist(stateab,”NY”,”NJ”,”DE”,”MD”,”WV”,”OH”)
gen sRIn = inlist(stateab,”MA”,”CT”)
gen sSCn = inlist(stateab,”NC”,”GA”)
gen sSDn = inlist(stateab,”ND”,”MN”,”IA”,”NE”,”WY”,”MT”)
gen sTNn = inlist(stateab,”KY”,”VA”,”NC”,”GA”,”AL”,”MS”,”AR”,”MO”)
gen sTXn = inlist(stateab,”OK”,”AR”,”LA”,”NM”)
gen sUTn = inlist(stateab,”ID”,”WY”,”CO”,”NM”,”AZ”,”NV”)
gen sVAn = inlist(stateab,”MD”,”DC”,”NC”,”TN”,”KY”,”WV”)
gen sVTn = inlist(stateab,”NH”,”MA”,”NY”)
gen sWAn = inlist(stateab,”AK”,”ID”,”OR”)
gen sWIn = inlist(stateab,”MI”,”IL”,”IA”,”MN”)
gen sWVn = inlist(stateab,”PA”,”MD”,”VA”,”KY”,”OH”)
gen sWYn = inlist(stateab,”MT”,”SD”,”NE”,”CO”,”UT”,”ID”)
There. If a blog is not the appropriate place to publish academic work of this form and caliber, I don’t know what is. Void filled.
Comments closed
by Max Shron on October 1st, 2010 at 15:57
Hi Austin,
In anticipation of using this data for a project, I decided to do a sanity check and look for symmetry.
Just so you know, New Jersey is listed as being adjacent to Connecticut, but the inverse is not. To my knowledge they do not touch, so you may want to remove that relationship from the file.
Thanks a lot for putting this here, it saved me a lot of time!
by Austin Frakt on October 1st, 2010 at 19:10
@Max Shron – Good catch. I’ll check my code.
by Gregg Lobdell on January 17th, 2012 at 13:14
I noticed that in your list for IL, WI appears twice.
Nice list, it saved me some time.
by Austin Frakt on January 17th, 2012 at 15:23
Thanks. Fixed.
by Ubikuity on September 15th, 2012 at 05:10
Thanks for your list but there is still some inconsistency between:
gen sCTn = inlist(stateab,”MA”,”RI”,”NY”)
gen sNJn = inlist(stateab,”NY”,”CT”,”DE”,”PA”)
I think it should be:
gen sNJn = inlist(stateab,”NY”,”DE”,”PA”)
by Austin Frakt on September 15th, 2012 at 05:54
Good catch. Fixed.
by Ubikuity on September 15th, 2012 at 07:52
I finally created a csv file using your blog post: https://github.com/ubikuity/List-of-neighboring-states-for-each-US-state
Thanks.
by Gordo on September 25th, 2013 at 08:01
In case anyone like me stumbles upon this, here is the tsql to build it out:
create table NeighborStates (statecode char(2), NeighborStateCode char(2))
create unique clustered index idxkey on NeighborStates(StateCode,NeighborStateCode)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AK’,’WA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AL’,’FL’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AL’,’GA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AL’,’MS’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AL’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’LA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’MO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’MS’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’OK’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AR’,’TX’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AZ’,’CA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AZ’,’CO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AZ’,’NM’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AZ’,’NV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘AZ’,’UT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CA’,’HI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CA’,’NV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CA’,’OR’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’KS’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’NE’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’NM’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’OK’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’UT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CO’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CT’,’MA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CT’,’NY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘CT’,’RI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘DC’,’MD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘DC’,’VA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘DE’,’MD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘DE’,’NJ’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘DE’,’PA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘FL’,’GA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘GA’,’NC’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘GA’,’SC’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘GA’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’IL’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’MN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’MO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’NE’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’SD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IA’,’WI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’MT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’NV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’OR’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’UT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’WA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ID’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IL’,’IN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IL’,’KY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IL’,’MO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IL’,’WI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IN’,’KY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IN’,’MI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘IN’,’OH’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KS’,’MO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KS’,’NE’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KS’,’OK’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KY’,’MO’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KY’,’OH’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KY’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KY’,’VA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘KY’,’WV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘LA’,’MS’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘LA’,’TX’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MA’,’NH’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MA’,’NY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MA’,’RI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MA’,’VT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MD’,’PA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MD’,’VA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MD’,’WV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ME’,’NH’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MI’,’OH’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MI’,’WI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MN’,’ND’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MN’,’SD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MN’,’WI’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MO’,’NE’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MO’,’OK’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MO’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MS’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MT’,’ND’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MT’,’SD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘MT’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NC’,’SC’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NC’,’TN’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NC’,’VA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘ND’,’SD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NE’,’SD’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NE’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NH’,’VT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NJ’,’NY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NJ’,’PA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NM’,’OK’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NM’,’TX’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NM’,’UT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NV’,’OR’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NV’,’UT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NY’,’PA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘NY’,’VT’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘OH’,’PA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘OH’,’WV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘OK’,’TX’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘OR’,’WA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘PA’,’WV’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘SD’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘TN’,’VA’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘UT’,’WY’)
Insert into NeighborStates(StateCode,NeighborStateCode) values(‘VA’,’WV’)
–Insert any that were missing from the list by reversing the order
Insert into NeighborStates(StateCode,NeighborStateCode) (select NeighborStateCode,StateCode from NeighborStates x where not exists (select * from NeighborStates where StateCode=x.NeighborStateCode and NeighborStateCode=x.StateCode))
select * from NeighborStates
by Duncan on November 2nd, 2013 at 22:03
This was just what I was looking for. Thanks!
by Burney on November 23rd, 2013 at 21:58
This is the answer to the prayer of a poor graduate student, who just spent hours trying to figure this out. Thank you so much!