Joining 2 tables

[jcis7]

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

[pradeepalankar]

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;

[Fugue]

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;

[jcis7]

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!

[overmar]

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.

[Patrick]

Hi

"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;