BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasasauraus
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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
Fluorite | Level 6

Thank you very much for the solution and speedy reply.

Reeza
Super User
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
Fluorite | Level 6

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

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 632 views
  • 0 likes
  • 3 in conversation