Search code examples
sqlsas

Where, Case, and In SAS SQL

Trying to use an 'in' within a 'case' within a 'where' clause in SAS SQL

I am trying to write an SQL query in SAS that utilizes a "where" clause. Within this "Where" clause, I need to use a "case" statement, and within this, an "in" statement. Is this possible?

Something like this:

proc sql;
connect to $$$$$;
create table test1 as 
select * from $$$$$ (
    select 
      line1, 
      line2 
    from 
      $$$$$ 
    where 
      line1 not in case when line2 = 1 then ('0', '1', '2') else ('3') end
  );
quit;
Solution

  • You may not need to use a case statement within the where clause, because you can write a logically equivalent condition without a case statement:

    where (line2 = 1 and line1 not in ('0', '1', '2'))
      or (line2 <> 1 and line1 not in ('3'))