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

[Fisher]

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.

[chang_y_chung_hotmail_com]

   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

   */

[Fisher]

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.

[sbb]

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.

[Fisher]

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.

[sbb]

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

[Fisher]

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.

[sbb]

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

[chang_y_chung_hotmail_com]

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

   */

[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;

[ChrisNZ · Accepted solution]

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;

[Fisher]

Excellent!! This is exactly what I wanted.

Big thanks. Chris! this poster is resolved.

[Peter_C]

> 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-operat...

peterC

[ChrisNZ]

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;

[Peter_C]

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