Case when condition

Sandeep77 — Tue, 14 Jun 2022 17:17:05 GMT

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;

Re: Case when condition

PaigeMiller — Tue, 14 Jun 2022 17:31:27 GMT

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)

Re: Case when condition

Sandeep77 — Tue, 14 Jun 2022 17:46:53 GMT

Thank you!!

topic Case when condition in New SAS User

Case when condition

Re: Case when condition

Re: Case when condition