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 (May 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 26 May 2008 11:47:44 +0100
Reply-To:     Ben Spong <bspong.stata@GOOGLEMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ben Spong <bspong.stata@GOOGLEMAIL.COM>
Subject:      Re: Question about sql
In-Reply-To:  <70bbc31b0805251615q9fc2502j21de6ac9dd75ce88@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Just a clarification of the reasoning I used in the last bit of the PROC SQL: by also imposing the restriction that the "matchid" found in the first step is also excluded from the selection process, I was trying to identify the second closest match to my selection criteria. It seems that the "have.matchid ^= match.matchid" restriction I imposed is not working as I was expecting and thus is not identifying the second best match. In fact, given that the id that is being retrieved is extacly the same as the one retrieved in the first step, it seems the restriction is not working at all. Any ideas on this?

On Mon, May 26, 2008 at 12:15 AM, Ben Spong <bspong.stata@googlemail.com> wrote: > I everyone, > > I'm using a sql proc to match a firm id to another firm id that > complies with the following two restrictions (each year): the match id > size must be within 70% and 130% of the size of id being tested; and > the bm variable of the matching id must be the closest to the bm of > the id to be matched. The SQL proc I'm using is the following: > > create table outdta(drop=bmabsdiff) as > select have.*, > match.id as matchid, > abs(have.bm - match.bm) as bmabsdiff > from dta1 as have join dta1 as match > on have.year = match.year > where 0.7 * have.size < match.size < 1.3 * have.size > and have.id ^= match.id > group by have.year, have.id > having bmabsdiff = min(bmabsdiff) > quit; > > The previous code works just fine. The problem I now need to find not > the match firm id that has the closest BM but the one that has the > second closest BM. I was trying to use the following code: > > create table outdta2 as > select have.*, > match.id as matchid2, > abs(have.bm - match.bm) as bmabsdiff > from outdta as have join outdta as match > on have.year = match.year > where 0.7 * have.size < match.size < 1.3 * have.size > and have.id ^= match.id and have.matchid ^= match.matchid > group by have.year, have.id > having bmabsdiff = min(bmabsdiff) > quit; > > I.e., I've included the restriction "and have.matchid ^= > match.matchid" in the where statement and I'm now using the "outdta" > as the basis tables. Unfortunatelly the code keeps retrieving exactly > the same id as the one retrieved in the first step. Anyone has an idea > of the reason for this and how to solve it? > > Best, > > B. >


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