Trying to Using if and Like together

[ubshams]

Hello,
I am trying to use the “if” and “like” statement to create a new column called “new_column” based on the character values of “column1”. But I get an error (see below). I am trying to use the “Like” statement but does not seem to work with “if” statement.
Please advice  on an alternative approach. Thanks!

Column1 is a character column and I am trying to create another character column called “column1” based on column1 values values.

---current code---
data want;
set have;
if upcase(column1) like "SPEC%"
then new_column = "Specialty";
run;

I get the following error:

Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
2 The SAS System 11:46 Friday, April 3, 2020

IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, THEN, ^, ^=, |, ||, ~, ~=.

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

[PaigeMiller]

LIKE works in PROC SQL. It does not work in a DATA step.

To fix this, try

data want;
set have;
if upcase(column1) =: "SPEC" then new_column = "Specialty";
run;

The =: operator (note the colon after the equal sign) finds cases where upcase(column1) starts with SPEC

In the future, please do not disconnect the log from the code. Copy the log (including the code and the warnings and error messages) with nothing chopped out and paste it into the window that appears when you click on the </> icon. This preserves the formatting of the log and makes it more readable and more meaningful. DO NOT SKIP THIS STEP.

[ballardw]

LIKE can only be used in a Where expression in a data step.

Example:

data want;
   set sashelp.class;
   where name like 'J%';
run;

You might try

data want;
   set have;
   if upcase(column1) =: "SPEC"
       then new_column = "Specialty";
run;

The =: is "begins with".

There are also number of other functions to search text like FIND, FINDW, INDEX, INDEXW and the PRX functions.

[ubshams]

Thanks. But the values in  column1 are like this:

Text1_Text2_Text3_Spec_Text5_Text6

Text1_Text2_Text3_Spec2_Text5_Text6

...

Text1_Text2_Text3_Spec6_Text5_Text6

So the ":=" is usfeful, but not in this case. Is there a CONTAINS OR LIKE equivalent I can use with an "IF" statement to create a new column? 

[Tom]

Yes.
https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm...
Try INDEX, INDEXW, FIND, or FINDW .

[ballardw · Accepted solution]

---

@ubshams wrote:

Thanks. But the values in  column1 are like this:

Text1_Text2_Text3_Spec_Text5_Text6

Text1_Text2_Text3_Spec2_Text5_Text6

...

Text1_Text2_Text3_Spec6_Text5_Text6

So the ":=" is usfeful, but not in this case. Is there a CONTAINS OR LIKE equivalent I can use with an "IF" statement to create a new column? 

---

You may need to actually provide real, or more realistic example values. Your initial LIKE example would "work" only if the string starts with "SPEC".

data have;
    input column1 $;
datalines;
Text1
Spec_
Cspec
x_SPEC_
;
run;
data want;
   set have;
   where upcase(column1) like "SPEC%";
   new_column = "Specialty";
run;

Only the value that starts with Spec is kept.

Which is why I translated your example like to =: .

[DavePrinsloo]

As mentioned by others use a function like FIND or INDEX and for more complex logic you could use PRXMATCH

data want;
set have;
if find(upcase(column1),"SPEC")
then new_column = "Specialty";
run;