Date:         Sun, 3 Aug 2008 22:02:00 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: PROC SQL implementation data Merge
Comments: To: Gobjuka <hgobjuka2001@GMAIL.COM>
In-Reply-To:  <0a8d3f96-44ad-4e34-9f42-035267339b9c@l42g2000hsc.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Gobjuka:
In SAS SQL a LEFT JOIN combines an INNER JOIN of datasets one and two
(combining all tuples (rows) that have the same composite key values of
key1 and key2):

create table solution as select t1.* from one as t1 LEFT JOIN two as t2
ON t1.key1=t2.key1 and t1.key2=t2.key2
;

To remove from the yield the tuples from one that match on key1 and key2
to tuples in two, add to the end of the program (before the semi-colon):
  where t2.key1 IS NULL .

The relation (dataset) produced by the program then will include tuples
in one that do not match on both key1 and key2 to any tuple in two.

You can test whether your program works as specified with

create table test as select * from (select key1,key2 from one) except
(select key1,key2 from two)
;

The values of key1 and key2 in test should be the same as the ones in
solution.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Gobjuka
Sent: Sunday, August 03, 2008 7:40 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC SQL implementation data Merge


Hi All,

I am new to PROC SQL and trying to explore it.
I read papers relevant to various "JOINS" available in SQL. However I
was not sure as to how this could be implemented in PROC SQL.

I appreciate any help or pointers in this regard.

proc sort data = one; BY key1 key2; run;
proc sort data = two; BY key1 key2; run;

data result;
  merge one (in = a)
            two (in = b);
  BY key1 key2;
  if (a) and NOT(b);
run;

Gobjuka