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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.