Skip to content
Advertisement

Join Multiple Table based on a condition

I have data where i need to join to different tables based on a condition, month. I am using proc sql. Currently I am using

Proc sql;
create table test as 
If month="mar"
then 
select a.*,
b.var1,
b.var2,
from 
data as a
left join
data2_mar
on a.id=b.id

else

select a.*,
c.var1,
c.var2,
from 
data as a
left join
data2_April
on a.id=c.id;
Run;

However, this is not working. Any help greatly appreciated.

Advertisement

Answer

You could do this in a single query, using conditional joins:

select 
    a.*,
    coalesce(b.var1, c.var1) var1,
    coalesce(b.var2, c.var2) var2
from data a
left join data2_mar b
    on  b.id = a.id
    and month = 'mar'
left join data2_april c
    on  c.id = a.id
    and month = 'april'

Note that you should not have several tables to store the same data over different months. Instead, you should have a single table, with one more column to store the month.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement