I Have code similar to the following, using the coalesce function to join Value1 to taple Temp_Original from 6 possible tables.
Is there a way to also have it output which table the value was taken from? I could write separate code to check the if the value is missing from each table in sequence, and output the Table name. But I was hoping there might be a smoother way to do it, perhaps using some saved value from the coalesce function indicating the placement of the first non empty value.
any ideas?
proc sql noprint; create table Temp_Join as select A.*, Coalesce(B.Value1,C.Value1,D.Value1,E.Value1,F.Value1,G.Value1) as Value1, Coalesce(B.Value2,C.Value2,D.Value2,E.Value2,F.Value2,G.Value2) as Value2 from Temp_Original as A left join Temp1 as B on A.SomeValue = B.SomeValue left join Temp2 as C on A.SomeValue = C.SomeValue left join Temp3 as D on A.SomeValue = D.SomeValue left join Temp4 as E on A.SomeValue = E.SomeValue left join Temp5 as F on A.SomeValue = F.SomeValue left join Temp6 as G on A.SomeValue = G.SomeValue; quit;
... View more
