[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Where and Contains
From:       "Richard A. DeVenezia" <radevenz () IX ! NETCOM ! COM>
Date:       2003-07-31 16:19:16
[Download RAW message or body]

"Susie Li" <Susie.Li@US.SANOFI.COM> wrote in message
news:OF166247FF.173947E9-ON85256D74.00495EA0@ny.sanofi.com...
> I do a lot of string searches  based on partial information as in the
> following example.  This is as close to the SQL wild-card search as I get
> in SAS, by using "Where" and "Contains" together.
>
> It is okay for checking one or two records this way, but gets rather
> cumbersome if I have to do many searches, since I have to type the
> "contains" text strings by hand one by one.  Is there an easier way to
> accomplish this by supplying a file instead?
>
>
>
> data found;
>      set demo;
>      where ((lname contains (upcase('baljep'))) and (fname contains
(upcase
> ('raj')))) or
>            ((lname contains (upcase('chawla'))) and (fname contains
(upcase
> ('rajesh')));
>      LFname=substr(lname,1,10)||'/'||substr(fname,1,10);
>
> proc print;
>      var LFname;
> run;
>
>
> Susie Li
> Sanofi-Synthelabo, Inc.
> 90 Park Ave
> New York, NY 10016
> (212)551-4385
> susie.li@us.sanofi.com
>


Instead of building a where clause, do a SQL join to your match criteria
This is more appropriate when the number of criteria is larger

data criteria;
  length lname fname $8;
  input lname fname ;
  lname = upcase(lname);
  fname = upcase(fname);
  id + 1;
cards;
baljep raj
chawla rejesh
run;

proc sql;
  create table found as select demo.*
  from demo, criteria as A, criteria as B
  where
  demo.lname contains trim(A.lname)
  and
  demo.fname contains trim(B.fname)
  and
  A.id = B.id
  ;
quit;


--
Richard A. DeVenezia, new SASCBTBL sample
http://www.devenezia.com/downloads/sas/sascbtbl/index.html#WindowsRegistry

[prev in list] [next in list] [prev in thread] [next in thread]