Dear all,
I have a file a file with the states' and and countys' adherent level. I would like
1) to find the zipcodes for each of the observations.
2) perform a linear interpolation to replace the inherent values between missing years (for example, 1991 - 1999, 2001 - 2009, 2011 onwards) for both states' adherent level and countys' adherent level.
Could anyone suggest a solution for both or either of the above requirements?
Thank you.
Regards,
MSPAK
Hi, here's some ZIP info (I'll leave the interpolation to someone else).
There's a SAS-supplied data set, SASHELP.ZIPCODE, that has info you can use to find ZIPs associated with counties. Note, there are ZIPs that cross county boundaries and the ZIPCODE data set only lists one county per ZIP.
proc sort data=sashelp.zipcode (keep=state county zip zip_class) out=new (drop=zip_class);
by state county;
where missing(zip_class);
run;
gives you a data set with STATE, COUNTY, and ZIP (the WHERE statement eliminates PO BOXES and unique ZIPs that have no geography, as with a ZIP associated with a business).
If you use this ...
proc freq data=new;
tables state*county / noprint out=table;
run;
proc freq data=table;
table count;
run;
you'll see that about 75% of counties have 10 or fewer ZIPs. However, 5% have 30+ ZIPs (the maximum is 289 ZIPs in a single county). Not sure how you will use this data and that will determine how you would organize a lookup file. One way would be to transpose the data ...
proc transpose data=new out=new ( drop=_: ) prefix=zip;
var zip;
by state county;
run;
and you would have a data set with variables STATE, COUNTY, ZIP1-ZIP289. There would be a lot of missing data among the ZIPs since only 25% of the STATE/COUNTY combinations have 10+ ZIPs, for example here's observation #1 ...
Obs STATE COUNTY zip1 zip2 zip3 zip4 zip5 zip6 zip7 zip8 zip9 zip10 <more>
1 1 1 36003 36006 36008 36051 36066 36067 36749 . . . <more>
ZIP8 through ZIP289 are missing.
If you try the folowing on the tranpsoed data set, you'll see the STAT/COUNTY with the 289 ZIPs ...
proc print data=new ;
var state county zip1-zip10 zip289;
where zip289 is not missing;
run;
You can do some reading ...
ZIP Code 411: A Well-Kept SAS® Secret
http://www2.sas.com/proceedings/sugi31/143-31.pdf
ZIP Code 411: Decoding SASHELP.ZIPCODE and Other SAS® Maps Online Mysteries
http://support.sas.com/resources/papers/proceedings10/219-2010.pdf
Hi, here's some ZIP info (I'll leave the interpolation to someone else).
There's a SAS-supplied data set, SASHELP.ZIPCODE, that has info you can use to find ZIPs associated with counties. Note, there are ZIPs that cross county boundaries and the ZIPCODE data set only lists one county per ZIP.
proc sort data=sashelp.zipcode (keep=state county zip zip_class) out=new (drop=zip_class);
by state county;
where missing(zip_class);
run;
gives you a data set with STATE, COUNTY, and ZIP (the WHERE statement eliminates PO BOXES and unique ZIPs that have no geography, as with a ZIP associated with a business).
If you use this ...
proc freq data=new;
tables state*county / noprint out=table;
run;
proc freq data=table;
table count;
run;
you'll see that about 75% of counties have 10 or fewer ZIPs. However, 5% have 30+ ZIPs (the maximum is 289 ZIPs in a single county). Not sure how you will use this data and that will determine how you would organize a lookup file. One way would be to transpose the data ...
proc transpose data=new out=new ( drop=_: ) prefix=zip;
var zip;
by state county;
run;
and you would have a data set with variables STATE, COUNTY, ZIP1-ZIP289. There would be a lot of missing data among the ZIPs since only 25% of the STATE/COUNTY combinations have 10+ ZIPs, for example here's observation #1 ...
Obs STATE COUNTY zip1 zip2 zip3 zip4 zip5 zip6 zip7 zip8 zip9 zip10 <more>
1 1 1 36003 36006 36008 36051 36066 36067 36749 . . . <more>
ZIP8 through ZIP289 are missing.
If you try the folowing on the tranpsoed data set, you'll see the STAT/COUNTY with the 289 ZIPs ...
proc print data=new ;
var state county zip1-zip10 zip289;
where zip289 is not missing;
run;
You can do some reading ...
ZIP Code 411: A Well-Kept SAS® Secret
http://www2.sas.com/proceedings/sugi31/143-31.pdf
ZIP Code 411: Decoding SASHELP.ZIPCODE and Other SAS® Maps Online Mysteries
http://support.sas.com/resources/papers/proceedings10/219-2010.pdf
Hi MikeZdeb,
Thank you for the program. I manage to get a list of the zipcode from SASHELP. It is not a need to transpose the data. I will merge the SASHELP data with my dataset.
Thank you.
MSPAK
For number 1, I am not sure what you have. What are your observations?
Is it the lat/long of points? Is it the state/county names? Do you have a map of counties? Or something else?
If it is counties and you want the ZIP Code of the county, is there anything in your data that links the two?
You could use the SASHELP.ZIPCODE file to look up the name of the state and county. But there are multiple ZIP Codes per county.
Or if you don't have the name of the county, you could use SAS/Graph and Proc GINSIDE, If you have a map of counties (in the Library MAPSGFK) and then a point file of ZIP Codes (that is in SASHELP). It will find the county that each point falls in. But again, there are multiple ZIP Codes per county.
So, I need to know what is in your data. There are probably other ways.
Note, the ZIP Code changes over different months and years. For example, one ZIP Code might become split and be 2 or 3.
If you are looking at historical records, a ZIP Code now may not have been there a year ago.
Hi Darrell,
I do not have the lat/long points, but I have the FIPS code for each county and state.
Thank you.
MSPAK
MSPAK,
To follow up on Darrell's comment, SAS issues replacement copies of SASHELP.ZIPCODES periodically (one came out last week). The USPS issues their ZIP Code revisions every 6 months. I don't know the lag between the USPS posting theirs and the SAS distribution.
Mike's elimination of the zip_class records needs to be considered for your application. A number of the single use ZIP codes actually have people living at them (college campuses).
You will also find that a few ZIP codes cross state lines, not just county lines.
Doc Muhlbaier
Duke
Hi. There's good info in the new postings. If you want to get a free copy of a cross-walk file for ZCTAs (not true ZIPs, but the US Census Zip Code Tabulation Areas) to county that will include multiple counties for ZCTAs that cross county boundaries, try ...
http://mcdc.missouri.edu/websas/geocorr12.html
I generated a CSV file (attached) for Delaware. Delaware has 4 ZIPs that cross county boundaries: 19938, 19950, 19963, 19977. I know that from another source ... http://www.melissadata.com/lookups/CountyZip.asp
The CSV file generated by the Missouri web site has two entries for each of those ZIPs, for example ...
"19977","10001","Kent DE","Smyrna, DE",18630,0.811
"19977","10003","New Castle DE","Smyrna, DE",4354,0.189
shows that ZIP 19977 is in counties 1 and 3 (Delaware is FIPS state 10, so the fill county numbers are 10001 and 10003).
You can generate a CSV file for all states just as easily as doing one. You can get more info about ZCTAs vs ZIPs at ...
ZIP Code Tabulation Areas (ZCTAs)
https://www.census.gov/geo/reference/zctas.html
Hi MikeZdeb,
Thank you for the database. You are so resourceful!
MSPAK
Hello MsPak, I have answered your interpolation question in your other thread at
https://communities.sas.com/t5/Base-SAS-Programming/Linear-interpolation/m-p/225682#M40531
but because of the recent changes to the Forums here, I'm not surprised you didn't see it because posts with replies that are many hours or days after the original post tend to get buried. (cc: @LainieH )
Hi PaigeMiller,
Thank you for your program.
Yes, I overlooked the messages due to the recent changes to this forum. In the past, I manage to view my own posts easily. Now, I need to spend time to find my own postings.
Regards,
MSPAK
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: