BookmarkSubscribeRSS Feed
shea0115
Calcite | Level 5

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;

 

 

 

4 REPLIES 4
Reeza
Super User

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;

 

 

 


 

novinosrin
Tourmaline | Level 20
proc print data = veap;

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


RUN;
shea0115
Calcite | Level 5

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.

novinosrin
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 701 views
  • 0 likes
  • 3 in conversation