topic Re: Joining tables in CAS in SAS Programming

Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 06:15:17 GMT

How can we join tables in CAS? which procedure can be used to join tables ?

Re: Joining tables in CAS

LinusH — Mon, 30 Oct 2023 08:46:16 GMT

If you want the join to execute in CAS, use PROC FEDSQL.

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 08:58:04 GMT

I try to join the tables using the Fedsql procedure, but it fails...
Can you assist me with this?

Re: Joining tables in CAS

sbxkoenk — Mon, 30 Oct 2023 09:43:16 GMT

Your 'a' - table most probably also has a column Regn_no_new.
Try putting:

select a.* , b.Regn_no_new as bRegn_no_new

Koen

Re: Joining tables in CAS

sbxkoenk — Mon, 30 Oct 2023 09:49:06 GMT

Instead of using a procedure, you can also do it via a CAS-enabled data step.

proc cas;
    dataStep.runCode /
    code="
        data table_name;
         merge table_A table_B;
		 by P_Policy_Number;
        run;
        ";
quit;

Koen

Re: Joining tables in CAS

sbxkoenk — Mon, 30 Oct 2023 09:50:45 GMT

Instead of using PROC FEDSQL, you can also use the fedsql.execDirect action in PROC CAS.

proc cas;
    fedsql.execDirect/
    query=
         "
          select *
          from TABLE1 a inner join TABLE2 b
          on a.VAR1 = b.VAR1
         "
    casout={name="TABLE3", replace=True};
quit;

Koen

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 10:43:59 GMT

Is it possible to execute a left join here?

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 10:53:21 GMT

I try it your way but still it doesn't work....

maybe the syntax is wrong ?

Can you please help me with this ??

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 10:54:52 GMT

But I don't want to change the column name...

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 11:02:55 GMT

I tried your way too but it also end up getting errors.

can you assist me with this??

Re: Joining tables in CAS

sbxkoenk — Mon, 30 Oct 2023 16:23:42 GMT

Hello,

I think you are confronted with this:

Problem Note 66120: Merging CAS tables with a BY statement produces an error regarding the BY variable format
https://support.sas.com/kb/66/120.html

Koen

Re: Joining tables in CAS

sbxkoenk — Mon, 30 Oct 2023 16:31:55 GMT

Hello,

I think the 'as a' and 'as b' do not work in PROC FEDSQL.
Drop the 'as' and just put :

table_1 a left join table_2 b

What's your Viya version? Check LOG after submitting this:

%put &=SYSVIYARELEASE;
%put &=SYSVIYAVERSION;

If it's still Viya 3.4 (without patch / hotfix), you might run into this issue:

Problem Note 65125: A PROC FEDSQL left join query returns randomly missing values in a massively parallel processing (MPP) environment for SAS® Viya® 3.4
https://support.sas.com/kb/65/125.html

Koen

Re: Joining tables in CAS

Deep04 — Mon, 30 Oct 2023 17:56:10 GMT

we are actually using 3.5