topic Re: How do I filter out/subset multiple individual cases within the same variable name? in SAS Programming

How do I filter out/subset multiple individual cases within the same variable name?

shea0115 — Fri, 29 Jun 2018 17:49:11 GMT

New SAS user, still figuring it out. I have an Excel sheet that I need to simplify in SAS. This is what I'm trying to do:

ID Contact 1 Contact 2 Contact 3

1 Bob Sarah Elizabeth

2 John Sarah Jean

3 Peter Sarah Elizabeth

4 Jean Peter John

5 Peter Jean Elizabeth

Now, I need to remove all rows that have "Bob" in any of the 3 contact columns AND remove all rows that have Elizabeth in any of the 3 contact columns. The final table would look like this:

ID Contact 1 Contact 2 Contact 3

2 John Sarah Jean

4 Jean Peter John

This is what I have so far, but it does not work to use multiple WHERE statements for some reason.

proc print data = veap

     WHERE contact 1 ~= 'Bob' or  contact 2 ~= bob or contact 3 ~= Bob;

     WHERE contact 1 ~= Elizabeth or contact 2 ~= Elizabeth or contact 3 ~= Elizabeth;

RUN;

Re: How do I filter out/subset multiple individual cases within the same variable name?

Reeza — Fri, 29 Jun 2018 17:58:52 GMT

if whichc("Bob", of contact1-contact3) then delete;

if whichc("Elizabeth", of contact1-contact3) then delete;

Try the WHICHC() function instead which searches a list of variables.

@shea0115 wrote:

New SAS user, still figuring it out. I have an Excel sheet that I need to simplify in SAS. This is what I'm trying to do:

ID       Contact 1      Contact 2      Contact 3

1         Bob               Sarah            Elizabeth

2         John              Sarah          Jean

3         Peter             Sarah            Elizabeth

4         Jean              Peter            John

5          Peter             Jean            Elizabeth

Now, I need to remove all rows that have "Bob" in any of the 3 contact columns AND remove all rows that have Elizabeth in any of the 3 contact columns. The final table would look like this:

ID       Contact 1      Contact 2      Contact 3

2         John             Sarah              Jean

4         Jean              Peter            John

This is what I have so far, but it does not work to use multiple WHERE statements for some reason.
proc print data = veap

     WHERE contact 1 ~= 'Bob' or  contact 2 ~= bob or contact 3 ~= Bob;

     WHERE contact 1 ~= Elizabeth or contact 2 ~= Elizabeth or contact 3 ~= Elizabeth;

RUN;

Re: How do I filter out/subset multiple individual cases within the same variable name?

novinosrin — Fri, 29 Jun 2018 18:05:12 GMT

proc print data = veap;

     WHERE contact1 not in  ('Bob','Elizabeth') and    contact2 not in  ('Bob','Elizabeth') and  contact3 not in  ('Bob','Elizabeth');


RUN;

Re: How do I filter out/subset multiple individual cases within the same variable name?

shea0115 — Fri, 29 Jun 2018 18:28:33 GMT

Yes! This worked for removing all contact 1 rows - but once I added the "and" and listed contact 2 ("Bob", "Elizabeth") the table looks the same. It did not actually perform the action of removing anything further.

Re: How do I filter out/subset multiple individual cases within the same variable name?

novinosrin — Fri, 29 Jun 2018 18:41:30 GMT

This is my test results:

Is this what you expect?

data have;
input (ID       Contact1      Contact2      Contact3) (:$20.);
cards;
1         Bob               Sarah            Elizabeth
2         John              Sarah          Jean
3         Peter             Sarah            Elizabeth
4         Jean              Peter            John
5          Peter             Jean            Elizabeth
;

proc print data = have;

WHERE contact1 not in  ('Bob','Elizabeth') and    contact2 not in  ('Bob','Elizabeth') and  contact3 not in  ('Bob','Elizabeth');

RUN;

Results:

SAS Output

The SAS System

Obs ID Contact1 Contact2 Contact324

2	John	Sarah	Jean
4	Jean	Peter	John