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.