Many-to-Many MERGE

Sample data:

data one;
input ID $ c1 $ n1;
cards;
A aa 11
A bb 12
A cc 13
B dd 14
;

data two;
input ID $ n2;
cards;
A 21
A 22
B 23
B 24
;

Code:

data combined;
merge one two;
by ID;
run;

Result:

ID    c1    n1    n2

A     aa    11    21
      bb    12    22
      cc    13    22

B     dd    14    23
      dd    14    24

Usually such a merge is undesirable, but not always. In some situations, the problem is to exclude the repetitions caused by the "inheritance" of values (notice 22, dd, and 14 in the example). Here is a simple solution:

data combined;
merge one two;
by ID;
output;
call missing(of _all_);
run;

Result:

ID    c1    n1    n2

A     aa    11    21
      bb    12    22
      cc    13     .

B     dd    14    23
             .    24

Of course if the DATA step is more complicated and involves additional variables which are RETAINed, the process performed by the CALL MISSING statement has to be selective.

Note: This technique is applicable to one-to-many or many-to-one situations as well as to many-to-many.