Hello:
I have two datasets to merge but the results are not what I need.
HAVE:
Dataset1:
gic_id gic_acct gic_certno
7 1234 111
8 1234 112
Dataset2 (contains customer name, address etc and gic_acct):
gic_acct cust_name cust_number
1234 Jane 8888
1234 Bob 9999
Want (after merge):
since both customers share the same gic accounts:
gic_id gic_acct gic_certno cust_name cust_number
7 1234 111 Jane 8888
7 1234 111 Bob 9999
8 1234 112 Jane 8888
8 1234 112 Bob 9999
=====================================================
my code:
proc sort data=dataset1 out=d1;
by gic_acct;
run;
proc sort data=dataset2 out=d2;
by gic_acct;
run;
data merg1;
merge d1 (in=a)
d2 (in=b);
by gic_acct;
if a and b;
run;
Resulting output:
gic_id gic_acct gic_certno cust_name cust_number
7 1234 111 Jane 8888
8 1234 112 Bob 9999
Greatly appreciate your assistance!
Hi @sasasauraus Please think of SQL when you have many to many relationship and datastep for one to one or one to many relationships-
data set1;
input gic_id gic_acct gic_certno;
cards;
7 1234 111
8 1234 112
;
data set2;
input gic_acct cust_name $ cust_number;
cards;
1234 Jane 8888
1234 Bob 9999
;
proc sql;
create table want as
select a.*, cust_name, cust_number
from set1 a inner join set2 b
on a.gic_acct=b.gic_acct
order by gic_id,gic_acct;
quit;
Hi @sasasauraus Please think of SQL when you have many to many relationship and datastep for one to one or one to many relationships-
data set1;
input gic_id gic_acct gic_certno;
cards;
7 1234 111
8 1234 112
;
data set2;
input gic_acct cust_name $ cust_number;
cards;
1234 Jane 8888
1234 Bob 9999
;
proc sql;
create table want as
select a.*, cust_name, cust_number
from set1 a inner join set2 b
on a.gic_acct=b.gic_acct
order by gic_id,gic_acct;
quit;
Thank you very much for the solution and speedy reply.
Thank you Reeza for the details. The SAS Community board rocks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.