Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

Note:   In this scenario, a NOTE will be issued to the log which states "NOTE: MERGE statement has more than one data set with repeats of BY values." This message is meant to be informational.

A DATA step that performs a many-to-many merge does not produce a cartesian product across a BY-Group. Understanding the nature of a many-to-many MERGE will ensure you have chosen the correct method to combine your data to get the desired results.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

/********************************************************************************/
/* WORK.ONE has duplicates for A and C.  WORK.TWO has duplicates for B and C.   */
/* If your data is not in sorted order, a PROC SORT or index on the BY          */
/* variables would be necessary to perform the many-to-many merge.              */
/*                                                                              */
/* Note, when a BY statement is used with the MERGE statement, variables do not */
/* reinitialize to missing until the BY-Group changes.                          */
/********************************************************************************/

data one;
  input id $ fruit $12.;
datalines;
a apple
a apricot
b banana
c coconut
c cherry
c crabapple
;

data two;
  input id $ color $;
datalines;
a amber
b brown
b black
c cocoa
c cream
;

/* Note the different values for COLOR for BY-Group 'c'.  The value */
/* 'cocoa' is overwritten in the PDV with 'cream' when the second   */
/* observation in the BY-Group is read from WORK.TWO and carries    */
/* down the rest of the BY-Group.                                   */

data both; 
  merge one two;
  by id;
run;

proc print data=both;
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Obs id fruit       color

1   a  apple       amber
2   a  apricot     amber
3   b  banana      brown
4   b  banana      black
5   c  coconut     cocoa
6   c  cherry      cream
7   c  crabapple   cream

Operating System and Release Information