DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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

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;

 

 

 

Super User
Posts: 23,771

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

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;

 

 

 


 

PROC Star
Posts: 1,831

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

proc print data = veap;

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


RUN;
New Contributor
Posts: 2

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

Posted in reply to novinosrin

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.

PROC Star
Posts: 1,831

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

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
2JohnSarahJean
4JeanPeterJohn
Ask a Question
Discussion stats
  • 4 replies
  • 79 views
  • 0 likes
  • 3 in conversation