Mastering Data Preparation in SAS: A Comprehensive Guide to Merging Data Sets
As a data scientist, a significant portion of your time and effort is dedicated to data preparation. In fact, experts estimate that data scientists spend anywhere from 50% to 80% of their time on data preparation tasks such as cleaning, transforming, and combining data sets [1]. The quality and structure of the input data directly impacts the performance of machine learning models, so it‘s critical to get this step right.
One of the most common data preparation tasks is merging or joining related data sets together. Merging allows you to combine data from multiple sources, integrate data at different granularity levels, and create analytical data sets tailored for specific business questions.
SAS provides powerful tools for merging data sets, notably the MERGE statement in the DATA step and the SQL join clauses in PROC SQL. In this guide, we‘ll dive deep into SAS merging techniques, with a focus on how to effectively combine data sets for machine learning applications. Whether you‘re a SAS programmer looking to upskill or a data scientist seeking to add SAS to your toolkit, this guide will provide you with a comprehensive understanding of merging data sets in SAS.
Understanding the SAS DATA Step and MERGE Statement
At the heart of SAS data manipulation is the DATA step, a versatile programming environment for reading, transforming, and outputting data. The DATA step works by iterating over each observation in the input data set(s), using a construct called the Program Data Vector (PDV) to create and update variable values, and then writing the result to an output data set [2].
When merging data sets using the DATA step, the MERGE statement is used to specify the input data sets and how they should be combined. Here‘s the basic syntax:
DATA output-dataset;
MERGE dataset1 dataset2 ... datasetn;
BY by-variable(s);
RUN;Some key points about the MERGE statement:
- The input data sets must be sorted by the variables specified in the BY statement.
- The PDV is initialized with missing values for all variables at the start of each iteration.
- As each input data set is read, the values of matching variables are overwritten in the PDV.
- By default, the number of observations in the output data set is the sum of the number of observations in all input data sets.
Let‘s walk through a simple example to illustrate. Suppose we have two data sets:
EMPLOYEES:
| Employee_ID | Name | Hire_Date |
|---|---|---|
| 1001 | John | 01Jan2020 |
| 1002 | Sally | 15Mar2018 |
| 1003 | Bob | 30Jun2022 |
JOB_TITLES:
| Employee_ID | Title | Effective_Date |
|---|---|---|
| 1001 | Analyst | 01Jan2020 |
| 1001 | Senior Analyst | 01Jan2022 |
| 1002 | Manager | 15Mar2018 |
To merge these data sets and create a new data set with each employee‘s current job title, we can use the following code:
PROC SORT DATA=employees;
BY employee_id;
RUN;
PROC SORT DATA=job_titles;
BY employee_id descending effective_date;
RUN;
DATA employee_current_title;
MERGE employees (IN=e) job_titles (IN=j);
BY employee_id;
IF e AND j;
RUN;Here‘s what happens during the DATA step:
-
For the first observation of EMPLOYEES (John), the PDV is initialized with
Employee_ID=1001,Name=John,Hire_Date=01Jan2020, and missing values forTitleandEffective_Date. -
The first observation of JOB_TITLES for employee 1001 is read, and the values of
TitleandEffective_Dateare overwritten in the PDV. -
The second observation of JOB_TITLES for employee 1001 is read, and the values of
TitleandEffective_Dateare again overwritten in the PDV. This results in John‘s most recent title (Senior Analyst) being retained. -
The IF statement checks that the observation exists in both input data sets (e=1 and j=1). The observation is output to the
employee_current_titledata set. -
The process repeats for the next EMPLOYEES observation.
The final output data set looks like this:
| Employee_ID | Name | Hire_Date | Title | Effective_Date |
|---|---|---|---|---|
| 1001 | John | 01Jan2020 | Senior Analyst | 01Jan2022 |
| 1002 | Sally | 15Mar2018 | Manager | 15Mar2018 |
Notice that employee 1003 (Bob) is not included in the output because there is no matching observation for him in the JOB_TITLES data set. This is an example of an inner join, where only matching observations are kept. By default, the MERGE statement performs an outer join and keeps all observations from all input data sets. We can use subsetting IF statements and the IN= data set option to control the type of join, as shown above.
DATA Step Merges vs. SQL Joins
In addition to DATA step merges, SAS offers SQL join capabilities through PROC SQL. Both methods can be used to combine data sets, but there are some key differences:
- PROC SQL joins do not require the input data sets to be sorted and do not use a BY statement. Instead, the join logic is specified in the ON clause.
- With PROC SQL, you must explicitly specify the type of join (e.g. INNER JOIN, LEFT JOIN). The default MERGE behavior is an outer join.
- PROC SQL can more easily join data sets on different variable names using aliasing and calculated expressions in the ON clause.
- Complex multi-way joins are often easier to express and read using SQL syntax compared to multiple MERGE statements.
Here‘s an example of using PROC SQL to perform the same employee/job title join:
PROC SQL;
CREATE TABLE employee_current_title AS
SELECT e.employee_id,
e.name,
e.hire_date,
j.title,
j.effective_date
FROM employees e
INNER JOIN job_titles j
ON e.employee_id = j.employee_id
GROUP BY e.employee_id
HAVING j.effective_date = MAX(j.effective_date);
QUIT;In general, DATA step merges are well-suited for simpler one-to-one or one-to-many joins, especially when the input data sets are already sorted. SQL joins offer more flexibility and may be more intuitive for complex join logic, particularly for programmers with SQL experience.
Performance-wise, DATA step merges can be more efficient than SQL joins for large data sets, especially when the data is already sorted and indexed [3]. However, PROC SQL can take advantage of parallel processing and query optimization techniques that can make it faster in some cases. As with any performance-related question, it‘s best to test both methods on your specific data and system.
Merging Best Practices for Data Science
When preparing data for machine learning projects, there are several best practices to keep in mind when merging data sets in SAS:
-
Ensure data consistency: Before merging, make sure the variable types, lengths, and formats are consistent across data sets. Use PROC CONTENTS to review variable attributes and make adjustments as needed using LENGTH/ATTRIB statements or FORMAT/INFORMAT functions.
-
Validate join logic: Inspect samples of the input and output data to validate that the join logic is correct and the results are as expected. Pay attention to missing values, duplicate keys, and many-to-many relationships that could cause unintended results.
-
Handle missing values: Decide how to handle missing values in the input data sets. Options include using COALESCE to prioritize non-missing values, using indicator variables to flag observations with missing key values, or imputing missing values before merging.
-
Subset and filter early: To reduce memory usage and improve performance, subset and filter data sets to include only the necessary observations and variables before merging. Use WHERE, IF, KEEP, and DROP statements judiciously.
-
Document and comment: Clearly document the purpose and logic of each merge step in your code. Use descriptive variable and data set labels to make the merged data easier to understand and use for modeling.
-
Test edge cases: Test your merge code on edge cases like empty data sets, data sets with no matching observations, and data sets with duplicate keys to ensure it handles these scenarios gracefully.
By following these best practices, you can create clean, well-structured analytical data sets that will support robust and accurate machine learning models.
Conclusion
Merging data sets is a critical skill for any data scientist working with SAS. Whether you‘re building predictive models, creating reports and visualizations, or exploring relationships in your data, the ability to efficiently combine data from multiple sources is essential.
In this guide, we‘ve covered the fundamentals of merging data sets using the SAS DATA step and MERGE statement, as well as using SQL joins in PROC SQL. We‘ve discussed the differences between these two approaches and provided best practices for preparing data for machine learning projects.
As you work with larger and more complex data sets, you may encounter challenges like memory constraints, long processing times, and data quality issues. SAS offers many advanced techniques for optimizing data preparation tasks, such as parallel processing, in-database processing, and data quality transformations. Continuing to deepen your understanding of SAS data management will pay dividends as you tackle more ambitious data science projects.
Remember, the quality of your input data is one of the most important factors in the success of your machine learning models. By mastering data preparation techniques like merging in SAS, you‘ll be well-equipped to build accurate, reliable, and insightful models that drive business value.
