- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
We have a " where in (1,2,3,....)" statement
do we have "where not in (1,2,3...)?" or a similar statement?
Thanks!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
yes - just use it as you've written it (almost).
where varname not in (1,2,3);
Dare to experiment - error messages are free!
Editor's note: consolidating some other helpful comments in this solution...
@bhthomas99 asks about the NOTIN operator (no space). @abbess replied:
The NOTIN operator work perfectly, I have tried it my self. Also it is mentioned in the SAS Support that NOT is a prefix wich can be used with other operators.
"A prefix operator is an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators"
See SAS Operators in Expressions (SAS documentation)
And @Doc_Duke points out that order-of-operations matters:
Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,
not varname IN (1, 2, 3)
does not give the same answer as
not (varname in (1,2,3)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
yes - just use it as you've written it (almost).
where varname not in (1,2,3);
Dare to experiment - error messages are free!
Editor's note: consolidating some other helpful comments in this solution...
@bhthomas99 asks about the NOTIN operator (no space). @abbess replied:
The NOTIN operator work perfectly, I have tried it my self. Also it is mentioned in the SAS Support that NOT is a prefix wich can be used with other operators.
"A prefix operator is an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators"
See SAS Operators in Expressions (SAS documentation)
And @Doc_Duke points out that order-of-operations matters:
Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,
not varname IN (1, 2, 3)
does not give the same answer as
not (varname in (1,2,3)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
>Dare to experiment - error messages are free!
Thanks for the chuckle - my log is full of Red today.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
where not (varname IN (1,2,3));
is a valid statement.
Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,
not varname IN (1, 2, 3)
does not give the same answer as
not (varname in (1,2,3)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
not varname IN (1, 2, 3)
reads as
(not varname) IN (1, 2, 3) which is true is var = 0
I'll keep this is mind, and add this comment so more people have a chance to read the thread.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have recently seen "NOTIN", which seems to work: however, there is no space between the infix and the operator. Is this even possible?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The NOTIN operator work perfectly, I have tried it my self. Also it is mentionned in the SAS Support that NOT is a prefix wich can be used with other operators.
"A prefix operator is an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators"
Source: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000780367.htm
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Although you have a couple of already accepted solutions, I wanted to throw my two cents in as a PROC SQL guy. I use the NOTIN frequently, especially when querying text:
All my data:
proc sql;
select product, count(*)
from sashelp.shoes
group by product
order by product
;
quit;
Results:
Excluding BOOT and SANDAL:
proc sql;
select product, count(*)
from sashelp.shoes
where product notin ('Boot','Sandal')
group by product
order by product
;
quit;
Results:
Good luck!
Chris