topic Re: Transform Data by Zip Code into US Regions in Graphics Programming

Transform Data by Zip Code into US Regions

P5C768 — Wed, 06 Jun 2012 22:24:38 GMT

I have a data set that is listed out by zip code as the mapping coordinate but I would like to group those zip codes into a small number of regions (say 5 or 10). Is there a way to do this using the zipcodes or do I need to develop a shape file to define the regions?

For example, my data is similar to this:

Zip	Region	Count
90001	South	50
90002	South	75
90003	South	60
90004	West	75
90005	East	100

Can I use the zip codes to define the Regions and then sum the counts to display on the map? Or do I need to create something similar to the coordinates in the maps.us table to define regions? Thank you in advance for any help provided.

Re: Transform Data by Zip Code into US Regions

Reeza — Wed, 06 Jun 2012 23:00:14 GMT

You can use the sashelp.zip file to merge the zips into county or states. If you want different regions you need to figure out a way to group them individually, thought I'd think county would be a good way to start (knowing very little about this b/c I'm Canadian).

Re: Transform Data by Zip Code into US Regions

Alpay — Thu, 07 Jun 2012 07:54:58 GMT

If you have zip code and regions defined in a file like sashelp.zipcode you can create a format and use it to summarize your counts.

Zafer

Example:

data zipregion;

input Zip Region $;

datalines;

90001 South

90002 South

90003 South

90004 West

90005 East

;

run;

data zipcount;

input Zip Count;

datalines;

90001 50

90002 75

90003 60

90004 75

90005 100

;

run;

/* If you have zip codes and regions defined in a data set */

data zipfmt;

set zipregion;

retain fmtname 'zipfmt';

Start = Zip;

Label = Region;

keep fmtname Start Label;

run;

proc format cntlin=zipfmt;

run;

proc summary data=zipcount nway missing;

class Zip;

var Count;

format Zip zipfmt.;

output out=zipcount_summary

sum=;

run;

proc print; run;

Re: Transform Data by Zip Code into US Regions

P5C768 — Mon, 11 Jun 2012 17:57:15 GMT

Sorry, I should have been more clear. Once you have the data summarized by region, how can I link the zip codes with a map file, say sashelp.zipcode and only display regions rather than zip codes. Basically I want to define my map parameter by zip code, but display region rather than zip code, using proc gmap.

Re: Transform Data by Zip Code into US Regions

P5C768 — Mon, 11 Jun 2012 18:20:18 GMT

What I am trying to do is similar to what is done in this example:

http://support.sas.com/kb/24/902.html

But instead of writing out all 40,000 zipcodes, could I just group them somehow so the regions are assigned by a region field that is related to a zip code.

Re: Transform Data by Zip Code into US Regions

art297 — Mon, 11 Jun 2012 18:40:14 GMT

Can't you just use the zipstate function (see http://support.sas.com/kb/24/803.html ) and then do the region assignments as was done in the example that you showed?

Re: Transform Data by Zip Code into US Regions

P5C768 — Mon, 11 Jun 2012 19:13:58 GMT

Some of my regions are not defined by states unfortunately. California is split between two regions for example. That is why I thought of defining my regions by zip code.

Re: Transform Data by Zip Code into US Regions

Astounding — Mon, 11 Jun 2012 20:46:09 GMT

Doesn't Art have the right idea here? What if you were to use the code in the sample program, but change from ID STATE to ID ZIP? Isn't that exactly what you need? (You might have to sum up your COUNT variable separately.)

Re: Transform Data by Zip Code into US Regions

Alpay — Mon, 11 Jun 2012 21:01:04 GMT

If I unserstand it right, you want to drill down from region to zip codes and color it on a map and remove the inerior boundaries for regions. Do these regions have discontiguous areas?

You will probably need shape files for zip codes (www.census.gov).

Re: Transform Data by Zip Code into US Regions

Alpay — Tue, 12 Jun 2012 07:43:33 GMT

For example:

Download the following file from www.census.gov web site (2010 TIGER/Line® Shapefiles: )

2012 TIGER/Line Shapefiles: ZIP Code Tabulation Areas for Michigan.

You can download the shape files for other states as well and define your zip code to region format.

proc format;

value $zipfmt

'48001'-'48999' = 'Region 1'

'49000'-'49999' = 'Region 2'

other = 'Region 3';

run;

proc mapimport datafile="D:\Temp\Zip\tl_2010_26_zcta510.shp"

out=mi;

run;

proc sql;

create table mi_1 as

select -1*x as x,

ZCTA5CE10 as Zip,

Segment,

put(ZCTA5CE10,$zipfmt.) as Region

from mi;

run;

proc sort data=mi_1;

by Region Zip Segment;

run;

proc gremove data=mi_1 out=mi_2;

by Region;

id Zip Segment;

run;

proc gproject data=mi_2 out=mi_prj degrees;

id Region;

run;

goptions device=png htext=3 gunit=pct cback=verylightmoderateyellow;

title;

proc gmap data=mi_prj map=mi_prj;

