If your MERGE statement returns more observations than you expect, check these possible causes and take the corresponding action(s) before performing the MERGE again.

One of the character BY variables has leading blanks.

The values "  Smith" and "Smith" do not match.
To eliminate leading blanks so that the values match, use the STRIP function on the variable in each data set.

Character BY variables have mismatching capitalization.

The values "SMITH" and "Smith" do not match.
To eliminate this issue, use one of the case functions (UPCASE, PROPCASE, LOWCASE) on the variable in each data set to make them the same case.

Character BY variables have a mismatching number of blank spaces within the values.

The values "John   Smith" and "John Smith" do not match.
To eliminate this issue, use the COMPBL function to compress multiple blank spaces into one blank space.

Note: All of these character functions can be nested and performed in one DATA step.

Numeric BY variables do not match.

If computations have been done on numbers, including SAS dates and SAS times, the numbers might not match exactly, due to numeric precision issues. To eliminate this issue, use the ROUND function on the numeric BY variable in each data set.

Unknowingly doing a one-to-many merge.

Each BY group in the resulting data set will have the same number of observations as the "many" data set. If you have duplicates in one of the data sets, you might want to use PROC SORT to identify the duplicates and then eliminate them.

Expecting the IN= data set option to be reset on each observation.

This option indicates whether a data set has contributed to the merge for each BY group, not for each observation. For more information about using the IN= option, see SAS Note 24681: "Merge data sets that have an uneven number of members in the BY groups and reset the remaining observations to 'missing'."

Operating System and Release Information