topic Re: sql or fedsql join to mimic join with exists operator in SAS Programming

sql or fedsql join to mimic join with exists operator

acordes — Tue, 27 Dec 2022 12:21:13 GMT

I want to resolve in one join what actually requires a secondary data step.

In my understanding I cannot use the exists operator as fedsql on caslibs does not allow for this option.

The following code does what it should but I want the (fedsql) to return only these rows form the left table that do not exist in the second table.

proc cas;
source MPG_toyota;
    create table public.sum22{options replace=true as 
      select a.*, case when b.codopera='' then 0 else 1 end as flagy 
               from PUBLIC.JS_MTOS_V2AA a left join MKT.OUTYS b 
      on b.codopera=a.codopera    ;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

data public.sum22;
set public.sum22;
where flagy=0;
run;

Re: sql or fedsql join to mimic join with exists operator

Tom — Tue, 27 Dec 2022 18:27:49 GMT

The documentation seems to clearly say that FEDSQL supports EXISTS.

https://documentation.sas.com/doc/en/pgmsascdc/v_034/fedsqlref/n0j8gspb9heiqgn1rneohxqxx3s3.htm

Is there some place where it is documented that you cannot use it from PROC CAS?

Re: sql or fedsql join to mimic join with exists operator

PGStats — Wed, 28 Dec 2022 04:43:38 GMT

I am not a fedSQL user but for such a simple condition, you could use

select * from PUBLIC.JS_MTOS_V2AA
where codopera not in (select codopera from MKT.OUTYS)

No?

Re: sql or fedsql join to mimic join with exists operator

acordes — Wed, 28 Dec 2022 09:52:14 GMT

Thank you, I had tried it already.

It returns the error:

ERROR: Unsupported operation in FedSQL query: IN/ANY/ALL subquery.

Re: sql or fedsql join to mimic join with exists operator

acordes — Wed, 28 Dec 2022 10:03:54 GMT

Thank you, I think it's not permitted.

https://documentation.sas.com/doc/en/vdmmlcdc/8.11/proc/p03fos5ei9l0opn1vm82gu383g10.htm