How to derive a region column (new) of a state from city and/or county in Base SAS 9.4

[inquistive]

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;

[Reeza]

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]

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]

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.