topic Re: Creating a Region Map from counties across multiple states in Graphics Programming

Creating a Region Map from counties across multiple states

AaroninMN — Wed, 08 May 2013 20:49:21 GMT

Is it possible to create a US map with regions from counties across multiple states? What I am trying to do is not combine entire states to create the region. Most of the regions I am creating combine two states with half of another. The data set I'm reading has approx 50,000 rows of county data by state.

Let me know if you need more info and Thank you in advance.

Aaron

Re: Creating a Region Map from counties across multiple states

GraphGuy — Thu, 09 May 2013 01:44:21 GMT

You can start with the county map data set, and add a 'region' variable, and then use Proc Gremove to get rid of internal boundaries (within the regions), and use the result as your new map. (You'll have to get your 'id' variables and 'by' variables just right - let me know if you need help with that.)

Re: Creating a Region Map from counties across multiple states

GraphGuy — Thu, 09 May 2013 12:45:46 GMT

OK - now that I'm back at a computer, I've put together a little sampel to demonstrate the steps & syntax you'll need. This sample has extra code to write out the map(s) via ods html, so you can have html hover-text which allows you to hover your mouse over the counties, to see what the county # is (this can help in trouble-shooting, and coming up with the list of counties you want to put in a different state, etc).

%let name=region;
filename odsout '.';

data my_map; set maps.uscounty (where=(fipstate(state) in ('NC' 'VA')));
original_order=_n_;
if fipstate(state)='NC' then region='A';
if fipstate(state)='VA' then region='B';
if fipstate(state)='VA' and county in (117 111) then region='A';
my_html='title='||quote(county);
run;

proc sort data=my_map out=my_map;
by region state county original_order;
run;

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

goptions device=png;

ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" style=sasweb;

proc gmap map=my_map data=my_map;
id state county;
choro region /
html=my_html
des='' name="&name";
run;

proc gmap map=my_map2 data=my_map2;
id region;
choro region /
des='' name="&name";
run;

quit;
ODS HTML CLOSE;
ODS LISTING;

Here's the output - a regular county map shaded by 'region', and then the new Region map with the county boundaries removed:

Re: Creating a Region Map from counties across multiple states

AaroninMN — Thu, 09 May 2013 14:28:38 GMT

Thanks Robert! I think I am getting close. This is my current code

PROC SQL;
CREATE TABLE WORK.Query_for_Query1_for_QUERY1913 AS SELECT QUERY1_FOR_QUERY1913.PEFR_ADR_ST_CDE,
QUERY1_FOR_QUERY1913.COUNTY,
QUERY1_FOR_QUERY1913.STATE,
QUERY1_FOR_QUERY1913.STATENAME,
QUERY1_FOR_QUERY1913.COUNTYNM,
QUERY1_FOR_QUERY1913.COUNTIES_GEO,
QUERY_FOR_QUERY.AGY_CODE AS Region,
QUERY1_FOR_QUERY1913.X,
QUERY1_FOR_QUERY1913.Y
FROM WORK.QUERY1_FOR_QUERY1913 AS QUERY1_FOR_QUERY1913,
WORK.TMP_0001 AS QUERY_FOR_QUERY
WHERE (QUERY1_FOR_QUERY1913.PEFR_ADR_ST_CDE = QUERY_FOR_QUERY.STATE AND QUERY1_FOR_QUERY1913.COUNTYNM = QUERY_FOR_QUERY.County)
ORDER BY QUERY_FOR_QUERY.AGY_CODE, QUERY1_FOR_QUERY1913.STATE, QUERY1_FOR_QUERY1913.COUNTY;
QUIT;

proc gremove
data=Query_for_Query1_for_QUERY1913
out=rfomap;
by region;
id region COUNTY STATE;
run;

PROC GPROJECT
DATA=RFOMAP
OUT=RFOMAP2
ID REGION;
RUN;

proc gmap
map=rfomap2
data=rfomap2;
id REGION;
choro region / nolegend;
run;
quit;

but this is my output:

Re: Creating a Region Map from counties across multiple states

GraphGuy — Thu, 09 May 2013 14:42:28 GMT

Within each county, the order of the observations is very important (this is the order in which the points along the border are connected).

You'll need to do a data step and get the original order, and then after you've done your sql stuff (and before you gremove) you'll need to ultimately sort by that original order (see code I posted earlier - it is a critical step).

Re: Creating a Region Map from counties across multiple states

AaroninMN — Thu, 09 May 2013 16:58:47 GMT

Robert, Thank You!!! That appears to have done the trick.

I changed my code to:

PROC SQL;
CREATE TABLE WORK.Query_for_Query1_for_QUERY1913 AS SELECT QUERY1_FOR_QUERY1913.PEFR_ADR_ST_CDE,
QUERY1_FOR_QUERY1913.COUNTY,
QUERY1_FOR_QUERY1913.STATE,
QUERY1_FOR_QUERY1913.STATENAME,
QUERY1_FOR_QUERY1913.COUNTYNM,
QUERY1_FOR_QUERY1913.COUNTIES_GEO,
QUERY_FOR_QUERY.AGY_CODE AS Region,
QUERY1_FOR_QUERY1913.X,
QUERY1_FOR_QUERY1913.Y,
QUERY1_FOR_QUERY1913.original_order
FROM WORK.QUERY1_FOR_QUERY1913 AS QUERY1_FOR_QUERY1913
RIGHT JOIN WORK.TMP_0008 AS QUERY_FOR_QUERY ON (QUERY1_FOR_QUERY1913.PEFR_ADR_ST_CDE = QUERY_FOR_QUERY.STATE) AND (QUERY1_FOR_QUERY1913.COUNTYNM = QUERY_FOR_QUERY.County)
ORDER BY QUERY_FOR_QUERY.AGY_CODE, QUERY1_FOR_QUERY1913.STATE, QUERY1_FOR_QUERY1913.COUNTY, QUERY1_FOR_QUERY1913.original_order;
QUIT;

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

PROC GPROJECT
DATA=RFOMAP
OUT=RFOMAP2;
ID REGION;
RUN;

proc gmap
map=rfomap2
data=rfomap2;
id REGION;
choro region;
run;
quit;

and got this output which is about what I was expecting: