Given the following table with two columns:

ID  ACC

A1  ACC1
A2  ACC1
A3  ACC1
B1  ACC2
B2  ACC2

All rows are related based on the ACC column. So my goal is to have the following table:

ID  ID2  ACC

A1  A2   ACC1
A1  A3   ACC1
A2  A1   ACC1
A2  A3   ACC1
A3  A1   ACC1
A3  A2   ACC1
B1  B2   ACC2
B2  B1   ACC2

CodePudding user response：

proc sql;
create table want as 
select left.ID, rigth.ID, left.ACC
from have as left, have as right
where left.ACC eq right.ACC
and left.ID ne right.ID;
quit;