topic Re: Joining 2 tables in SAS Programming

Joining 2 tables

jcis7 — Fri, 07 Feb 2014 15:52:36 GMT

Appreciate any help you can give.

I have one table with facility names and some county names named Facility

I have another table with all county names and email addresses named Contacts

Facility table has only some county names listed in the Contacts Table so I need ultimately the email addresses from the Contacts table for only the county names listed in the Facility Table.

The Facility Table has the county names listed more than once since more than one Facility is listed per county.

The Contacts Table has the county name listed more than once because there's more than one contact for some counties.

How do I get the email addresses for only the counties listed in the Facility Table?

Facility Table

County Facility

Alameda Happy Days

Contra Costa Sunshine Daycare

Contra Costa BusyBees

Inyo Elmwood Montessori

San Joaquin Buzy Beez

etc...

Contacts Table

County Email Address

Alameda hamwood@alameda.gov

Alameda stern@alameda.gov

Contra Costa lily@cc.gov

Inyo sch@iy.com

Inyo tom@iy.com

San Joaquin kj1@sq.gov

Re: Joining 2 tables

pradeepalankar — Fri, 07 Feb 2014 17:51:58 GMT

data Facility;

infile cards dlm='|';

input County :$50. Facility :$50. ;

cards;

Alameda|Happy Days

Contra Costa|Sunshine Daycare

Contra Costa|BusyBees

Inyo|Elmwood Montessori

San Joaquin|Buzy Beez

;

data contacts;

infile cards dlm='|';

input County :$50. Email :$50.;

cards;

Alameda|hamwood@alameda.gov

Alameda|stern@alameda.gov

Contra Costa|lily@cc.gov

Inyo|sch@iy.com

Inyo|tom@iy.com

San Joaquin|kj1@sq.gov

;

run;

proc sql;

create table want as

select f.county,f.facility,c.email from facility f inner join contacts c

on f.county=c.county;

quit;

Re: Joining 2 tables

Fugue — Fri, 07 Feb 2014 20:27:21 GMT

Get rid of duplicate county records:

proc sql;

select f.county

, c.email

from contacts c

inner join

( select distinct county

from facility ) as f

on c.county = f.county

;

quit;

Re: Joining 2 tables

jcis7 — Thu, 13 Feb 2014 15:42:25 GMT

This worked great after adding an 'as' after 'contacts'

Question: what does the phrase 'inner join' tell sas to do? join the contacts table with the newly made table f?

why is it sometimes you can ask sas to do an inner join in sql without specifying 'inner join' but for this you have to? Is it because there

is a subquery?

Appreciate your help!

Re: Joining 2 tables

overmar — Sat, 15 Feb 2014 20:46:32 GMT

The term inner join implies that you only want to keep all of the records which match in both tables. When I read your query it looked like you wanted a table with all of the facilities and all of the email addresses that you could match, rather than only those which were in both tables. The way that the example code was shown implied that you had an email address for every county, but my guess is that you do not. If this was what you wanted then you should specify left rather than inner join with the facility table being specified first in your statement.

Re: Joining 2 tables

Patrick — Sat, 15 Feb 2014 23:28:24 GMT

"why is it sometimes you can ask sas to do an inner join in sql without specifying 'inner join' but for this you have to?"

This is just SQL syntax allowing you to code an inner join implicit or explicit. Below how it would look like implicit.

proc sql;

select f.county

, c.email

from

contacts as c

( select distinct county

from facility ) as f

where c.county = f.county

;

quit;