topic Re: Merging Tables in SAS Programming

Merging Tables

ernie86 — Thu, 30 Mar 2017 22:35:36 GMT

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

dataset a

dataset b

dataset c

Astounding — Thu, 30 Mar 2017 22:46:00 GMT

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 — Thu, 30 Mar 2017 22:48:49 GMT

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:

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.