Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for Search instead for Did you mean:🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Opal | Level 21I 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;