topic Re: Contiguous CBSA Identification in Graphics Programming

Contiguous CBSA Identification

jacob_klimek — Thu, 02 Jun 2016 17:44:38 GMT

Hello,

I am working on a map of the United States with CBSA lines set in SAS EG (I think v9?). This is really my first time trying to tweak with the code other than changing patterns/colors/titles etc. so I don't have much experience to work off of. Basically, I am trying to use the CBSA codes I have in combination with the CBSA locations that are drawn on the map to make a new dataset that has each CBSA code and ALL of the CBSAs that it is contiguous to (borders touch) in a horizontal dataset.

Example scenario. There are four CBSAs, 10000,10001,10002,10003. 10003 touches all the others, 10002 touches 10003 and 10001, 10000 only touches 10003. From this, I want a dataset that looks like this:

MAIN_CBSA CONT_CBSA1 CONT_CBSA2 CONT_CBSA3

10000 10003

10001 10002 10003

10002 10001 10003

10003 10000 10001 10002

The on idea I had was to take the coordinates dataset I have and try to take observations that have the same x and y values and write wide the cbsa code for each but I don't know how to do this.

data that I have:

x y cbsa_name cbsa_code

1 2 New York 10001

1 2 Brooklyn 10002

data I want:

x y cbsa_name cbsa_code cont_cbsa1

1 2 New York 10001 10002

1 2 Brooklyn 10002 10001

I just don't know how to generate variables for each observation based off of the x and y.

The order of the cont_cbsa* variable doesn't matter. I just need them all in a row, because eventually I will be pulling a count of patients (for medicare) across each of the CBSAs and want to know whether a sum of the main_cbsa and any of the others equals 500+. If this isn't doable that's totally fine I just thought I'd give it a shot.

Thanks

Re: Contiguous CBSA Identification

ballardw — Thu, 02 Jun 2016 17:57:16 GMT

What will you actually do with the resulting data set in more detal? The type of structure you are proposing is often much harder to work with in the long run.

It sounds like you are looking to create "groups" but your "results" makes two different groups by name with the same components and that usually doesn't make sense.

Can you provide some dummy data of both inputs and the desired result?

Re: Contiguous CBSA Identification

jacob_klimek — Thu, 02 Jun 2016 18:31:38 GMT

I think I basically figured it out actually. The x and y are the coordinate system used for each boundary. I just that in every case when a boundary shares two points the cbsas are contiguous.

data boundaries2;set boundaries;

keep x y cbsa_cbsa;

run;

proc sql; create table boundary_test as

select distinct a.cbsa_cbsa,b.cbsa_cbsa as cbsa2

from boundaries2 a

left outer join boundaries2 b on a.x=b.x and a.y=b.y;

quit;

proc transpose data=boundary_test prefix=cont_cbsa;

by cbsa_cbsa;

var cbsa2;

run;

Re: Contiguous CBSA Identification

ballardw — Thu, 02 Jun 2016 19:55:42 GMT

If you are looking to combine these you could add a region (or similar idea) to your boundary data (make a NEW set in case of errors) based on the values of your CBSA.

The Proc Gremove can be used to remove internal boundaries and just show the region.

Be very careful processing map boundary data sets with SQL. The results often are not in the order required to draw a map afterward.