MERGE Statement

Joins observations from two or more SAS data sets into single observations

Valid: in a DATA step

Category: File-handling

Type: Executable

Syntax

MERGE SAS-data-set-1 <( data-set-options )>

SAS-data-set-2 <( data-set-options )>
< SAS-data-set-n <( data-set-options )>>
<END= variable >;

Arguments

SAS-data-set(s)

names at least two existing SAS data sets from which observations are read.
Tip: Optionally, you can specify additional SAS data sets.

( data-set-options )

specifies one or more SAS data set options in parentheses after a SAS data set name .
Explanation: The data set options specify actions that SAS is to take when it reads observations into the DATA step for processing. For a list of data set options, see Definition of Data Set Options on page 6.

END= variable

names and creates a temporary variable that contains an end-of-file indicator.
Explanation: The variable, which is initialized to 0, is set to 1 when the MERGE statement processes the last observation. If the input data sets have different numbers of observations, the END= variable is set to 1 when MERGE processes the last observation from all data sets.
Tip: The END= variable is not added to any SAS data set that is being created.

Details

Overview The MERGE statement is flexible and has a variety of uses in SAS programming. This section describes basic uses of MERGE. Other applications include using more than one BY variable, merging more than two data sets, and merging a few observations with all observations in another data set.

One-to-One Merging One-to-one merging combines observations from two or more SAS data sets into a single observation in a new data set. To perform a one-to-one merge, use the MERGE statement without a BY statement. SAS combines the first observation from all data sets that are named in the MERGE statement into the first observation in the new data set, the second observation from all data sets into the second observation in the new data set, and so on. In a one-to-one merge, the number of observations in the new data set is equal to the number of observations in the largest data set named in the MERGE statement. See Example 1 for an example of a one-to-one merge. For more information, see Reading, Combining, and Modifying SAS Data Sets in SAS Language Reference: Concepts .

CAUTION:

Use care when you combine data sets with a one-to-one merge. One-to-one merges may sometimes produce undesirable results. Test your program on representative samples of the data sets before you use this method.

Match-Merging Match-merging combines observations from two or more SAS data sets into a single observation in a new data set according to the values of a common variable. The number of observations in the new data set is the sum of the largest number of observations in each BY group in all data sets. To perform a match-merge, use a BY statement immediately after the MERGE statement. The variables in the BY statement must be common to all data sets. Only one BY statement can accompany each MERGE statement in a DATA step. The data sets that are listed in the MERGE statement must be sorted in order of the values of the variables that are listed in the BY statement, or they must have an appropriate index. See Example 2 for an example of a match-merge. For more information, see Reading, Combining, and Modifying SAS Data Sets in SAS Language Reference: Concepts .

Comparisons

MERGE combines observations from two or more SAS data sets. UPDATE combines observations from exactly two SAS data sets. UPDATE changes or updates the values of selected observations in a master data set as well. UPDATE also may add observations.
Like UPDATE, MODIFY combines observations from two SAS data sets by changing or updating values of selected observations in a master data set.
The results that are obtained by reading observations using two or more SET statements are similar to those that are obtained by using the MERGE statement with no BY statement. However, with the SET statements, SAS stops processing before all observations are read from all data sets if the number of observations are not equal. In contrast, SAS continues processing all observations in all data sets named in the MERGE statement.

Examples

Example 1: One-to-One Merging

This example shows how to combine observations from two data sets into a single observation in a new data set:

 data benefits.qtr1;     merge benefits.jan benefits.feb;  run;

Example 2: Match-Merging

This example shows how to combine observations from two data sets into a single observation in a new data set according to the values of a variable that is specified in the BY statement:

 data inventry;     merge stock orders;     by partnum;  run;