DATA Step, Macro, Functions and more

Merging Tables

Reply
Occasional Contributor
Posts: 17

Merging Tables

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
Super User
Posts: 5,085

Re: Merging Tables

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;

Super User
Posts: 17,863

Re: Merging Tables

[ Edited ]

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

 

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.
Ask a Question
Discussion stats
  • 2 replies
  • 125 views
  • 1 like
  • 3 in conversation