Merging Datasets: DATA Step MERGE vs. PROC SQL Joins
Compare match-merging using IN= variables with relational SQL inner, left, and full joins.
Merging in SAS combines observations from two or more datasets based on common key variables. In the DATA step, match-merging requires pre-sorted data and uses the MERGE and BY statements alongside IN= flags. In PROC SQL, tables are joined dynamically without requiring pre-sorting using standard SQL JOIN syntax.
Why Match-Merging vs. Relational Joins Matter for Large Data Lakes #
Choosing between DATA step merge and PROC SQL joins impacts query performance, memory consumption, and data integrity in large enterprise data lakes.
Standard Syntax, Derivation & Framework Pattern #
/* Method 1: DATA Step Match-Merge */
PROC SORT DATA=work.demog; BY Subject_ID; RUN;
PROC SORT DATA=work.labs; BY Subject_ID; RUN;
DATA work.merged_patient;
MERGE work.demog(IN=a) work.labs(IN=b);
BY Subject_ID;
IF a AND b; /* Inner Join Equivalent */
RUN;
/* Method 2: PROC SQL Join (No pre-sort required) */
PROC SQL;
CREATE TABLE work.sql_merged AS
SELECT d.Subject_ID, d.Age, d.Sex, l.Lab_Result
FROM work.demog AS d
INNER JOIN work.labs AS l
ON d.Subject_ID = l.Subject_ID;
QUIT;Core Rules #
- DATA step MERGE requires all input datasets to be sorted by the BY variables beforehand.
- IN= options create temporary flags (0 or 1) indicating whether an input dataset contributed to the current PDV row.
- PROC SQL does not require pre-sorted inputs and handles Cartesian products and many-to-many joins cleanly.
- For 1-to-many merges, DATA step MERGE retains non-overwritten values from earlier tables, which can cause unexpected data propagation.
How memory topology, PDV streaming, and join cardinality determine whether to use DATA step or PROC SQL on enterprise tables.
Frequently Asked Questions (FAQ) #
Does PROC SQL require tables to be sorted before joining?
No. Unlike DATA step MERGE which mandates pre-sorted datasets by the BY variables, PROC SQL dynamically joins tables on any matching predicate.
How does the IN= dataset option work during a MERGE?
The IN= option creates a temporary boolean variable (1 if the dataset contributed data to the current observation, 0 otherwise), enabling precise filtering for left, right, and inner joins.
Want complete video breakdowns, code files & exercises?
Explore comprehensive step-by-step masterclass training in our authoritative SAS Certification Course & Programming Training.