ZIPCODE FOR EACH COUNTY and INTERPOLATION

[mspak]

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

[MikeZdeb · Accepted solution]

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

http://www.sascommunity.org/wiki/ZIP_Code_411:_Decoding_SASHELP.ZIPCODE_and_Other_SAS%C2%AE_Maps_Onl...

[mspak]

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

[Darrell_sas]

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.

[mspak]

Hi Darrell,

I do not have the lat/long points, but I have the FIPS code for each county and state.

Thank you.

MSPAK

[Doc_Duke]

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

[MikeZdeb]

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

[mspak]

Hi MikeZdeb,

Thank you for the database. You are so resourceful!

MSPAK

[PaigeMiller]

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 )

[mspak]

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