Help using Base SAS procedures

subquery in a connect statement

Reply
N/A
Posts: 0

subquery in a connect statement

I am trying to extract Oracle data but when I use a subquery as a list as follows -

proc sql;
&connect;
create TABLE /*RF300L1.*/D_FAB300_edc_collected1 as
select * from connection to ORACLE
(select * from edc_collected_data
where COL_INS_ID in
(select col_ins_id from Notfound_in_edc_collected));
disconnect from ORACLE;
quit;

but I recieve the following error-

ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL statement: select * from edc_collected_data where COL_INS_ID in (select col_ins_id from Notfound_in_edc_collected)
PROC Star
Posts: 7,366

Re: subquery in a connect statement

Have you checked to ensure that those are the correct file names? I found it surprising that one ended with _data, while the other didn't.

Art
Frequent Contributor
Posts: 91

Re: subquery in a connect statement

Are both tables, edc_collected_data and Notfound_in_edc_collected, Oracle tables, or is one of them a SAS table? In this query, they must both be Oracle tables.
N/A
Posts: 0

Re: subquery in a connect statement

Thanks advoss! I think that my answer the one in the subquery is a SAS table..

sorry checking my threads after a long long tme..
Super User
Posts: 9,691

Re: subquery in a connect statement

Hi.
It looks like you are using proc sql 's Pass-Through capability.
Do you try to use libname statement?


Ksharp
Frequent Contributor
Posts: 90

Re: subquery in a connect statement

I tried to use the LIBNAME statement before trying the Pass-Through SQL. Thanks for Google and you. I removed the LIBNAME statement from the Pass_Through and it worked.Smiley Happy

Ask a Question
Discussion stats
  • 5 replies
  • 605 views
  • 1 like
  • 5 in conversation