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;
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;
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;
Thnk you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.