BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AaroninMN
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
GraphGuy
Meteorite | Level 14

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:

region.png

region1.png

View solution in original post

5 REPLIES 5
GraphGuy
Meteorite | Level 14

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.)

GraphGuy
Meteorite | Level 14

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:

region.png

region1.png

AaroninMN
Obsidian | Level 7

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:

SASGraph.jpg

GraphGuy
Meteorite | Level 14

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).

AaroninMN
Obsidian | Level 7

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:

SASGraph2.jpg

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2219 views
  • 3 likes
  • 2 in conversation