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;
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 print data = veap;
WHERE contact1 not in ('Bob','Elizabeth') and contact2 not in ('Bob','Elizabeth') and contact3 not in ('Bob','Elizabeth');
RUN;
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.
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 |
2 | John | Sarah | Jean |
4 | Jean | Peter | John |
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.
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.