As others already shared first you need to have certainty how the SQL logic needs to look like ...and then you need to define this logic in DI Studio.
Based on the sample data @A_SAS_Man created for you below two SQL coding options
data a;
input Id A $1. ;
datalines;
1 X
2 X
3 X
4 X
5 X
6 X
8 X
;
data b;
input Id B $1.;
datalines;
4 Y
5 Y
6 Y
7 Y
8 Y
9 Y
;
data c;
input Id C $1.;
datalines;
1 Z
2 Z
8 Z
9 Z
10 Z
11 Z
;
/* option 1 */
proc sql;
create table want as
select
coalesce(a.id,b.id,c.id) as id,
a.a,
b.b,
c.c
from
a
full outer join b
on a.id=b.id
full outer join c
on a.id=c.id or b.id=c.id
order by id
;
quit;
/* option 2 */
proc sql;
create view v_inter as
select
coalesce(a.id,b.id) as id,
a.a,
b.b
from
a full outer join b
on a.id=b.id
;
quit;
proc sql;
create table want as
select
coalesce(i.id,c.id) as id,
i.a,
i.b,
c.c
from
v_inter i full outer join c
on i.id=c.id
order by id
;
quit;
As @ErikLund_Jensen hints it's sometimes not that intuitive to define a SQL using the SQL transformation even if you exactly know what syntax you want it to generate.
For this reason I sometimes used to "break" things up into more simple SQLs. In above code you could implement option 2 using two SQL transformation nodes - the first one creates the view, the second one then joins your 3rd table to the view to create the table you're after. Because the first SQL only creates a view going for two nodes in the DI flow won't have a negative impact on performance (a view is basically encapsulated SQL code that only gets executed when you use the view ...which is only in the 2nd SQL node).
... View more