id Region;

choro Region / discrete

coutline=Black

nolegend;

run;

Re: Transform Data by Zip Code into US Regions

GraphGuy — Tue, 12 Jun 2012 12:41:44 GMT

And here's a similar example to Alpay's, using a county map (which is shipped with SAS/Graph), rather than zip code maps - it plots the map at a couple of points along the way, so you can see how we're building up the final map:

data my_map; set maps.uscounty (where=(fipstate(state) in ('NC' 'VA')));
original_order=_n_;
statecode=fipstate(state);
run;

proc gmap data=my_map map=my_map;
id state county;
choro state / discrete;
run;

data new_map; set my_map;
if statecode='VA' then region='A';
if statecode='NC' then region='B';

/* let's say these 4 counties in NC are really in region 'A' (with Virginia) */
if statecode='NC' and county in (77 69 181 185) then region='A';
run;

proc gmap data=new_map map=maps.uscounty;
id state county;
choro region;
run;

proc sort data=new_map out=new_map;
by region original_order;
run;

proc gremove data=new_map out=new_map;
by region;
id state county;
run;

proc gmap data=new_map map=new_map;
id region;
choro region;
run;

Re: Transform Data by Zip Code into US Regions

P5C768 — Wed, 13 Jun 2012 19:56:52 GMT

The methods that alpay and Robert listed work, I guess I'm just struggling with how to convert the 40k plus zip codes into an array. Alpay's example assumes region include sequential zip codes, which mine do not. Here is an output of a proc means to show the range:

Analysis Variable : ZIP The 5-digit ZIP Code
Region	N	Minimum	Maximum
Region1	5802	7002	61378
Region2	9786	544	88595
Region3	154	99501	99950
Region4	5191	19930	74966
Region5	7386	1005	78962
Region6	4709	59001	99403
Region7	1649	24314	49289
Region8	1159	501	93599
Region9	5498	39426	96158

Robert's example assumes that you only want to change a handful amount of zip or counties, rather then setting the region by a range of zip codes. I ran a proc transpose to convert my data to be in columns with each row representing a region. I'm thinking maybe I can create an array of each row, though some regions have nearly 10k records and I'm not sure what the limit would be. That way I could use Robert's method to set my regions by the zipcodes in my data.

Re: Transform Data by Zip Code into US Regions

Reeza — Wed, 13 Jun 2012 20:08:10 GMT

If you want to use Alpay's code and need to recreate this portion there's way to do that without using ranges:

proc format;

value $zipfmt

'48001'-'48999' = 'Region 1'

'49000'-'49999' = 'Region 2'

other = 'Region 3';

run;

My guess is you currently have data in the form something like the following:

Region ZipCode

1 48001

2 28003

3 34564

1 48002

Does that sound correct? If so you can create a format then use Alpay's code a few days ago, June 7 rather than mess with an array.

Re: Transform Data by Zip Code into US Regions

P5C768 — Wed, 13 Jun 2012 20:31:34 GMT

Correct, but my regions don't contain continuous zip codes.

I can't do this:

proc format;

value $zipfmt

'07002'-'61378' = 'Region 1'

'00544'-'88595' = 'Region 2'

run;

because the regions would overlap. That is why I was thinking of an array.

Re: Transform Data by Zip Code into US Regions

Alpay — Wed, 13 Jun 2012 20:42:40 GMT

You can create a format from your data set using proc format and specifying your format data set (cntlin option).

For further explanation please see the documentation for proc format in help files.

I have not tested the following code but it gives an idea as a starting point.

data zipfile;

input Region ZipCode $;

datalines;

1 48001

2 28003

3 34564

1 48002

;

run;

data zipfmt;

set zipfile;

retain zipfmt '$zipfmt';

Start =Zipcode;

Label = Region;

keep fmtname Start Label;

run;

proc format cntlin=zipfmt; run;

Re: Transform Data by Zip Code into US Regions

P5C768 — Wed, 13 Jun 2012 21:57:28 GMT

I see how this would work, but if I'm correct you would need data by zip code for this to work, correct? I have data by my region label and I am trying to use a separate data set that links zip codes to my regions.

Re: Transform Data by Zip Code into US Regions

Reeza — Wed, 13 Jun 2012 22:09:55 GMT

So you don't have a mapping of zipcodes to regions?

If you do, then merge the information together.

Re: Transform Data by Zip Code into US Regions

P5C768 — Thu, 14 Jun 2012 15:26:19 GMT

I have a mapping of zip codes to regions, but I have 9 regions and 40k zip codes, so my choro variable will be repeated 40k times. My thought was to use the zip codes to create my regions on the map, then add the data by region, but I guess that won't work.

My other idea was to try and concatenate all the zip codes into a string so that the format option would work. However, one of my regions has nearly 10k zip codes, so at 5 digits a piece, it exceeds the maximum string length.

I guess this just won't work. It sound like I need to work on getting data by zip code, rather than region. Then maybe I can use the format option to create my regions and sum my data by region.

