topic Re: Using PROC FORMAT & PROC FREQ with mixed results - County Data in SAS Procedures

Using PROC FORMAT & PROC FREQ with mixed results - County Data

deltron — Wed, 17 Jul 2019 14:22:31 GMT

Hello, I'm currently using CPS ASEC data to tally all available counties in the data set. I combined state and county codes to create a standard FIPS State/County code and listed all counties in the US, including states containing unlisted counties (see code). This made for one very long PROC FORMAT.

I'm getting mixed results when I use and don't use the format. When the format is used, 241 levels are reported. When the format isn't used, 280 levels are reported. The 39 geographies that aren't in the original Proc Freq are listed in the Proc Format and are contained in the data set, so I don't understand why the original freq isn't displaying them.

Any insight appreciated.

data listed not_lstd; 
set work.ststst; 
if gxco = '000' then output not_lstd;
else if gxco not = '000' then output listed; 
run;

/*Gives frequencies for 329 levels identified and not-identified counties*/
proc freq data = work.ststst nlevels;
table stcofips / missing;
*no format;
run;

/*Gives frequencies for 241 identified counties*/
proc freq data = listed nlevels;
table stcofips / missprint;
format stcofips $counties. ;
*format stcofips $counties. ; /*280 levels when omitted*/ 
run; 

/*Gives frequencies for 49 not-identified counties within states*/proc freq data = not_lstd nlevels;
tables stcofips / missprint;
format stcofips $counties. ;
run;

Re: Using PROC FORMAT & PROC FREQ with mixed results - County Data

Astounding — Thu, 18 Jul 2019 00:01:56 GMT

This means that your format contains duplicated translations. The values to the left of the equal sign are different but the translated values to the right of the equal sign are the same. Applying the format collapses identical translated values into the same row of the table.

Re: Using PROC FORMAT & PROC FREQ with mixed results - County Data

ballardw — Thu, 18 Jul 2019 15:46:27 GMT

There are some very common county names such as Washington, Lincoln, Jefferson and Adams to pick on a few.

This code may help identify your problem cases.

proc sql;
   select distinct countynm, statename
   from sashelp.zipcode
   where state in (1:50)
   ;
quit;

Or other information, if available, coupled with the SASHELP.ZIPCODE data set might help resolve some of the FIPS issues.

Re: Using PROC FORMAT

deltron — Thu, 18 Jul 2019 17:48:37 GMT

This helps, as well. Thanks. Since it was only 39 counties, I went ahead and modified their respective format. I didn't realize PROC FORMAT would see the values to the right of the = sign as the same despite having a different FIPS ST code associated with it.