Date: Thu, 7 Sep 2006 13:17:12 -0700
Reply-To: Jairaj Sathyanarayana <jairajs@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jairaj Sathyanarayana <jairajs@GMAIL.COM>
Subject: Re: Issue with PROC SQL
In-Reply-To: <004f01c6d2b5$9f3015c0$419e5680@hpdv1267ea>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
One of the many possible methods to solve your case---
proc sql;
create table t3 as
select t1.*
from t1
where not exists (select * from t2 where t1.id=t2.id);
*quit*;
Hope this helps.
Jairaj
On 9/7/06, Nuno Soares <ns.mlists@gmail.com> wrote:
>
> Hi everyone,
>
> I'm having a problem using PROC SQL, that must be quite easy to identify
> but
> I haven't found a justification.
> I have two tables, table1 and table2. Table1 includes some observations
> from
> table2 plus other observations that aren't included in table2, the same as
> table2 regarding table1, i.e.: table2 has observations that are in table1
> plus some other observations that aren't. Both tables have the same
> variables, including the -id- variable (string) that identify each
> observation. If the same observation is in Table1 and Table2, they have
> the
> same id. I'm trying to get a table (table3) that has the observations from
> table1 but excluding the observations that are in both table1 and table2.
> At first I tried the following SQL:
>
> proc sql;
> create table table3
> as select a.*
> from table1 as a, table2 as b
> where a.id<>b.id;
> quit;
>
> I thought that this should do the trick, but I was wrong... The result
> from
> this SQL is a table that comprises the following observations: (table1
> observations)*(table2 observations) - (observations that are both in
> table1
> and table2). The result should have been: table1 observations -
> observations
> that are both in table1 and table2.
>
> Any ideas? This should be a simple procedure, but I can't find a
> solution...
>
> Best,
>
> Nuno
>
| 
