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 |
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.