BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ken_oy
Fluorite | Level 6
I know this is a very simple question,
We have a " where in (1,2,3,....)" statement
do we have "where not in (1,2,3...)?" or a similar statement?

Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions
Bill
Quartz | Level 8

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)

 

View solution in original post

7 REPLIES 7
Bill
Quartz | Level 8

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)

 

stateworker
Fluorite | Level 6
Bill wrote:
>Dare to experiment - error messages are free!


Thanks for the chuckle - my log is full of Red today.
Doc_Duke
Rhodochrosite | Level 12
More completely, you can put a "not" infix operator in nearly every logical expression, and often outside, e.g.
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)
ChrisNZ
Tourmaline | Level 20
Thanks for this Doc. I always use NOT before IN, and it never occured to me that

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.
bhthomas99
Calcite | Level 5

I have recently seen "NOTIN", which seems to work: however, there is no space between the infix and the operator.  Is this even possible?

abbess
Calcite | Level 5

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

DarthPathos
Lapis Lazuli | Level 10

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:

Screen Shot 2016-07-29 at 7.46.55 PM.png

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:

Screen Shot 2016-07-29 at 7.47.19 PM.png

 

Good luck!

Chris

Has my article or post helped? Please mark as Solution or Like the article!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 140911 views
  • 8 likes
  • 8 in conversation