And of course if you want to replicate the remerging to bit it's fairly easy... this is not tested but it should be approximately right. If the performance is not good on this, perform the subquery first to a temporary table (assuming you have rights to do so) and then query that instead of performing it inline. Or just perform both queries separately in the passthrough into two different SAS datasets and then let SAS merge them together.

proc sql; connect to oracle(path=&path user=&userid pass=&pwd); select * from connection to oracle (select * from &schema..ae_suspect_drugs D where PRIMARY_FLAG='Y' and drug_role='S' and exists ( select 1 from ( select case_id, count(1) from &schema..ae_suspect_drugs group by case_id having count(1) > 1 ) E where D.case_id = E.case_id ) group by case_id having count(*) > 1 ) ;

-Joe

On Wed, Sep 28, 2011 at 10:44 AM, Joe Matise <snoopy369@gmail.com> wrote:

> You in general cannot select things that you aren't grouping by, unless > you're using summary functions. Change the select * to select case_id and > it should work. SAS allows the 'remerging to ...' bit but that is highly > nonstandard. > > -Joe > > > On Wed, Sep 28, 2011 at 10:24 AM, Ya Huang <ya.huang@amylin.com> wrote: > >> Hi there, >> >> Is there something simple I missed? I just can't understand >> why the following gives me error: >> >> 28 proc sql; >> 29 connect to oracle(path=&path user=&userid pass=&pwd); >> 30 select * >> 31 from connection to oracle >> 32 (select * >> 33 from &schema..ae_suspect_drugs >> 34 where PRIMARY_FLAG='Y' and drug_role='S' >> 35 group by case_id >> 36 having count(*) > 1 >> 37 ) >> 38 ; >> ERROR: ORACLE prepare error: ORA-00979: not a GROUP BY expression. SQL >> statement: select * from aers.ae_suspect_drugs where >> PRIMARY_FLAG='Y' and drug_role='S' group by case_id having count(*) >> > 1. >> NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of >> statements. >> 39 quit; >> >> >> If I remove group by and having statement, it works fine. Is the "group >> by" in Oracle different from Proc SQL? >> >> Thanks >> >> Ya >> > >