Selecting for Certain Observations Based on Two Tables

Reply
Regular Contributor
Posts: 194

Selecting for Certain Observations Based on Two Tables

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.....

Trusted Advisor
Posts: 2,116

Re: Selecting for Certain Observations Based on Two Tables

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

SAS(R) 9.2 SQL Procedure User's Guide

for more examples.

Regular Contributor
Posts: 194

Re: Selecting for Certain Observations Based on Two Tables

Thank you!

Ask a Question
Discussion stats
  • 2 replies
  • 173 views
  • 0 likes
  • 2 in conversation