BookmarkSubscribeRSS Feed
ernie86
Calcite | Level 5

I have datasets a & b as following. How do I get dataset c ?

dataset a

Prg_ID State
1004 AL
1005 AL
1007 MI
1008 MI
1009 MI

 

dataset b

State ZipCode
AL 35004
AL 35005
AL 35006
AL 35007
AL 35010
AL 35011
AL 35013
MI 48871
MI 48872
MI 48873

 

dataset c

Prg_ID State ZipCode
1004 AL 35004
1004 AL 35005
1004 AL 35006
1004 AL 35007
1004 AL 35010
1004 AL 35011
1004 AL 35013
1005 AL 35004
1005 AL 35005
1005 AL 35006
1005 AL 35007
1005 AL 35010
1005 AL 35011
1005 AL 35013
1007 MI 48871
1007 MI 48872
1007 MI 48873
1008 MI 48871
1008 MI 48872
1008 MI 48873
1009 MI 48871
1009 MI 48872
1009 MI 48873
2 REPLIES 2
Astounding
PROC Star

When you want a many-to-many match, the best tool for the job is usually SQL.  For example:

 

proc sql;

create table c as

select a.*, b.zipcode

where a.state=b.state;

run;

Reeza
Super User

EDIT: I misunderstood the question, so this isn't relevant but may be helpful 🙂

 

What version of SAS are you using?

 

Do you want to use Dataset B for sure?

If this is homework, then probably. If not, SAS provides several datasets and functions for working with Zipcodes and spatial US data. 

 

In 9.3+ there are several functions available here:

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p0w6napahk6...

 

State and ZIP code FIPNAME Function Converts two-digit FIPS codes to uppercase state names.
FIPNAMEL Function Converts two-digit FIPS codes to mixed case state names.
FIPSTATE Function Converts two-digit FIPS codes to two-character state postal codes.
STFIPS Function Converts state postal codes to FIPS state codes.
STNAME Function Converts state postal codes to uppercase state names.
STNAMEL Function Converts state postal codes to mixed case state names.
ZIPCITY Function Returns a city name and the two-character postal code that corresponds to a ZIP code.
ZIPCITYDISTANCE Function Returns the geodetic distance between two ZIP code locations.
ZIPFIPS Function Converts ZIP codes to two-digit FIPS codes.
ZIPNAME Function Converts ZIP codes to uppercase state names.
ZIPNAMEL Function Converts ZIP codes to mixed case state names.
ZIPSTATE Function Converts ZIP codes to two-character state postal codes.

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
  • 2 replies
  • 1039 views
  • 1 like
  • 3 in conversation