Dataset Merge with incorrect results

[sasasauraus]

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!

[novinosrin · Accepted solution]

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;

[sasasauraus]

Thank you very much for the solution and speedy reply.

[Reeza]

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#p...

[sasasauraus]

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