I encountered a pretty strange problem while reviewing some code programmed by another programmer. First of all he was using nested joins in PROC SQL. By nested I mean that there is joins appended like the following:
proc sql;
create table test as
select a.*, coalesce(b.var,c.var,d.var,e.var) as newvar
from data1 as a left join data2(where=(some filter)) as b on a.key=b.key
where some conditions for a and/or b
left join data3(where=(some filter)) as c on a.key=c.key
where some conditions for a and/or c
left join data4(where=(some filter)) as d on a.key=d.key;
where some conditions for a and/or d
quit;
And the joins SEEM to go as intended. I was not aware that this kind of nesting is even possible so does SAS process this sequentially so that it first performs the join with a and b and then the result table is joined with c etc.? For example I don't quite understand how SAS handles the situations where there are common columns in b,c and d and they are already contained in the result table that the new condition is then left joined...
And then we come to the second question which is probably somehow related to the above mentioned processing. The reason I previously wrote SEEM is because the coalesce does not output correct values. For example if we have a situation that the current iterations state is that b.var="" c.var="" and d.var="value" then the coalesce assigns newvar="" BUT if the order of the arguments are changed (but the state is still the same) for example to coalesce(b.var,d.var,c.var) then the newvar gets the value of "value"...?!?
So my initial reasoning was that the aliases a,b,c and d refers to the filtered data (for example data2(where=(some filter)) for b) and not to the joined data when using the coalesce (I am not quite sure if coalesce is transforming into a SAS function instead of SQL function because of several arguments, since the coalesce is supported by SQL if I am not wrong). But this reasoning was revoked because when I tried this manually by filtering all the datasets with the where filter and the outcome was just as desired but the coalesce output was still wrong).
Hopefully someone can get the hang of this issue even there are no specific codes. Thanks!
... View more
