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
Content-Type: text/plain; charset="us-ascii"
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
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sent: Sunday, August 03, 2008 7:40 PM
Subject: PROC SQL implementation data Merge
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;
merge one (in = a)
two (in = b);
BY key1 key2;
if (a) and NOT(b);