How to Perform One-to-Many Merge in SAS

You can use the following syntax to perform a one-to-many merge in SAS:

data final_data;
  merge data_one data_many;
  by ID;
run;

This particular example creates a new dataset called final_data by merging the datasets called data_one and data_many on the variable called ID.

In the data_one dataset, each unique ID value only appears once.

In the data_many dataset, each unique ID value occurs multiple times.

This is known as a one-to-many merge.

The following example shows how to use this syntax in practice.

Example: One-to-Many Merge in SAS

Suppose we have the following dataset called data_one that contains information about sales personnel at some company:

/*create dataset*/
data data_one;
    input ID Gender $;
    datalines;
1 Male
2 Male
3 Female
4 Male
5 Female
;
run;

/*view dataset*/ 
proc print data = data_one;

Notice that each unique ID value only occurs once in the dataset.

Now suppose we have another dataset called data_many that contains information about sales made by each sales person at various locations:

/*create dataset*/
data data_many;
    input ID Store $ Sales;
    datalines;
1 A 22
1 B 25
1 C 20
2 A 14
2 B 23
3 A 10
4 A 15
4 B 29
5 A 16
5 C 22
;
run;

/*view dataset*/ 
proc print data = data_many;

Notice that each unique ID value occurs multiple times.

We can use the following syntax to perform a one-to-many merge using these datasets:

/*create new dataset using one-to-many merge*/
data final_data;
  merge data_one data_many;
  by ID;
run;

/*view new dataset*/
proc print data=final_data;