BookmarkSubscribeRSS Feed
Deep04
Obsidian | Level 7

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

12 REPLIES 12
LinusH
Tourmaline | Level 20

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

Data never sleeps
Deep04
Obsidian | Level 7

Deep04_0-1698656119672.png

 

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

sbxkoenk
SAS Super FREQ

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
Obsidian | Level 7

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

sbxkoenk
SAS Super FREQ

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
Obsidian | Level 7
Is it possible to execute a left join here?
Deep04
Obsidian | Level 7

Deep04_1-1698663104038.png

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

maybe the syntax is wrong ?

Can you please help me with this ??

sbxkoenk
SAS Super FREQ

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
Obsidian | Level 7

Deep04_0-1698688530451.jpeg

we are actually using 3.5 

 

sbxkoenk
SAS Super FREQ

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
Obsidian | Level 7

Deep04_0-1698663635139.png

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

can you assist me with this??

sbxkoenk
SAS Super FREQ

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 2723 views
  • 2 likes
  • 3 in conversation