02-07-2014 10:52 AM
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?
Alameda Happy Days
Contra Costa Sunshine Daycare
Contra Costa BusyBees
Inyo Elmwood Montessori
San Joaquin Buzy Beez
County Email Address
Contra Costa email@example.com
San Joaquin firstname.lastname@example.org
02-07-2014 12:51 PM
infile cards dlm='|';
input County :$50. Facility :$50. ;
Contra Costa|Sunshine Daycare
San Joaquin|Buzy Beez
infile cards dlm='|';
input County :$50. Email :$50.;
create table want as
select f.county,f.facility,c.email from facility f inner join contacts c
02-13-2014 10:42 AM
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!
02-15-2014 03:46 PM
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.
02-15-2014 06:28 PM
"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.
contacts as c
( select distinct county
from facility ) as f
where c.county = f.county