BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fisher
Quartz | Level 8
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.
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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;

View solution in original post

18 REPLIES 18
chang_y_chung_hotmail_com
Obsidian | Level 7

   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
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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
Quartz | Level 8
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
Tourmaline | Level 20

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
Quartz | Level 8
Excellent!! This is exactly what I wanted.

Big thanks. Chris! this poster is resolved.
Peter_C
Rhodochrosite | Level 12
> 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
Tourmaline | Level 20
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
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 28064 views
  • 0 likes
  • 9 in conversation