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 |
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:
key x y 1 1 3 1 1 4 1 2 3 1 2 4
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:
key x y 1 1 3 1 1 4 1 2 3 1 2 4
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.