Sig and Mark: Thank you very much for your assistance. I have been remiss in not sending out an email earlier but I've been swamped here.

Thanks and regards

Kumar

-----Original Message----- From: Sigurd Hermansen [mailto:HERMANS1@WESTAT.com] Sent: Thursday, March 29, 2007 5:30 PM To: Sridhar, Nagakumar; SAS-L@listserv.uga.edu Subject: RE: SQL Optimization help needed

Kumar: I have to confess that I don't know what you might do to optimize these queries. I can't predict how the logical condition 'min(absrslt)< lo and max(absrslt) < hi < 10' evaluates. Further, while correct syntax, the projection 'select *' leaves much unsaid in a GROUP BY query. I suspect that the yield of the query will depend on whether the actual SELECT list matches the GROUP BY list or not. Seems to me that you should make what you are trying to do more obvious to the 'L as well as to anyone that might have to maintain or modify them.

Now as for > The thing is I would like to have only those values in tsh_lo that > don't fit in to tsh_hi and tsh_hist and that's what is foxing me.

I understand and enjoy the '... what is foxing me ...' phrase, not something I would expect to hear in the USA, but I find the technical phrases more difficult to follow. The ambiguous 'SELECT *' clause leaves the relations to be derived from tsh undefined. What values don't fit in what values?

If I assume that you are looking for an EXCEPT query or a key set complement query (semidifference), you could write something along the lines of

select * from (select * from tsh_lo) except ( (select * from tsh_hi) union corr (select * from tsh_hist) ) ;

or

select * from (select * from tsh_lo) where ID not IN ( (select ID from tsh_hi) union (select ID from tsh_hist) ) ;

I'd spend some time first trying to simplify logical conditions and adding specific references to column variables. Then the SQL compiler may be able to optimize the performance of the queries. S

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Sridhar, Nagakumar Sent: Thursday, March 29, 2007 4:57 PM To: SAS-L@listserv.uga.edu Subject: SQL Optimization help needed

> Hey All: > > I would like to know if there is anyway of optimizing the > following SQL code: proc sql; > > create table tsh_lo as select * > > from tsh > > group by clinptcl,sitenbr,subjnbr > > having min(absrslt)< lo and max(absrslt) < hi < 10; > > > > create table tsh_hi as select * > > from tsh > > group by clinptcl,sitenbr,subjnbr > > having hi < max(absrslt) < 10; > > > > create table tsh_hist as select * > > from tsh > > group by clinptcl,sitenbr,subjnbr > > having max(absrslt) >= 10; > > quit; > > > The thing is I would like to have only those values in tsh_lo that > don't fit in to tsh_hi and tsh_hist and that's what is foxing me. > > TIA > > Kumar > ********************************************************************* This message and any attachments are solely for the intended recipient. If you are not the intended recipient, disclosure, copying, use or distribution of the information included in this message is prohibited -- Please immediately and permanently delete. ********************************************************************* This message and any attachments are solely for the intended recipient. If you are not the intended recipient, disclosure, copying, use or distribution of the information included in this message is prohibited -- Please immediately and permanently delete.