Choose record with max date in oracle pass through

[TheNovice]

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;

[SASKiwi · Accepted solution]

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;

[TheNovice]

Thnk you!