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 (March 2011, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Wed, 9 Mar 2011 14:56:22 -0600
Reply-To:   Joe Matise <snoopy369@GMAIL.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Joe Matise <snoopy369@GMAIL.COM>
Subject:   Re: SQL lookup - more efficient way?
Comments:   To: Michael Murff <mysasbox@gmail.com>
In-Reply-To:   <AANLkTinhnRJPkvofLrjbwZfNY5wxTfoLNuxqG0P8PpM1@mail.gmail.com>
Content-Type:   text/plain; charset=ISO-8859-1

Do you have any indexes on the tables?

I think BETWEEN is technically slower than two < > criteria, at least on some SQL instances; I'm not sure why that is, though perhaps > and then < simply disqualifies records faster (some records fail the first criteria and do not have to evaluate the second). I do agree with Bob that a correlated subquery may be more efficient (depending on the relative size of the tables).

-Joe

On Wed, Mar 9, 2011 at 12:34 PM, Michael Murff <mysasbox@gmail.com> wrote:

> ** Hi sas-l; > ** my real tables are quite large; > ** anybody know how to optimize this using ansi standard SQL syntax? ; > > data main; > do id=1 to 10; > num_x=round(100*uniform(123),1); output; > end; > run; > > data lookup; > beg_num=1; end_num=10; output; > beg_num=11; end_num=20; output; > beg_num=21; end_num=30; output; > run; > proc sql; > select * from main, lookup > where num_x between beg_num and end_num > order by id; > quit; > endsas; > > ** thank you in advance; > ** Regards, Mike; >


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