CODEWITHSUNDEEP
sqlsaswhere-clause
M.Belhaj
M.Belhaj

Reputation: 11

Using multiple conditions within where statement

I am trying to identify the number of people (based on ID) who are present in all 3 "touch"es AND opened (open=1) all 3 emails. I am using the below code, but it returns a value of 0 which I don't believe is true. So, I'm not sure that I am representing this correctly in the data. 

proc sql;
    select count (ID) 
    from TableX 
    where (Touch='Touch1' and Open=1) and (Touch='Touch2' and Open=1) and (Touch='Touch3' and Open=1);
    quit;

Thank you!

Upvotes: 1

Views: 6849

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can identify the people using:

proc sql;
    select id
    from TableX 
    where Open = 1 and Touch in ('Touch1', 'Touch2', 'Touch3')
    group by id
    having count(distinct Touch) = 3;
    quit;

To count them, use a subquery:

proc sql;
    select count(*)
    from (select id
          from TableX 
          where Open = 1 and Touch in ('Touch1', 'Touch2', 'Touch3')
          group by id
          having count(distinct Touch) = 3
         ) x
    quit;

Upvotes: 5

Related Questions