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