SAS PROC SQL NOT CONTAINS multiple values in one statement

puk789 picture puk789 · Nov 11, 2016 · Viewed 9.8k times · Source

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.

Answer

Joe picture Joe · Nov 11, 2016

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.