Case when condition

[Sandeep77]

Hi Experts,

I have created a table with phone numbers. Now I am trying to divide the phone numbers into mobile and landline numbers. Suppose if the mobile number starts with 07 then I am using the below code but getting some error in the code. Can you please suggest?

proc sql;
create table Mobile_numbers as
select a.*,
case when dr_phone like (07%)  then 'Mobile' else 'Landline'
end as Mobile_or_Landline
from Telephone_numbers;
quit;

Error:
29         proc sql;
30         create table Mobile_numbers as
31         select a.*,
32         case when dr_phone like (07%)  then 'Mobile' else 'Landline'
                                      _
                                      22
                                      200
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, ^, ^=, |, ||, ~, ~=.  

ERROR 200-322: The symbol is not recognized and will be ignored.

33         end as Mobile_or_Landline
34         from Telephone_numbers;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35         quit;

[ballardw]

Is your phone number actually a number or character? If character the syntax would be '07%' to indicate the comparison value.

If numeric "like" is not going to appropriate at all.

[Sandeep77]

It's a character value. So I made the changes to '07%' but it still shows the below error. 

proc sql;
create table Mobile_numbers as
select a.*,
case when dr_phone like '07%' then 'Mobile' else 'Landline'
end as Mobile_or_Landline
from Telephone_numbers;
quit;

error:
29         proc sql;
30         create table Mobile_numbers as
31         select a.*,
32         case when dr_phone like '07%' then 'Mobile' else 'Landline'
33         end as Mobile_or_Landline
34         from Telephone_numbers;
ERROR: Could not expand a.*, correlation name not found.
ERROR: The following columns were not found in the contributing tables: a.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35         quit;

[Tom · Accepted solution]

If you want to refer to the dataset Telephone_numbers with the alias A then you need to tell SQL that in the FROM clause.

create table Mobile_numbers as
  select a.*
       , case when dr_phone like '07%' then 'Mobile' else 'Landline'
         end as Mobile_or_Landline
  from Telephone_numbers a
;

Or just remove the a. from before the * in the list of variables.

[Sandeep77]

Thank you. Got it!

[yaakov555]

case when dr_phone like '(07%)' then 'Mobile' else 'Landline'