topic Re: Dataset Merge with incorrect results in SAS Programming

Dataset Merge with incorrect results

sasasauraus — Thu, 06 Jan 2022 17:15:26 GMT

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!

Re: Dataset Merge with incorrect results

novinosrin — Thu, 06 Jan 2022 17:29:20 GMT

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;

Re: Dataset Merge with incorrect results

Reeza — Thu, 06 Jan 2022 17:29:58 GMT

SAS data step cannot do a many to many merge, use the SQL solution instead.

Note: The MERGE statement does not produce a Cartesian product on a many-to-many match-merge. Instead, it performs a one-to-one merge while there are observations in the BY group in at least one data set. When all observations in the BY group have been read from one data set and there are still more observations in another data set, SAS performs a one-to-many merge until all BY group observations have been read.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lestmtsref/n1i8w2bwu1fn5kn1gpxj18xttbb0.htm#p1rfsa0978il4yn1q7s3rknmzfyr

Re: Dataset Merge with incorrect results

sasasauraus — Thu, 06 Jan 2022 20:08:14 GMT

Thank you very much for the solution and speedy reply.

Re: Dataset Merge with incorrect results

sasasauraus — Thu, 06 Jan 2022 20:09:42 GMT

Thank you Reeza for the details. The SAS Community board rocks!