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 (February 2005, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 25 Feb 2005 14:15:39 -0600
Reply-To:     baogong jiang <bgjiang@gmail.com>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         baogong jiang <bgjiang@GMAIL.COM>
Subject:      Re: comparing records
Comments: To: LWn <lars.wahlgren.pleasenospam@stat.lu.se>
In-Reply-To:  <V5LTd.131309$dP1.470450@newsc.telia.net>
Content-Type: text/plain; charset=US-ASCII

This is from SAS online Doc:

This section compares merges to joins. DATA step match-merges and PROC SQL joins can produce the same results. However, a significant difference between a match-merge and a join is that you do not have to sort the tables before you join them. When All of the Values Match When all of the values match in the BY variable and there are no duplicate BY variables, you can use an inner join to produce the same result as a match-merge.

When Only Some of the Values Match When only some of the values match in the BY variable, you can use an outer join to produce the same result as a match-merge.

When the Position of the Values Is Important When you want to merge two tables and the position of the values is important, you might need to use a DATA step merge.

PROC SQL does not process joins according to the position of values in BY groups.Instead, PROC SQL processes data only according to the data values.

Baogong

On Fri, 25 Feb 2005 19:35:17 GMT, LWn <lars.wahlgren.pleasenospam@stat.lu.se> wrote: > If I'm not mistaken, just the opposite! Means/Merge kept the original order > wheras SQL did something to the original ordering. > I used Keas data as it is on the bottom of this meassage without any > presorting. > > /LWn > > "toby dunn" <tobydunn@HOTMAIL.COM> skrev i meddelandet > news:BAY101-F10E0BAE7BBDBD59035809ADE650@phx.gbl... > > LWN, > > > > SQL doesn't require a pre sort of the data like your proc means/merge > > does. > > I bet the SQL kept the original order of the data and applied the stats to > > that order, where you means/merge did some implicite order and/or your > > explicite sort order. > > > > > > > > Toby Dunn > > > > > > > > > > From: LWn <lars.wahlgren.pleasenospam@STAT.LU.SE> > > Reply-To: LWn <lars.wahlgren.pleasenospam@STAT.LU.SE> > > To: SAS-L@LISTSERV.UGA.EDU > > Subject: Re: comparing records > > Date: Fri, 25 Feb 2005 17:54:24 GMT > > > > I just tried both Bob's sql solution and my own proc means/merge. > > The results are almost the same. The values V1 are not in the same > > order. Means/Merge-solution keeps the original but but SQL does not. > > Can anybody explain? / LWn > > > > Proc Means/Merge gives > > Obs idno v1 maxv1 > > 1 1 10 20 > > 2 1 20 20 > > 3 1 8 20 > > 4 1 15 20 > > 5 2 34 34 > > 6 2 29 34 > > 7 2 12 34 > > > > From SQL > > Obs idno v1 maxv1 > > 1 1 8 20 > > 2 1 20 20 > > 3 1 10 20 > > 4 1 15 20 > > 5 2 12 34 > > 6 2 29 34 > > 7 2 34 34 > > > > > > > > "Bob Abelson" <Bob_Abelson@HGSI.COM> skrev i meddelandet > > news:OF3C32BAD8.D8A1ED63-ON85256FB3.005B393A-85256FB3.005B52B7@hgsi.com... > > > proc sql; > > > create table kea2 as > > > select idno, v1, max(v1) as maxv1 > > > from kea > > > group by idno; > > > quit; > > > > > > Bob Abelson > > > HGSI > > > 240 314 4400 x1374 > > > bob_abelson@hgsi.com > > > > > > > > > > > > > > > Kea <kea2003@AEMAIL4U.COM> > > > Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> > > > 02/25/2005 11:26 AM > > > Please respond to Kea > > > > > > > > > To: SAS-L@LISTSERV.UGA.EDU > > > cc: > > > Subject: comparing records > > > > > > > > > Hi All, > > > > > > My data set is like this: > > > > > > idno v1 > > > 1 10 > > > 1 20 > > > 1 8 > > > 1 15 > > > 2 34 > > > 2 29 > > > 2 12 > > > > > > I'd like to compare the v1 values for each idno and find the maximum. > > > > > > The output I would like is as follows: > > > > > > > > > idno v1 maxv1 > > > 1 10 20 > > > 1 20 20 > > > 1 8 20 > > > 1 15 20 > > > 2 34 34 > > > 2 29 34 > > > 2 12 34 > > > > > > Many thanks in advance. >

-- Baoogng Jiang Department of Agronomy Lousisana State University


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