Joining tables in CAS

[Deep04]

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

[LinusH]

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

[Deep04]

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

[sbxkoenk]

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

[Deep04]

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

[sbxkoenk]

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

[Deep04]

Is it possible to execute a left join here?

[Deep04]

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

maybe the syntax is wrong ?

Can you please help me with this ??

[sbxkoenk]

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

[Deep04]

we are actually using 3.5 

[sbxkoenk]

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

[Deep04]

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

can you assist me with this??

[sbxkoenk]

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