Selecting for Certain Observations Based on Two Tables

jcis7 — Thu, 31 Jan 2013 18:49:21 GMT

How do you create one table from two to retain observations in Table 1 (Dogs) that don't match IDs in Table 2 (Cats)?

Table 1 Name: Dogs

ID Name Address Type

1 Sinclair 50 Haven Dr. Dog

2 Cathy 23 Appian Way Dog

5 Demitri 30 Ione Ln Dog

Table 2 Name: Cats

ID Name Address Type

1 Sinclair 50 Haven Dr. Cat

2 Cathy 23 Appian Way Cat

3 Dione 123 High Tower Cat

Results Table:

ID Name Address Type

5 Demitri 30 Ione Ln Dog

I don't know how to select this using sql? Or is a better way to do it? Any help is much appreciated! Thanks!

proc sql;

create table as;

select dog.ID, dog.Name, dog.Address, dog.Type

from dogs

where.....

Re: Selecting for Certain Observations Based on Two Tables

Doc_Duke — Thu, 31 Jan 2013 18:57:46 GMT

Try this:

CREATE TABLE want AS

SELECT *

FROM dog

WHERE dog.id NOT IN (select id from cat);

In the SQL manual, the "select" in the WHERE clause is called a "subquery." See

for more examples.

jcis7 — Thu, 31 Jan 2013 19:00:08 GMT

Thank you!