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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.