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 (August 2008, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


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