Re: Transform Data by Zip Code into US Regions

MikeZdeb — Thu, 14 Jun 2012 16:40:46 GMT

hi ... here is something similar to what you are trying to do

me ... take the counties in New York State and form new geographic areas (HSAs) based on a set of rules that assign counties to HSAs

you ... take zip codes in US and and form new geographic areas (regions) based on a set of rules that assign zip codes to regions

I assume that you have a map data set with zip code boundaries

you will have to create a new map data set with regions and you do that with PROC GREMOVE (see below) ... then you could create a choropleth map that is shaded by region based on you response data set

this code started with a county-based map from the SAS MAPS library (maps.counties) and ends up with an HSA-based map ... 8 HSAs are shown instead of the 62 New York State counties

this example is in a paper ... "The Basics of Map Creation with SAS/Graph" ... and you can get the paper plus all the examples at ...

http://www.albany.edu/~msz03/map_basics.zip

* rules for county to HSA conversion;

proc format;

value cou2hsa

3, 9, 13, 29, 37, 63, 73, 121 = '1'

15, 51, 55, 69, 97, 99, 101, 117, 123= '2'

11, 23, 43, 45, 49, 53, 65, 67, 75, 89, 109 = '3'

7, 17, 107 = '4'

1, 19, 21, 25, 31, 33, 35, 39, 41, 57, 77, 83, 91, 93, 95, 113, 115 = '5'

27, 71, 79, 87, 105, 111, 119 = '6'

5, 47, 61, 81, 85 = '7'

59, 103 = '8'

;

* assign new geographic designation (HSA) to each New York State county from maps.counties data set;

data hsatemp;

set maps.counties;

where state eq 36 and density lt 6;

hsa = put(county,cou2hsa.);

run;

* sort map data set in order by new geographic designation;

proc sort data=hsatemp;

by hsa;

run;

* eliminate county boundaries with HSA areas using PROC GREMOVE;

proc gremove data=hsatemp out=hsamap;

by hsa;

id county;

run;

* project the map;

proc gproject data=hsamap out=hsaproj;

id hsa;

run;

* create a dummy response data set ... one observation per new area;

proc sql;

create table hsa as

select distinct hsa, 1 as var

from hsamap;

quit;

* draw the map;

goptions reset=all ftext='calibri' htext=2 gunit=pct;

pattern v=ms c=blue;

proc gmap

data=hsa

map=hsaproj;

id hsa;

choro var / discrete coutline=white nolegend;

note ' HEALTH SERVICE AREAS'

j=l ' AREAS FORMED FROM COUNTIES'

j=l ' USING PROC GREMOVE';

run;

quit;

Re: Transform Data by Zip Code into US Regions

P5C768 — Thu, 14 Jun 2012 18:12:36 GMT

I changed the first part to:

proc format;

value cou2hsa

99501, 99502, 99503, 99504, 99505, 99506, 99507, 99508, 99509, 99510, 99511, 99513, 99514, 99515, 99516, 99517, 99518, 99519, 99520, 99521, 99522, 99523, 99524, 99529, 99530, 99540, 99556, 99557, 99566, 99567, 99568, 99572, 99573, 99574, 99575, 99577, 99586, 99587, 99588, 99599, 99603, 99605, 99610, 99611, 99615, 99619, 99627, 99629, 99631, 99635, 99639, 99640, 99645, 99647, 99652, 99653, 99654, 99656, 99663, 99664, 99667, 99668, 99669, 99672, 99674, 99675, 99676, 99677, 99682, 99683, 99686, 99687, 99688, 99689, 99691, 99693, 99694, 99695, 99697, 99701, 99702, 99703, 99704, 99705, 99706, 99707, 99708, 99709, 99710, 99711, 99712, 99714, 99716, 99720, 99724, 99725, 99729, 99730, 99731, 99732, 99733, 99737, 99738, 99740, 99743, 99744, 99755, 99756, 99757, 99758, 99760, 99764, 99767, 99768, 99774, 99775, 99776, 99777, 99779, 99780, 99781, 99790, 99801, 99802, 99803, 99811, 99820, 99821, 99824, 99825, 99826, 99827, 99829, 99830, 99832, 99833, 99835, 99836, 99840, 99841, 99850, 99901, 99903, 99918, 99919, 99921, 99922, 99923, 99925, 99926, 99927, 99928, 99929, 99950 = 'Region1'

other = 'Other Regions';

Run;

data hsatemp;

set sashelp.zipcode;

hsa = put(ZIP,cou2hsa.);

run;

That seems to work up until gproject, where I get this error:

ERROR: At least one value is out of range for polar radian coordinates. Data may already be projected.

ERROR: Expected range is (-3.141592654, 3.1415926536) for X and (-1.570796327, 1.5707963268) for Y.

ERROR: Actual range is (-176.658889, 171.062651) for X and (5.310246, 70.66976) for Y.

I think the problem comes from the fact that all these examples use shapefiles whereas the sashelp.zipcode uses center of zip