O'Reilly logo

SAS 9.4 Language Reference, 6th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Combining SAS Data Sets: Basic Concepts
What You Need to Know Before Combining Information Stored in
Multiple SAS Data Sets
Many applications require input data to be in a specific format before the data can be
processed to produce meaningful results. The data typically comes from multiple sources
and might be in different formats. Therefore, you often, if not always, have to take
intermediate steps to logically relate and process data before you can analyze it or create
reports from it.
Application requirements vary, but there are common factors for all applications that
access, combine, and process data. Once you have determined what you want the output
to look like, you must perform the following tasks:
Determine how the input data is related.
Ensure that the data is properly sorted or indexed, if necessary.
Select the appropriate access method to process the input data.
Select the appropriate SAS tools to complete the task.
The Four Ways That Data Can Be Related
Data Relationship Categories
Relationships among multiple sources of input data exist when each of the sources
contains common data, either at the physical or logical level. For example, employee
data and department data could be related through an employee ID variable that shares
common values. Another data set could contain numeric sequence numbers whose
partial values logically relate it to a separate data set by observation number.
You must be able to identify the existing relationships in your data. This knowledge is
crucial for understanding how to process input data in order to produce desired results.
All related data fall into one of these four categories, characterized by how observations
relate among the data sets:
one-to-one
one-to-many
many-to-one
many-to-many
To obtain the results that you want, you should understand how each of these methods
combines observations, how each method treats duplicate values of common variables,
and how each method treats missing values or nonmatched values of common variables.
Some of the methods also require that you preprocess your data sets by sorting them or
by creating indexes. See the description of each method in “Combining SAS Data Sets:
Methods” on page 478.
One-to-One Relationship
In a one-to-one relationship, typically a single observation in one data set is related to a
single observation from another based on the values of one or more selected variables. A
468 Chapter 21 Reading, Combining, and Modifying SAS Data Sets
one-to-one relationship implies that each value of the selected variable occurs no more
than once in each data set. When you work with multiple selected variables, this
relationship implies that each combination of values occurs no more than once in each
data set.
In the following example, observations in data sets Salary and Taxes are related by
common values for EmployeeNumber.
Figure 21.1 One-to-One Relationship
SALARY
EmployeeNumber
Salary
TAXES
TaxBracket
1234 55000
1111 0.18
3333 72000
1234 0.28
4876 32000
3333 0.32
5489 17000
4222 0.18
4876 0.24
EmployeeNumber
One-to-Many and Many-to-One Relationships
A one-to-many or many-to-one relationship between input data sets implies that one data
set has at most one observation with a specific value of the selected variable, but the
other input data set can have more than one occurrence of each value. When you work
with multiple selected variables, this relationship implies that each combination of
values occurs no more than once in one data set. However, the combination can occur
more than once in the other data set. The order in which the input data sets are processed
determines whether the relationship is one-to-many or many-to-one.
In the following example, observations in data sets One and Two are related by common
values for variable A. Values of A are unique in data set One but not in data set Two.
Figure 21.2 One-to-Many Relationship
ONE
TWO
1
3
5
3
6
4
1
1
2
3
0
99
1 4 88
1
2
5
1
77
66
2 2 55
3 4 44
A E F
A B C
In the following example, observations in data sets One, Two, and Three are related by
common values for variable ID. Values of ID are unique in data sets One and Three but
not in Two. For values 2 and 3 of ID, a one-to-many relationship exists between
observations in data sets One and Two, and a many-to-one relationship exists between
observations in data sets Two and Three.
Combining SAS Data Sets: Basic Concepts 469

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required