Here's how:
DATA HAVE1;
INPUT (NAME1 NAME2 X1 X2) (:$8.);
CARDS;
Z J a a
Z J b a
Z J c b
X C b a
X C b c
X C c b
;
proc sql;
select a.name1, a.name2, a.overlap, b.all, c.nbX1, c.nbX2
from
(select name1, name2, count(*) as overlap
from
(select name1, name2, x1 as x from have1
intersect
select name1, name2, x2 as x from have1)
group by name1, name2) as a
inner join
(select name1, name2, count(*) as all
from
(select name1, name2, x1 as x from have1
union
select name1, name2, x2 as x from have1)
group by name1, name2) as b
on a.name1=b.name1 and a.name2=b.name2
inner join
(select name1, name2,
count(distinct X1) as nbX1,
count(distinct X2) as nbX2
from have1
group by name1, name2) as c
on a.name1=c.name1 and a.name2=c.name2
;
quit;
For some nasty reason, my SAS dies when I try to do this with natural joins . That's why I went back to explicit joins.
... View more
