How to use proc sql to select variable that contains ANY of given strings?

[SAS_user_n]

Hello,

can I ask a related question ---> I am also trying to search strings with proc sql (extracting data from DB2 using PROC SQL) by using WHERE condition (and need to avoid using data step) to reduce the number of observations extracted.

My question is --->
Is there a way to modify "in:" from the below to able to search any substring?

proc sql;
select NAME from SASHELP.CLASS(where=(NAME in:('A','B')));
quit;

For example,
I want to find all names that have "ane" in the string, But "in:" only captures strings that start with "ane".

Could you kindly advise? Thank you very much.

[art297]

You could use something like:

data strings;
input string $upcase.;
cards;
ro
an
;

proc sql;
select "upcase(NAME) contains '"||trim(string)||"'"
into :strings
separated by " or "
from strings
;
select NAME
from SASHELP.CLASS (where=(&strings.))
;
quit;

HTH,
Art
-----
> Hello,
>
> can I ask a related question ---> I am also trying to
> search strings with proc sql (extracting data from
> DB2 using PROC SQL) by using WHERE condition (and
> need to avoid using data step) to reduce the number
> of observations extracted.
>
> My question is --->
> Is there a way to modify "in:" from the below to able
> to search any substring?
>
> proc sql;
> select NAME from SASHELP.CLASS(where=(NAME
> in:('A','B')));
> quit;
>
> For example,
> I want to find all names that have "ane" in the
> string, But "in:" only captures strings that start
> with "ane".
>
> Could you kindly advise? Thank you very much.

[Sandhya]

Equivalent of in: in PROC SQL is supposed to be inT. But it work selective. Not sure of the pattern.

I would be happy to know why this happens.

Thanks,
Sandy.

[SAS_user_n]

Thank you all for suggestions. I was able to resolve my problem this way:
(I want to only select if a char var has "ab cdcdcd ef" appearing in any part of the string).

proc sql;
connect to db2 ;
create table temp as
select * from connnection to db2
(select x y z
from Q
where x like '%ab cdcdcd ef%'
order by x);
quit;