puk789 puk789 - 22 days ago 17
SQL Question

SAS PROC SQL NOT CONTAINS multiple values in one statement

In PROC SQL, I need to select all rows where a column called "NAME" does not contain multiple values "abc", "cde" and "fbv" regardless of what comes before or after these values. So I did it like this:

SELECT * FROM A WHERE
NAME NOT CONTAINS "abc"
AND
NAME NOT CONTAINS "cde"
AND
NAME NOT CONTAINS "fbv";


which works just fine, but I imagine it would be a headache if we had a hundred of conditions. So my question is - can we accomplish this in a single statement in PROC SQL?
I tried using this:

SELECT * FROM A WHERE
NOT CONTAINS(NAME, '"abc" AND "cde" AND "fbv"');


but this doesn't work in PROC SQL, I am getting the following error:


ERROR: Function CONTAINS could not be located.


I don't want to use LIKE.

Joe Joe
Answer

You could use regular expressions, I suppose.

data a;
input name $;
datalines;
xyabcde
xyzxyz
xycdeyz
xyzxyzxyz
fbvxyz
;;;;
run;

proc sql;

SELECT * FROM A WHERE
  NAME NOT CONTAINS "abc" 
  AND
  NAME NOT CONTAINS "cde"
  AND
  NAME NOT CONTAINS "fbv";


SELECT * FROM A WHERE
  NOT (PRXMATCH('~ABC|CDE|FBV~i',NAME));
quit;

You can't use CONTAINS that way, though.

Comments