SQL full join for multiple tables

[Patrick]

Hi all,

I can't figure out how in below code the SQL would need to look like so it returns the desired result as done via a data step merge.

I'm looking for something "generic" that would also work for 6 source tables.

I want in the end to generate the code using dictionary tables and I understand that I might end-up with a data step merge with all the renaming "galore" for performance reasons. ...but still really curious how a SQL version would need to look like and I just can't manage to come-up with something simple/a single SQL.

/* create sample data */
data class_A;
  set sashelp.class(firstobs=1 obs=4);
  call missing(age,weight);
  keep name age weight sex;
run;
data class_B;
  set sashelp.class(firstobs=3 obs=6);
  call missing(age);
  keep name age weight sex;
run;
data class_C;
  set sashelp.class(firstobs=4 obs=8);
  keep name age weight sex;
run;

/* SQL join - not doing the right thing */
proc sql;
  create table not_there_yet_want as
  select
     coalesce(a.name,b.name,c.name) as name
    ,coalesce(a.age,b.age,c.age) as age
    ,coalesce(a.weight,b.weight,c.weight) as weight
    ,coalesce(a.sex,b.sex,c.sex) as sex

  from class_A a

  full join class_B b
  on a.name=b.name

  full join class_C c
  on a.name=c.name
  order by name
  ;
quit;

title 'SQL not returning desired result';
proc print data=not_there_yet_want;
run;
title;

/* desired result - but looking for a SQL version */
data desired;
  if 0 then set sashelp.class(keep=name age weight);
  merge
    class_a(rename=(age=_a_age weight=_a_weight))
    class_b(rename=(age=_b_age weight=_b_weight))
    class_c(rename=(age=_c_age weight=_c_weight))
    ;
  by name;
  age=coalesce(_a_age,_b_age,_c_age);
  weight=coalesce(_a_weight,_b_weight,_c_weight);
  drop _:;
run;

title 'Desired result - but not done using SQL';
proc print data=desired;
run;
title;

Thanks,

Patrick

[EyalGonen · Accepted solution]

Hi @Patrick 

See if this is what you meant:

/* SQL join - not doing the right thing */
proc sql;
  create table not_there_yet_want as
  select
     coalesce(a.name,b.name,c.name) as name
    ,coalesce(a.age,b.age,c.age) as age
    ,coalesce(a.weight,b.weight,c.weight) as weight
    ,coalesce(a.sex,b.sex,c.sex) as sex

  from class_A a

  full join class_B b
  on a.name=b.name

  full join class_C c
  on coalesce(a.name,b.name)=c.name
  order by name
  ;
quit;

[Patrick]

@EyalGonen That did the trick. Thanks!