topic Re: Function for FIPS codes by County in SAS Procedures

Function for FIPS codes by County

DavidPhillips2 — Wed, 13 Jun 2018 21:04:30 GMT

Is there a proc that returns the FIPS code with inputs for state, county like fips(VA, Amelia) returns 24393.

Re: Function for FIPS codes by County

Reeza — Wed, 13 Jun 2018 21:55:46 GMT

Close but no cigar 😞

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm&docsetVersion=9.4&locale=en

State and ZIP code	FIPNAME Function	Converts two-digit FIPS codes to uppercase state names.
	FIPNAMEL Function	Converts two-digit FIPS codes to mixed case state names.
	FIPSTATE Function	Converts two-digit FIPS codes to two-character state postal codes.
	STFIPS Function	Converts state postal codes to FIPS state codes.
	STNAME Function	Converts state postal codes to uppercase state names.
	STNAMEL Function	Converts state postal codes to mixed case state names.
	ZIPCITY Function	Returns a city name and the two-character postal code that corresponds to a ZIP code.
	ZIPCITYDISTANCE Function	Returns the geodetic distance between two ZIP code locations.
	ZIPFIPS Function	Converts ZIP codes to two-digit FIPS codes.
	ZIPNAME Function	Converts ZIP codes to uppercase state names.
	ZIPNAMEL Function	Converts ZIP codes to mixed case state names.
	ZIPSTATE Function	Converts ZIP codes to two-character state postal codes.

Re: Function for FIPS codes by County

ballardw — Wed, 13 Jun 2018 23:06:07 GMT

Query the SASHELP.Zipcode data set:

proc sql;
   select distinct catt(put(state,z2.),put(county,z3.)) as FIPS
   from sashelp.zipcode
   where statecode='VA' and Countynm='Amelia';
quit;

or do a join on your data.

I used the catt and put as the values for state and county are numeric and would not appear as the correct 5 characters for a county FIPS code. Obviously if only want the county code then just use County.

There are lots of other things in that set of interest as well. One reason to use DISTINCT is that each City/zipcode combination would generate a value.

Re: Function for FIPS codes by County

Reeza — Thu, 14 Jun 2018 03:50:07 GMT

If the data exists you can roll your own using PROC FCMP or a format.

Re: Function for FIPS codes by County

DavidPhillips2 — Thu, 14 Jun 2018 13:10:20 GMT

Trying to follow. I need to convert United States, Alabama, Autauga to US-01001 I'm not sure if that is a ZIP code or something else called FIPS code. I am reading the documentation in this thread.

Re: Function for FIPS codes by County

DavidPhillips2 — Thu, 14 Jun 2018 13:38:10 GMT

It took me a while to understand the code is a combination of two codes state and county. Maybe there is more than one type of FIPS code and county codes are two digits.

Re: Function for FIPS codes by County

ballardw — Thu, 14 Jun 2018 15:31:02 GMT

@DavidPhillips2 wrote:

It took me a while to understand the code is a combination of two codes state and county. Maybe there is more than one type of FIPS code and county codes are two digits.

FIPS codes are of variable lengths depending on purpose or use. The first two characters, which should include a leading zero are the "state", and if the purpose is for state only use may stop there. The next three characters identify a county. When the county FIPS codes were created all the initial codes were odd, 001, 003, 005 etc and assigned alphabetically, to allow a number of additional counties to be added and have a chance of maintaining alphabetical order since new counties don't get created very often. Even if a state only has 4 counties the county part of the FIPS would be 001/3/5 or 7.

Additional characters after the first 5 may identify such things as bridges, rest stops, road-side historical markers (you might make a guess where my education on this came from), parks, federal buildings/facilities and others. The added information would come after the state+ county but I don't remember how specific the rules were for number of characters and formats if any.

Also note that the "state" part of the FIPS is not 01 to 50 as US protectorates such as Guam, Puerto Rico, Virgin Islands as well as Washing DC receive their own 2 character "state" code and some areas not counties (Louisiana parishes and Virgin Island islands for example) will be in the place of the 3 character "county" portion,

The additional part of your needed string should be relatively easy to prefix to my example.

Re: Function for FIPS codes by County

DavidPhillips2 — Fri, 15 Jun 2018 17:28:11 GMT

Is there a way to create the standard naming for text like "Amelia US-01038" from the three digit FIPS county code? I need to make sure that my name in my table for display has the same county name as that of the SAS tables. So if there is a function for this I can pair it up perfectly.

Re: Function for FIPS codes by County

Reeza — Sat, 16 Jun 2018 22:33:53 GMT

To verify results, what's the actual FIPS code for Amelia, VA?

51007?

01038?

24393?

@DavidPhillips2 wrote:

Is there a way to create the standard naming for text like "Amelia US-01038" from the three digit FIPS county code? I need to make sure that my name in my table for display has the same county name as that of the SAS tables. So if there is a function for this I can pair it up perfectly.

If the FIPS is, from what I'm reading, a combination of State + County codes then I would build a custom format and then use that to call it up as needed. The SASHELP.ZIPCODE table will work.

You could probably use a hash lookup with two parameters to a user defined function, FCMP instead but I'm not very good at hash solutions.

Here's an example of how that works:

*remove duplicate state/county records;
proc sort data=sashelp.zipcode out=fips_data nodupkey;
    by countynm statecode;
run;

*create a format table for PROC FORMAT;
data custom_fips_fmt;
    set fips_data;
    fmtname='fips_fmt';
    start=catx(', ', countynm, statecode);
    label=catt(state, put(county, z3.));
    type='C';
run;

proc format cntlin=custom_fips_fmt;
run;

*demo usage;
data demo;
    County='Amelia';
    State='VA';
    fips=put(catx(', ', county, state), $fips_fmt.);
run;

proc print data=demo;
run;