Merging Tables

[ernie86]

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

[Astounding]

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]

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.