3.3 Match-Merging with BY Statements & FIRST./LAST. Processing

3.3 Match-Merging with BY Statements & FIRST./LAST. Processing

Quick Answer: Match-merging combines datasets horizontally based on common key variables using MERGE and BY. During BY-group processing, SAS creates temporary flags FIRST.variable and LAST.variable to mark group boundaries.

Match-merging is one of the most powerful and tested features on the SAS Base Programming exam. Unlike vertical combination (SET), match-merging uses the MERGE statement accompanied by a BY statement to join observations side-by-side (horizontally) when values of common key variables match.

---

1. Prerequisites and Fundamentals of Match-Merging

To execute a match-merge, the DATA step must include a MERGE statement specifying the input datasets, followed by a BY statement naming the key variable(s).

data work.merged_data;
    merge work.customers work.orders;
    by CustomerID;
run;

Strict Requirements & Behaviors

1. Sorting Requirement: Every dataset listed in the MERGE statement MUST be sorted by the variable(s) listed in the BY statement.
2. PDV Combination: Observations with matching BY values are merged into a single PDV row, aligning variables from both datasets.
3. Retention Across BY Groups: At the start of a new BY group, non-BY variables in the PDV are set to missing. However, while processing multiple records within the same BY group, SAS retains values in the PDV across iterations until the BY group changes.

---

2. Overwriting Hazard: Identical Non-BY Variable Names

If input datasets share non-BY variable names, values from datasets listed later in the MERGE statement will overwrite values from earlier datasets in the PDV!

/* Overwriting Example */
data work.dept_a; input ID Status $; datalines;
101 Active
; run;

data work.dept_b; input ID Status $; datalines;
101 Pending
; run;

data work.merged_status;
    merge work.dept_a work.dept_b;
    by ID;
run;

Result: In work.merged_status, the value of Status for ID=101 will be "Pending" because dept_b was listed second in the MERGE statement and overwrote "Active" from dept_a. To prevent unexpected overwriting, use the RENAME= dataset option.

---

3. FIRST. and LAST. Temporary Variables

When a BY statement is present in a DATA step (with SET, MERGE, or UPDATE), SAS automatically creates two temporary numeric variables for every variable listed in the BY statement:

• FIRST.byvar: Set to 1 on the first observation of a BY group; otherwise 0.
• LAST.byvar: Set to 1 on the last observation of a BY group; otherwise 0.

data work.sales_summary;
    merge work.customers work.transactions;
    by CustomerID;
    
    /* Temporary variables FIRST.CustomerID and LAST.CustomerID exist in PDV */
    if FIRST.CustomerID then AccountTotal = 0;
    
    AccountTotal + Amount;
    
    if LAST.CustomerID then output;
run;

Rules Governing FIRST. and LAST. Variables

1. Temporary Status: FIRST. and LAST. variables exist only in the PDV during execution and are never written to the output dataset.
2. Hierarchy Rule for Multiple BY Variables: When multiple variables are listed in the BY statement (e.g., BY Region State;), higher-level variable transitions propagate down:
   • If FIRST.Region = 1, then FIRST.State is automatically set to 1, regardless of whether the state value changed.
   • If LAST.Region = 1, then LAST.State is automatically set to 1.

Sample Data Evaluation Matrix

Consider a dataset sorted by Region and Store:

---

4. Controlling Merge Joins with the IN= Option

The IN= dataset option creates a temporary numeric variable (valued 1 or 0) indicating whether a specific input dataset contributed data to the current PDV iteration.

data work.inner_join work.left_join;
    merge work.clients (in=in_c) work.orders (in=in_o);
    by ClientID;
    
    /* Inner Join: record present in BOTH datasets */
    if in_c and in_o then output work.inner_join;
    
    /* Left Join: record present in Client dataset */
    if in_c then output work.left_join;
run;

---

5. Exam Pitfalls & One-to-Many vs Many-to-Many Merges

• One-to-One Merge: Each key value appears once in each dataset. Smooth 1-to-1 row pairing.
• One-to-Many Merge: Key value appears once in lookup table, multiple times in transaction table. Lookup attributes repeat across all matched transactions.
• Many-to-Many Merge (DANGER): Key value appears multiple times in BOTH input datasets. SAS pairs records positionally within the key group until one dataset runs out, then retains the last values. This generates unpredictable, corrupt results and is almost always an exam question trap!