How can we join tables in CAS? which procedure can be used to join tables ?
If you want the join to execute in CAS, use PROC FEDSQL.
I try to join the tables using the Fedsql procedure, but it fails...
Can you assist me with this?
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
But I don't want to change the column name...
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
I try it your way but still it doesn't work....
maybe the syntax is wrong ?
Can you please help me with this ??
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
we are actually using 3.5
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
I tried your way too but it also end up getting errors.
can you assist me with this??
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
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.