Date:         Thu, 20 Sep 2007 21:34:59 -0700
Reply-To:     Tree Frog <tree.frog2@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tree Frog <tree.frog2@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: array in sql?
Comments: To: sas-l@uga.edu
In-Reply-To:  <1190338644.133972.95540@z24g2000prh.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Hi Tracy

There are lots of ways to do this, but:

data tomatch;
  input y1 $ y2 $ y3 $ y4 $ y5 $;
  datalines;
them arch each meal take
port bean file drug mack
;
run;

data source;
  input x1 $ x2 $;
  datalines;
arch hail
male beak
take fawn
five mack
four ease
;
run;

proc sql noprint;
* first, normalise the data you want as your match criteria: ;
  create table match_criteria as
    select y1 as crit from tomatch union
    select y2 as crit from tomatch union
    select y3 as crit from tomatch union
    select y4 as crit from tomatch union
    select y5 as crit from tomatch
  ;
* then, use this set in a not-in list in a query against your source
data: ;
  create table want as
  select *
  from source
  where (x1 not in (select crit from match_criteria))
    and (x2 not in (select crit from match_criteria))
  ;
quit;

If it's a quick, one-off thing you're doing, this should do the trick.

Tree Frog

On Sep 21, 11:37 am, lisiq...@yahoo.com wrote:
> Hi, everyone,
>
> I have two data files, a and b.  File a has two variables x1 and x2.
> File b has five variables y1-y5.  All these variables are characters.
> What I want is to look for observations in file a whose values of x1
> and x2 don't equal any of the variables y1-y5 in file b.
>
> I had the following but it didn't work.
>
> proc sql;
>  create table c as
>  select a.*
>  from a, b
>  where a.x1~=b.y1 and a.x1~=b.y2 and a.x1~=b.y3 and a.x1~=b.y4 and
> a.x1~=b.y5
>              and a.x2~=b.y1 and a.x2~=b.y2 and a.x2~=b.y3 and
> a.x2~=b.y4 and a.x2~=b.y5;
> quit;
>
> Any comments on how to make it work are greatly appreciated.
>
> Thanks!
> Tracy