Date:         Mon, 26 Nov 2007 20:24:46 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: IN operator in Proc SQL

On Mon, 26 Nov 2007 13:50:59 -0800, Ruve <bandbig@GMAIL.COM> wrote:

>In a simple SQL like below, the IN performs a full match against a
>given list.  What about if I want to partially match by the first
>digits only, as we do LIKE 'abc%' on single case basis?
>
>Should I use substr(name,1) in (select name from c)?

I think so, as long as you always want to match on the same number of
characters (3 in the example). You will need SUBSTR for both sides of the
comparison.

In more complicated situations you may need to use a join instead of a
subquery and to use regular expressions (as Sig suggested).

>Or any other ways
>in SQL?  Thanks!
>
>*================;
>proc sql;
>        create table b as
>                select id, name
>                from a
>                where name in (select name from c)
     order by id;
>quit;