find the value like “%{%}%”; “%[%]%” and “%(%)%”

[Alexxxxxxx]

Dear all

how can I find the value like “%{%}%”; “%[%]%” and “%(%)%” 

for example 

"{UMLAUT OVER (A)}"

"{UMLAUT OVER (E)}" 

"{UMLAUT OVER (O)}"

"{UMLAUT OVER (U)}" 

"{UMLAUT OVER (N)}" 

I use the following codes,

proc sql;
create table whichone as 
select distinct 
*
from step9.Patstat_gb_hrm_Step2
where HRM_L2_Step2 like ('%{%}%','%[%]%','%(%)%')
;
quit;

however,  I get a result like below,

928  proc sql;
929  create table whichone as
930  select distinct
931  *
932  from step9.Patstat_gb_hrm_Step2
933  where HRM_L2_Step2 like ('%{%}%','%[%]%','%(%)%')
                                     -
                                     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, ^, ^=, |, ||, ~, ~=.

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

934  ;
935  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

could you please give me some suggestions about this?

thanks in advance.

[Reeza · Accepted solution]

I think you need to list multiple LIKE statements with an OR. 

LIKE does not have an IN type functionality to list multiple things at once. 

HRM_L2_Step2 like '%{%}%'
or HRM_L2_Step2 like '%[%]%' 
or HRM_L2_Step2 like '%(%)%'

Or you can try regular expressions. 

---

@Alexxxxxxx wrote:

Dear all

how can I find the value like “%{%}%”; “%[%]%” and “%(%)%” 

for example 

"{UMLAUT OVER (A)}"

"{UMLAUT OVER (E)}" 

"{UMLAUT OVER (O)}"

"{UMLAUT OVER (U)}" 

"{UMLAUT OVER (N)}" 

I use the following codes,

proc sql;
create table whichone as 
select distinct 
*
from step9.Patstat_gb_hrm_Step2
where HRM_L2_Step2 like ('%{%}%','%[%]%','%(%)%')
;
quit;

however,  I get a result like below,

928  proc sql;
929  create table whichone as
930  select distinct
931  *
932  from step9.Patstat_gb_hrm_Step2
933  where HRM_L2_Step2 like ('%{%}%','%[%]%','%(%)%')
                                     -
                                     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, ^, ^=, |, ||, ~, ~=.

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

934  ;
935  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

could you please give me some suggestions about this?

thanks in advance.

---

[PeterClemmensen]

proc sql;
create table want as 
select distinct 
*
from have
where prxmatch('/{.*}|\[.*\]|(.*)/', HRM_L2_Step2);
quit;