Help using Base SAS procedures

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

Reply
Contributor
Posts: 70

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

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.
Regular Contributor
Posts: 241

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

   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


   */

Contributor
Posts: 70

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

Posted in reply to chang_y_chung_hotmail_com
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.
Super Contributor
Super Contributor
Posts: 3,174

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

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.
Contributor
Posts: 70

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

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.
Super Contributor
Super Contributor
Posts: 3,174

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

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
Contributor
Posts: 70

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

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.
Super Contributor
Super Contributor
Posts: 3,174

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

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
Regular Contributor
Posts: 241

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

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


   */

Frequent Contributor
Posts: 91

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

Posted in reply to chang_y_chung_hotmail_com
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;
PROC Star
Posts: 1,760

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

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 inSmiley Sad'A','B')));
quit;
Contributor
Posts: 70

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

Excellent!! This is exactly what I wanted.

Big thanks. Chris! this poster is resolved.
Valued Guide
Posts: 2,177

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

> 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
PROC Star
Posts: 1,760

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

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;
Valued Guide
Posts: 2,177

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

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
Ask a Question
Discussion stats
  • 18 replies
  • 4742 views
  • 0 likes
  • 9 in conversation