Excluding all records of the same ID if any record with that ID meets two conditions in PROC SQL

[alicezwang96]

I have a table that looks like this:

ID    Pet
01    Dog
01    Cat
01    Parrot
01    Guinea Pig
02    Dog
02    Guinea Pig
03    Parrot

If an ID has a dog and a cat, then I want to exclude ALL records for that ID, regardless of what other animals they have. So my output table would look like:

ID    Pet
02    Dog
02    Guinea Pig
03    Parrot

This is the query that I've tried:

PROC SQL;
CREAT TABLE new_table AS
SELECT * from Pets a
WHERE NOT EXISTS (SELECT *
                  FROM Pets b
                  WHERE b.ID = a.ID
                  AND b.ID = "Dog"
                  AND b.ID = "Guinea Cat"));
RUN;

This doesn't seem to be working and isn't actually filtering anything out.

[PaigeMiller]

b.ID = "Dog" AND b.ID = "Guinea Cat"

is always false, the value of ID can't be both "Dog" and "Guinea Cat". Furthermore, variable ID contains numbers, while variable PET contains the types of animals, but even if you replaced ID with PET in the above command, it still won't work. So that's two reasons why this won't work.

I would do the following (UNTESTED CODE)

proc transpose data=have out=have_t prefix=pet;
    by id;
    var pet;
run;
data want;
    set have_t;
    if whichc('Dog',of pet:)>0 and whichc('Cat',of pet:)>0 then delete;
run;

This doesn't quite produce the exact output you want, it produces a transposed output. You could always transpose WANT to get the exact layout you want.

[novinosrin]

Hi @alicezwang96  -

data have;
input ID $    Pet & $15.;
cards;
01    Dog
01    Cat
01    Parrot
01    Guinea Pig
02    Dog
02    Guinea Pig
03    Parrot
;


proc sql;
 create table want as
 select *
 from have
 group by id
 having count(distinct case when pet in ('Dog','Cat') then pet else ' ' end)<2;
quit;