topic Re: How to use proc sql to select variable that contains ANY of given strings? in SAS Procedures

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

Fisher — Wed, 19 Jan 2011 14:01:20 GMT

I have a list of strings, now I like to use proc sql to select obs from a dataset that one variable begins or contains ANY of the string in the given string list. There are many strings in the given string list, and the lengths of strings are variable, so I can't use functions such as substring().

I tried to do this do this way:

proc sql;
select * from myds where onevar contains ANY ('str1', 'str2', 'str3', 'str4', 'str5', 'str6', 'str7');
but it doesn't work. Any better way to do it? thanks.

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

chang_y_chung_hotmail_com — Wed, 19 Jan 2011 15:15:35 GMT

proc sql;

select *

from sashelp.class

where name in ('Alfred', 'Alice', 'Robert');

quit;

/* on lst

Name Sex Age Height Weight

-------------------------------------------

Alfred M 14 69 112.5

Alice F 13 56.5 84

Robert M 12 64.8 128

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

Fisher — Wed, 19 Jan 2011 15:25:03 GMT

Thanks.

But when the name is 'Alicen', then this obs will NOT be picked up. I mean comparing whole or part of the strings, not only whole string. In your example, you only check if the name value is equal to one of the given strings.

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

sbb — Wed, 19 Jan 2011 16:01:00 GMT

So, in your SELECT and WHERE clauses, you will need to use "LIKE" or "CONTAINS" and separate each data-value test using an OR condition. It is possible to generate such argument strings with SAS macro code, if so inclined.

Scott Barry
SBBWorks, Inc.

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

Fisher — Wed, 19 Jan 2011 16:06:56 GMT

Thanks Scott.
My string list is very long, if I use LIKE or CONTAINS, then I have to type these keywords and every string in the list which is boring.

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

sbb — Wed, 19 Jan 2011 16:12:25 GMT

As suggested, you can write a SAS macro to generate the necessary code from your list of values.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post-reply:
macro generate code from list site:sas.com

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

Fisher — Wed, 19 Jan 2011 16:13:44 GMT

I found I can use data step to do it:

data temp;
set sashelp.class;
if name in: ( 'Al', 'Robert');
run;

which picks up 'Alfred', 'Alice' and 'Robert'.
I am just curious if there is an equivalent way in proc sql.

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

sbb — Wed, 19 Jan 2011 16:25:23 GMT

No equivalent with PROC SQL (reason for suggesting macro code approach) - consider using a WHERE statement instead of IF (performance considerations, however data-volume dependent, with the DATA step approach).

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post-reply:

proc sql select where clause operators site:sas.com

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

chang_y_chung_hotmail_com — Wed, 19 Jan 2011 17:29:49 GMT

proc sql does not have in: but it does have eqt. This should work fine assuming that the lookup table(names below) does not contain entries that eqt themselves, like: "Al" and "A". (guess why?)

proc sql;

create table names (name character(20));

insert into names set name="Al";

insert into names set name="Robert";

select c.*

from sashelp.class as c, names as n

where c.name eqt n.name;

quit;

/* on lst

Name Sex Age Height Weight

-------------------------------------------

Alfred M 14 69 112.5

Alice F 13 56.5 84

Robert M 12 64.8 128

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

advoss — Wed, 19 Jan 2011 21:29:00 GMT

The original request referred to the string being "CONTAIN"ed in the variable. The existing solutions have found it when the variable started with the string and didn't ignore case. If those two criteria are required, the following code accomplishes that.

data allstrings;
input string $3.;
cards;
red
al
ber
;
proc sql noprint;
select 'lowcase(name) contains '''||trim(lowcase(string))||''''
into :strings separated by ' or '
from allstrings
order by 1;
create table subset as
select *
from sashelp.class
where &strings;
quit;

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

ChrisNZ — Tue, 06 Mar 2018 16:14:18 GMT

To answer post @ Jan 19, 2011 11:13 AM

>I am just curious if there is an equivalent way in proc sql.

There is: operators =: or in: can be used to subset a sas table in a sql statement if they are used as a dataset option instead of a sql clause.

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

SAS macro approach suggested by @advoss:

The original request referred to the string being "CONTAIN"ed in the variable. The existing solutions have found it when the variable started with the string and didn't ignore case. If those two criteria are required, the following code accomplishes that.
data allstrings;
  input string $3.;
  cards;
red
al
ber
;

proc sql noprint;
  select 'lowcase(name) contains '''||trim(lowcase(string))||'''' 
    into :strings separated by ' or '
      from allstrings
        order by 1;
  create table subset as
    select *
      from sashelp.class
        where &strings;
quit;

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

Peter_C — Thu, 20 Jan 2011 16:22:50 GMT

> I have a list of strings, now I like to use proc sql
> to select obs from a dataset that one variable begins

almost

see the tip from Amadeus on using ANY and ALL in proc sql, at
http://www.amadeus.co.uk/sas-technical-services/tips-and-techniques/sql/using-the-any-and-all-operators-in-proc-sql/

peterC

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

Fisher — Thu, 20 Jan 2011 16:34:13 GMT

Excellent!! This is exactly what I wanted.

Big thanks. Chris! this poster is resolved.

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

ChrisNZ — Mon, 24 Jan 2011 02:21:41 GMT

Hi Peter,

I didn't know this syntax, thanks fot the link.

On the other hand I fail to see what its point is. The examples given are just as easy to do in a more standard fashion. When would you use the all and any operators?

Thanks.

[pre]
data mozart_shoes;
format id z3. name $char24.;
input id name $ & sales;
datalines;
001 Super-tread trainer 50000
002 X-Pro sports trainer 60000
003 Road runner deluxe 55000
004 Triathlon special 30000
run;
data top_brands;
format name $char24.;
input name $ & sales;
datalines;
Cougar 500 58000
Mike running pro 56000
Abibas Super-tread 53000
run;
proc sql;
select name, sales
from mozart_shoes
where sales gt any (select sales from top_brands);
select name, sales
from mozart_shoes
where sales gt (select min(sales) from top_brands);

select name, sales
from mozart_shoes
where sales gt all (select sales from top_brands);
select name, sales
from mozart_shoes
where sales gt (select max(sales) from top_brands);
quit;

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

Peter_C — Mon, 24 Jan 2011 13:11:52 GMT

Chris
I agree with you, those examples don't demand ANY or ALL.
However, I googled sql all any and found sql discussion group postings which made more sense of the feature.
Nice to know it's there.
Peter

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

SAS_user_n — Wed, 20 Apr 2011 15:03:09 GMT

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.

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

art297 — Wed, 20 Apr 2011 16:09:08 GMT

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.

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

Sandhya — Wed, 20 Apr 2011 18:05:34 GMT

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.

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

SAS_user_n — Mon, 25 Apr 2011 12:48:39 GMT

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;