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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: