topic Choose record with max date in oracle pass through in SAS Programming

Choose record with max date in oracle pass through

TheNovice — Tue, 07 Jan 2020 19:17:15 GMT

hi, I can't get this query to work...

I am trying to extract records with code S from COL table where IDis from another table (not oracle)

I have tried the 2 variations below but both given an error. What am i doing wrong ? Appreciate any direction

proc sql;
CONNECT TO ORACLE (user=&name pw=&pass path=Ex);
create table SUS as
  select * from connection to oracle
  (select ID,COL_ACTV_CODE,max(COL_ACTV_DATE)
   from ods.COL
   group by ID
   where COL_ACTV_CODE = 'S'

       )
   WHERE ID IN (SELECT ID FROM TEST1)
;
disconnect from oracle;
quit;

proc sql;
CONNECT TO ORACLE (user=&name pw=&pass path=Ex);
create table SUS as
  select * from connection to oracle
  (select ID,COL_ACTV_CODE,max(COL_ACTV_DATE)
   from ods.COL
   group by ID
   having COL_ACTV_CODE = 'S'

       )
   WHERE ID IN (SELECT ID FROM TEST1)
;
disconnect from oracle;
quit;

Re: Choose record with max date in oracle pass through

SASKiwi — Tue, 07 Jan 2020 20:05:19 GMT

Your WHERE statement must go before the GROUP BY. Please note you will have a re-merging data problem as COL_ACTV_CODE isn't included in your GROUP BY.

proc sql;
 CONNECT TO ORACLE (user=&name  pw=&pass  path=Ex);
 create table SUS as
  select * from connection to oracle
  (select ID,COL_ACTV_CODE,max(COL_ACTV_DATE)
   from  ods.COL
  where COL_ACTV_CODE = 'S'
   group by ID
  )
   WHERE ID IN (SELECT  ID FROM TEST1)
 ;
 disconnect from oracle;
quit;

Re: Choose record with max date in oracle pass through

TheNovice — Wed, 08 Jan 2020 20:35:07 GMT

Thnk you!