Like operator requires character operands

[Sandeep77]

Hi all,

I am trying to find the data with 120A  but when I am using the where function I am getting the below error message. The AccountStatusKey_Current is Numeric type. Do I need to change the type to character? if so then how can I do it?

Proc sql;
create table Accountstatus as 
select AccountKey,
AccountStatusKey_Current,
AccountStatusKey_Previous,
Datekey
from DWHDW.fact_tx_AccountStatus
where AccountStatusKey_Current like '120A';
quit;

Error log:



29         Proc sql;
30         create table Accountstatus as
31         select AccountKey,
32         AccountStatusKey_Current,
33         AccountStatusKey_Previous,
34         Datekey
35         from DWHDW.fact_tx_AccountStatus
36         where AccountStatusKey_Current like '120A';
ERROR: LIKE operator requires character operands.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              5457.43k
      OS Memory           42680.00k
      Timestamp           02/27/2023 03:53:00 PM
      Step Count                        53  Switch Count  16

[PaigeMiller]

AccountStatusKey_Current must be numeric, otherwise you would not get that message.

You can convert it to character with the PUT function, but don't bother, a number (if converted to character) cannot be like '120A'

which should always fail. You have, in addition to the syntax error, a logic error which you must fix first

[Sandeep77]

Thank you but I did not get it. So should I use where AccountStatusKey_Current = '120A' instead of like? Also should I use the put statement in between my code?

[Reeza]

If AccountStatusKey_Current is numeric, it can never have the letter A in it.

So you need to verify your request as it will never be true. Is it the correct variable you're referencing?

[Sandeep77]

Okay, I got it. Yes, my mistake. Actually it is just AccountStatusKey_Current  is120 and not 120A.

[Sandeep77]

Even with 120 I am getting the same error message.

[PaigeMiller]

---

@Sandeep77 wrote:
Even with 120 I am getting the same error message.

---

You have to convert the numeric variable AccountStatusKey_Current to character with the PUT function.

[PaigeMiller]

---

@Sandeep77 wrote:

Thank you but I did not get it. So should I use where AccountStatusKey_Current = '120A' instead of like? Also should I use the put statement in between my code?

---

A number cannot be equal to '120A' either.

[Tom · Accepted solution]

So this error

36         where AccountStatusKey_Current like '120A';
ERROR: LIKE operator requires character operands.

means that AccountStatusKey_Current is a numeric variable.  Hence the error message about needing a character operand.

Notice that if AccountStatusKey_Current is a number can never have the letter A as part of its value.  So it can never be equal to the string '120A'.  

Did you instead want to test if the the value as 120?  Do you have a format attached to that variable that makes it display strings that include the letter A?  If so you need to test for the actual value not the formatted value.  Or use the PUT() function to convert the number to the formatted value and then you can check if that formatted value looks like what you are looking for.

Plus if you are testing for a single value like 120 or '120A' you do not need to use LIKE. 

Instead just use = 

where AccountStatusKey_Current ='120A';
where AccountStatusKey_Current = 120;

Or if you have list of distinct values use IN 

where AccountStatusKey_Current in ('120A' '130B');
where AccountStatusKey_Current in (120 130) ;

[Sandeep77]

Thank you Tom. I understood it now. I was making the mistake of using '120'.