Case when condition

[Sandeep77]

Hi Experts,

I have created a dataset where there are telephone numbers in 3 columns. Now I want to use the case when functions. If the telephone number is available in any three column then end as 'Y' else 'N'. I am not very sure if I can do it together. Can you please look at my code as I am getting error. I feel that it's more than a syntax error.

proc sql;
create table Telephone_numbers as 
select distinct
a.*,
b.dr_phone,
b.dr_phone2,
b.dr_phone3,
case when
b.dr_phone, b.dr_phone2, b.dr_phone3 is not null then 'Y' else 'N'
end as Number_available
from repcodes as a
inner join
p2scflow.debtor as b on a.accounts_number=b.debt_code; 
quit;

Error:

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         proc sql;
30         create table Telephone_numbers as
31         select distinct
32         a.*,
33         b.dr_phone,
34         b.dr_phone2,
35         b.dr_phone3,
36         case when
37         b.dr_phone, b.dr_phone2, b.dr_phone3 is not null then 'Y' else 'N'
                     _
                     22
                     76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, THEN, ^, ^=, |, ||, ~, 
              ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

38         end as Number_available
39         from repcodes as a
40         inner join
41         p2scflow.debtor as b on a.accounts_number=b.debt_code;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
42         quit;

[PaigeMiller]

case when b.dr_phone is not null or b.dr_phone2 is not null or b.dr_phone3 is not null then 'Y' else 'N' end as variable_name

or simpler

case when coalesce(b.dr_phone,b.dr_phone2,b.dr_phone3) is not null then 'Y' else 'N' end as variable_name

Commas, like in your code, are only used to separate variables in the SELECT clause. They don't belong in CASE statements (except at the end after you have end as variable_name)

[Sandeep77]

Thank you!!