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 |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: