BookmarkSubscribeRSS Feed
inquistive
Quartz | Level 8

I want to derive a new column 'Region'. I have City, County and State columns available in the source table. Is there a way to do so in SAS and/or proc sql ? Any help is much appreciated.

Here is a sample select statement(For eg: need to derive RTP, Triad etc. regions of North Carolina):

Proc sql;

Create tbl1 as select full name, address, city, state, zip, county, /*how to derive 'region' column from available columns?*/

from tbl2 ;

quit;

3 REPLIES 3
Reeza
Super User
Your problem is unclear. WHat is RTP? Triad?

If those are defined spatial area's you may want to get the SHP files from public sources. If you're working across multiple states that will likely mean dealing with multiple SHP files. How familiar are you with spatial data and analytics? Or if you have a file that maps ZIPs to different regions that's also useful.

I'm Canadian and our Stats Bureau produces a file called a PCCF which maps our Canadian Postal Codes to a variety of regions such as the census divisions then our local government also processes the data to include neighbourhoods, and custom area's for our province.
inquistive
Quartz | Level 8
Thank you for your prompt and insightful response and pointing out caveats in my question.
Those are regions of a state (North Carolina) in USA.
To the question about my knowledge on spatial data and analytics, I should not hesitate to say that I am just a curious learner with no professional exposure at all.
Reeza
Super User

If you're interested in NC data then start here then:
https://www.nconemap.gov/

This section has a variety of different boundaries for NC
https://www.nconemap.gov/search?groupIds=c2416e452eb54e519d7e5eaacf99fa2c

How you would use these overall depends on your use case.

 

You can map your zip codes to the zip data set in SAS maps library, and then use the ZIP centroids with PROC GINSIDE and the spatial layers of your choice to assign regions. 

Or you can geocode your street addresses using PROC GEOCODE to get more accurate location data and then use PROC GINSIDE. 

 

PROC GEOCODE

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/grmapref/n02y3yabtlqatsn16gp2fo51yo7p.htm

 

PROC GINSIDE

https://documentation.sas.com/doc/en/pgmmvacdc/9.4/grmapref/p0qjcc8hugcjb2n1x3bmuaar16f0.htm

 

And these are infinitely easier in a GIS tool - QGIS is free and quite robust.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 784 views
  • 0 likes
  • 2 in conversation