I have a proc fed sql query where I am passing a query as text .
I wanted to run a another query . but I am getting error
ERROR: Unsupported operation in FedSQL query: IN/ANY/ALL subquery.
proc cas;
fedsql.execdirect status=s/
query="create table CAS_LIB.newt {options replace=true} as
select a.buis_id as b_id ,
b.plan_set as p_s ,
b.version_no as ver_no ,
a.date as date ,
a.mkd_price_amt as mkd_price_amt ,
from old_cas.tab1 as a,
old_cas.tab2 as b
where a.buis_id in
(select id from new_cas.tab3) and
upcase(a.a.buis_id) = upcase(b.buis_id)";
How can I use this subquery here in fed sql.
Sub-queries NOT using IN operator are OK. Try something like this:
proc cas;
fedsql.execdirect status=s/
query="create table CAS_LIB.newt {options replace=true} as
select a.buis_id as b_id ,
b.plan_set as p_s ,
b.version_no as ver_no ,
a.date as date ,
a.mkd_price_amt as mkd_price_amt ,
from old_cas.tab1 as a,
old_cas.tab2 as b
left join
(select distinct id from new_cas.tab3) as C
on A.buis_id = C.ID
where upcase(a.buis_id) = upcase(b.buis_id)
and not missing(C.ID)";
Hello,
FedSQL for CAS does not support use of non-correlated subqueries with the IN, ANY, and ALL predicates. For example, the following non-correlated subquery is not supported:
select * from table1 where x in (select x from table2);
At least this is true for SAS VIYA 3.5.
It's documented here :
FedSQL Programming for CAS
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casfedsql/p1ucns3lrhzhamn1k6fw6vhzhzam.htm
Good luck,
Koen
Sub-queries NOT using IN operator are OK. Try something like this:
proc cas;
fedsql.execdirect status=s/
query="create table CAS_LIB.newt {options replace=true} as
select a.buis_id as b_id ,
b.plan_set as p_s ,
b.version_no as ver_no ,
a.date as date ,
a.mkd_price_amt as mkd_price_amt ,
from old_cas.tab1 as a,
old_cas.tab2 as b
left join
(select distinct id from new_cas.tab3) as C
on A.buis_id = C.ID
where upcase(a.buis_id) = upcase(b.buis_id)
and not missing(C.ID)";
Please post your complete SAS log then including code and errors. We can't help without evidence.
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.