You could be right, but I need a SQL view nonetheless. Turns out this is a case for a FULL JOIN. It's not pretty but it works.
proc sql;
create view merged_full_sum as
select distinct a.x, a.y, a.z,
sum(case when b.key1 = 8 then 1 else 0 end) as L8,
sum(case when b.key1 = 7 then 1 else 0 end) as L7,
sum(case when b.key1 = 9 then 1 else 0 end) as L9,
sum(case when c.key2 = 8 then 1 else 0 end) as M8,
sum(case when c.key2 = 7 then 1 else 0 end) as M7,
sum(case when c.key2 = 4 then 1 else 0 end) as M4,
sum(case when c.key2 = 9 then 1 else 0 end) as M9,
sum(case when c.key2 = 3 then 1 else 0 end) as M3,
(case when calculated L8 > 1 then 1 else calculated L8 end) as L8,
(case when calculated L7 > 1 then 1 else calculated L7 end) as L7_new,
(case when calculated L9 > 1 then 1 else calculated L9 end) as L9_new,
(case when calculated M8 > 1 then 1 else calculated M8 end) as M8_new,
(case when calculated M7 > 1 then 1 else calculated M7 end) as M7_new,
(case when calculated M4 > 1 then 1 else calculated M4 end) as M4_new,
(case when calculated M9 > 1 then 1 else calculated M9 end) as M9_new,
(case when calculated M3 > 1 then 1 else calculated M3 end) as M3_new
from TableA a
full join Long b on (a.x = b.ID)
full join Long2 c on (a.x = c.ID)
group by a.x;
quit;
... View more
