topic Re: Many to Many join in SAS Enterprise Guide

Many to Many join

Sean_OConnor — Tue, 05 Jul 2022 15:02:10 GMT

Good Afternoon,

I have two datasets which I need to merge together with a primary key common to both. However, I need all possible combinations of joins.

For example - Dataset A has various different mortgage transactions associated with people over n number of years.

Dataset B - has various different property transactions associated with people over n number of years.

I can merge dataset A and B together based on a primary key (JoinKey) but the issue is that when there are many different potential matches they do not come out. As you see from below for JoinKey 2 there is basically 4 different properties matched to 4 different mortgages but in truth I need all potential 16 matches here - 4*4.

I'm not sure how to merge this in SAS so any help would be welcome.

JoinKey	Property_Key (Dataset B)	Mortgage Key (Dataset A)
1	20003B-1	010009P-452638dd4fe80bc6f1b0d2deac075492d7a
1	20003B-1	010009P-7454787243c7260e21563f7998fef1
2	12005V-1	011434X-a8687f0496365442dbda99ca74e
2	2102H-1	011434X-adc35318d5f0e
2	1208B-1	011434X-bd5b206ada728f18e7
2	1501V-1	011434X-c3601e9a24c8d7319ea1b

Re: Many to Many join

Kurt_Bremser — Tue, 05 Jul 2022 15:45:03 GMT

A cartesian join is best done in SQL; in a data step it needs additional coding.

data have1;
input key x;
datalines;
1 1
1 2
;

data have2;
input key y;
datalines;
1 3
1 4
;

proc sql;
create table want as
  select
    h1.key,
    h1.x,
    h2.y
  from have1 h1 full join have2 h2
  on h1.key = h2.key
;
quit;

proc print data=want noobs;
run;

Result:

Re: Many to Many join

Tom — Tue, 05 Jul 2022 15:47:01 GMT

For a many-to-many you probably want to use SQL.

proc sql;
create table want as
  select * 
  from DATASET_A a 
  full join DATASET_B b
    on a.JoinKey = b.JoinKey
;
quit;