topic Many to many merge in SAS Programming

Many to many merge

Ronein — Tue, 22 Feb 2022 05:55:17 GMT

Hello

Here is many to mane merge .

I need to get 9 rows but I get 5

How can I get the desired result please?

Data have1;
input AccountID trasactionKey;
cards;
123 222777
123 999999
456 333888
;
Run;


 Data have2;
 Input AccountID customerID;
 cards;
123 999
123 888
123 333 
123 222
456 333
;
Run;

Data wanted;
input AccountID trasactionKey customerID;
cards;
123 222777 999
123 222777 888
123 222777 333
123 222777 222
123 999999 999
123 999999 888
123 999999 333
123 999999 222
456 333888 333
;
Run;


proc sort data=have1;
by AccountID;
Run;

proc sort data=have2;
by AccountID;
Run;


Data My_Try_to_get_wanted;
merge have1(in=a) have2(in=b);
by AccountID;
if a;
Run;

Re: Many to many merge

andreas_lds — Tue, 22 Feb 2022 06:38:49 GMT

Please have a look at the documentation of the merge statement, there you will find an explanation.

Re: Many to many merge

Kurt_Bremser — Tue, 22 Feb 2022 07:07:13 GMT

9 observations points to a cartesian join, for which you need PROC SQL.

Re: Many to many merge

AndreaVianello — Tue, 22 Feb 2022 09:04:35 GMT

Data have1;
input AccountID trasactionKey;
cards;
123 222777
123 999999
456 333888
;
Run;


 Data have2;
 Input AccountID customerID;
 cards;
123 999
123 888
123 333 
123 222
456 333
;
Run;

/****   sql full join ****/

proc sql; 
 create table Want as
  select * 
   from have1 full join have2
   on have1.AccountID=have2.AccountID;
quit;