BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have a dataset with demographic data by CBSA (MSA is the old term). I want to overlay the data on a map, but I can't figure out how to get the X and Y coordinates for the CBSA/MSA boundaries.

Thoughts?
9 REPLIES 9
Darrell_sas
SAS Employee
SAS doesn't ship CBSA boundary files, however you can download these from the US Census website:
http://www2.census.gov/cgi-bin/shapefiles2009/national-files

You can import the shapefiles into a SAS data set with Proc MAPIMPORT.

And there is a data set matching up CBSA data and ZIP Code information on the SAS MapsOnLine website that might be useful to you also:
http://support.sas.com/rnd/datavisualization/mapsonline/html/misc.html
Ixion
Calcite | Level 5

Hi Darrell,

As a follow-up to this discussion, does SAS provide a zip to MSA name conversion file?  I see the files in SASHELP that provide ZIP to MSA code, but I'm looking for something that gives the name of the MSA.  The OMB has a file that provides the cross-walk but it is not in a SAS friendly format.  Are you aware of a pre-formatted file that is readily available to SAS users that have the MSA names?

Thanks,

Cathy

ballardw
Super User

After getting the MSA code from the SAS ZIPcode data set all you need is a file with the code and description. Which should be much easier to find than ZIP to MSA. Looks like they should be at http://www.census.gov/population/metro/data/def.html

You can read the file and select which set of discriptions you want. I would be tempted to make a format to map the code from the ZIP code data to the descriptions.

Ixion
Calcite | Level 5

I'm disappointed that SAS would require such steps to get the mapping.  Why do all the work of putting together a ZIP file and then leave MSA as a code?  It's like building a car and leaving out the steering wheel.

The file at the above site isn't formatted for import so I guess I know what I'm doing for the next hour.  It would have taken the authors only seconds to add a character for delimination or to create a fixed width to make import easy.

GraphGuy
Meteorite | Level 14

I'm not the one who puts together the sasehlp.zipcode file ... but, in general, it is a best practice to "normalize" data sets, and only include an id number ... and then have the longer text names looked-up or merged-in from another data set using the id# as the "key".  This way, rather than storing the long text names several times for the CBSA/MSAs that might encompass several zip codes, you only store the long text once.  This helps keep sashelp.zipcode smaller, and more manageable.

I agree - the TXT files containing the MSA names on the Census website are not easily readable (like much of the Census reports, they are more formatted for human readability rather than being conveniently machine-readable).  But they also have the data in XLS files on that same page, which would probably be easier to read into a SAS data set (assuming you have SAS/ACCESS to PC Files?)

MikeZdeb
Rhodochrosite | Level 12

Hi ... in case you have yet to get the MSA names that correspond to the MSAs listed in SASHELP.ZIPCODE, I used this SAS code with the attached text file that I found at  ...

http://www.census.gov/population/estimates/metro-city/99mfips.txt

data msa (index=(msa));

infile 'z:\99mfips.txt';

input @;

if find(_infile_,' MSA',50)  then input msa msa_desc & $60.;

else

if find(_infile_,' PMSA',50) then input dummy msa dummy msa_desc & $60.;

else delete;

drop dummy;

run;

data zipcode;

set sashelp.zipcode;

set msa key=msa / unique;

if _error_ then do;

   _error_ = 0;

   call missing(msa_desc);

end;

run;

If you use the data set ZIPCODE with ...

proc freq data=zipcode;

table msa*msa_desc / list missing;

run;

you'll see that all the observations that have a non-zero MSA get a name for the MSA (variable MSA_DESC).

Srinivasansuren
Calcite | Level 5

hello,

I did download the new zipcode file and I do not see the CBSA code in there. There is still only MSA code. Is there a SAS data set with CBSA code?

Thanks for your help!

louisehadden
Quartz | Level 8

Hi - I was actually the person who requested the CBSA crosswalk to begin with some years ago.  SAS has changed their zip code file vendor so some changes may have gotten lost - in any event, you can obtain a CBSA to county crosswalk from www.cms.gov, which is based on a fiscal year.  See attached, but search for newer ones as time goes on.  I hope this helps.  I simply create formats from this file using zip code or some other code (county code) to apply to my data.  This type of matching is ALWAYS inexact, as often zip codes can cross county boundaries, and so on, and you'll have to check your file for non-matches, etc., but it works pretty well.  As for boundaries you can download cbsa shapefiles and use PROC MAPIMPORT, download zcta3 shapefiles and use SAS/GRAPH to remove interior boundaries from cbsa boundaries, or use the SAS-supplied county file and use SAS/GRAPH to remove interior boundaries, depending on what level your data is on.  Note that MANY CBSAs cross state lines, so you may want to do something a little fancy in terms of replacing the state boundaries after removing the interior county boundaries, and so on.  Mike Zdeb's book shows an example of this and there are other examples on SAS MAPSONLINE and on Robert Allison's webpage.

Good luck!

Srinivasansuren
Calcite | Level 5

Thanks for the input. If I merge a zip code file with the crosswalk file you suggested by county name and statecode, use annotate and project it on a US map will it be a good approximation?

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 10444 views
  • 2 likes
  • 8 in conversation