LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (September 2006, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Nuno Soares <ns.mlists@gmail.com>
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 >


Back to: Top of message | Previous page | Main SAS-L page