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?
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.
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.
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?)
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 ...
data msa (index=(msa));
if find(_infile_,' MSA',50) then input msa msa_desc & $60.;
if find(_infile_,' PMSA',50) then input dummy msa dummy msa_desc & $60.;
set msa key=msa / unique;
if _error_ then do;
_error_ = 0;
If you use the data set ZIPCODE with ...
proc freq data=zipcode;
table msa*msa_desc / list missing;
you'll see that all the observations that have a non-zero MSA get a name for the MSA (variable MSA_DESC).
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.