Table of Contents
One-to-Many Merge in SAS is the process of merging two datasets that have one-to-many relationships. It involves using the MERGE statement in SAS with the BY option to match up the rows with common values in a key variable. This allows the user to combine the observations from both datasets into one dataset. It is important to note that one-to-many merges can only be accomplished with datasets that have one-to-many relationships, which means one observation in one dataset can have multiple observations in the other dataset.
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;
The one-to-many merge produced a new dataset that contains all information from both datasets.
Note: You can find the complete documentation for the SAS merge statement .
The following tutorials explain how to perform other common tasks in SAS: