CODEWITHSUNDEEP
sqlsas
Angelo Lapa Huamani
Angelo Lapa Huamani

Reputation: 53

How to exclude rows with multiple conditions and "not in" in SAS?

I have a doubt in a query, I thought the solution would be simple but I realized that it is not. I have table A and the cod field is the main key.

COD CATEGORY PRODUCT IND SOURCE
1 Two black Y ANEXO8
2 Two black Y ANEXO8
3 Two black N ANEXO8
4 Two red Y ANEXO8
5 Two red Y ANEXO8
6 Two red N ANEXO8
7 Two yellow Y ANEXO8
8 Two yellow N ANEXO8
9 Two green N ANEXO8
10 Two green N ANEXO8
11 Two pink Y ANEXO8
12 Two pink Y ANEXO8
13 Two pink N ANEXO8
14 Two gray N SAS
15 Two gray N SAS
16 Two gray N SAS

What I am trying to get is to first filter out all rows that have the field "ANEXO8", then to exclude all rows that have the field PRODUCTequal to "black", finally to exclude all rows that have the field product equal to "red" only if the field IND is equal to "Y".

The resulting table would be equal to:

COD CATEGORY PRODUCT IND SOURCE
6 Two red N ANEXO8
7 Two yellow Y ANEXO8
8 Two yellow N ANEXO8
9 Two green N ANEXO8
10 Two green N ANEXO8
11 Two pink Y ANEXO8
12 Two pink Y ANEXO8
13 Two pink N ANEXO8
14 Two gray N SAS
15 Two gray N SAS
16 Two gray N SAS

I have tried to perform a single query:

proc sql;
   create table test as                                                                            
   select * from A 
   where SOURCE = "ANEXO8" 
   and PRODUCT not in ("black")
   and (PRODUCT not in ("red") and IND ne "Y"));
run;

But I don't get the result I want, do you know what I could do, or maybe where am I going wrong?

Upvotes: 0

Views: 1953

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your logic is close but the last and should be or:

create table test as                                                                            
   select * from A 
   where SOURCE = 'ANEXO8' and 
         PRODUCT <> 'black' and
         (PRODUCT <> 'red' or IND <> 'Y');

This is simply a logic error. Do note other differences:

  • NOT IN seems over kill when "not equals" is sufficient.
  • The SQL Standard string delimiter is a single quote not a double quote.
  • The SQL Standard not-equals operator is <>.

Upvotes: 1

Error_2646
Error_2646

Reputation: 3781

Try this

proc sql;
   create table test as                                                                            
   select * from A 
   where SOURCE = "ANEXO8" 
   and PRODUCT not in ("black")
   and not (PRODUCT in ("red") and IND = "Y"));
run;

You have to be careful with how the parenthesis are actually influencing the negation

Upvotes: 3

Related Questions