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.....
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.
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.