Skip/Ignore a condition in CASE statement in SQL

Question

I have a query in which I am querying based on few CASE conditions. I want a particular CASE statement to do nothing and skip that case all together.

select colA
case
when colB = 'P' then colC in (colD)
when colB = 'Q' then colC in (colE)
when colB = 'R' then do nothing

from table_A

Basically I want that whenever colB encounters value 'R' it does nothing and skips to the next row in table_A. Any way I can do this? If it matters, this is in DB2 Database and will be used in a PROC SQL procedure in SAS Enterprise Guide (Version 8.3)

Answer 1

If you want the variable you are creating with CASE expression to be NULL then use the appropriate null or missing value in the CASE expression.

Perhaps you mean you want to create the new colB variable to have the existing colB variable's value when it is not P or Q?

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A

If you don't want the observations with COLB='R' in the results then exclude those using WHERE.

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A
where colB ne 'R'

If you are actually using SAS then skip the SQL completely and just write SAS code to do whatever you want. Then you could actually have statements that are executed conditionally.

data want;
  set mylib.table_A;
  if colB='P' then do;
     * some other data step statements ;
  end;
run;