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>
Subject: Re: array in sql?
Content-Type: text/plain; charset="us-ascii"
There are lots of ways to do this, but:
input y1 $ y2 $ y3 $ y4 $ y5 $;
them arch each meal take
port bean file drug mack
input x1 $ x2 $;
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
create table want as
where (x1 not in (select crit from match_criteria))
and (x2 not in (select crit from match_criteria))
If it's a quick, one-off thing you're doing, this should do the trick.
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
> 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;
> Any comments on how to make it work are greatly appreciated.