ExamFinch.com My Study

Study guide

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

Practice this exam

SAS Certified Specialist: Base Programming Using SAS 9.4 A00-231

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: SAS Base Programming Exam Overview

1.1 A00-231 Exam Format, Scoring & Test-Day Logistics 1.2 Official Content Areas, Weights & Study Plan

2 Chapter 2: Accessing & Creating SAS Data Structures

2.1 SAS Environment, Syntax Rules, & Data Step Basics 2.2 SAS Data Libraries & the LIBNAME Statement 2.3 Investigating Libraries with PROC CONTENTS & PROC DATASETS 2.4 Importing Delimited & Excel Data with PROC IMPORT 2.5 Raw Data Ingestion with INFILE and INPUT Statements

3 Chapter 3: Managing & Combining SAS Data Sets

3.1 Reading SAS Data Sets with the SET Statement 3.2 Combining Data Sets: Concatenating & Interleaving 3.3 Match-Merging with BY Statements & FIRST./LAST. Processing 3.4 Controlling Data Output & Variable Selection (KEEP, DROP, RENAME, OBS=)

4 Chapter 4: Data Transformation & Conditional Logic

4.1 Creating & Modifying Variables with Assignment Statements 4.2 Modifying Variable Attributes with LENGTH, LABEL, FORMAT & RENAME= 4.3 Conditional Processing: IF-THEN/ELSE Statements & WHERE Subsetting 4.4 Accumulating Totals & Subtotals (RETAIN & Sum Statement) 4.5 Processing Data Iteratively with DO Loops

5 Chapter 5: SAS Functions & Data Type Conversions

5.1 Character Functions for String Manipulation 5.2 Numeric & Mathematical Functions 5.3 Working with SAS Dates, Times, & Intervals 5.4 Reading & Displaying Dates with Informats and Formats 5.5 Explicit & Implicit Data Type Conversions (INPUT & PUT)

6 Chapter 6: Restructuring Data & Program Maintenance

6.1 SAS Array Processing for Multi-Variable Transformations 6.2 Sorting Data Sets with PROC SORT 6.3 Restructuring Data Sets with PROC TRANSPOSE 6.4 Creating & Applying Custom Formats with PROC FORMAT 6.5 Simplifying Programs with Macro Variables (%LET)

7 Chapter 7: Error Handling & Debugging SAS Programs

7.1 SAS Log Analysis & Diagnostic Messages 7.2 Syntax vs. Logic Errors & Phasing 7.3 Debugging Techniques with _ERROR_, _N_, & PUT Statements 7.4 Identifying Logic Errors with the PUTLOG Statement 7.5 Handling Missing Values & Preventing Calculation Propagations

8 Chapter 8: Summary & Detail Data Reporting

8.1 Detail Reporting with PROC PRINT 8.2 Descriptive Statistics with PROC MEANS & PROC SUMMARY 8.3 Frequency & Categorical Analysis with PROC FREQ 8.4 Screening Extreme & Missing Values with PROC UNIVARIATE 8.5 Custom Tabular Reports with PROC REPORT & PROC TABULATE

9 Chapter 9: ODS Output Delivery & Data Export

9.1 Output Delivery System (ODS) Architecture Basics 9.2 Generating PDF, HTML, & RTF Output Destinations 9.3 Exporting Reports to Excel with ODS EXCEL & XLSX 9.4 Generating CSV, PowerPoint & Other ODS Destinations 9.5 Customizing ODS Titles, Footnotes, & Global Options 9.6 Exporting SAS Data Sets with PROC EXPORT & the XLSX Engine

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

Key Takeaways Explain This Topic AI

Match-merging uses a MERGE statement accompanied by a BY statement to join observations horizontally based on common key values across pre-sorted input datasets.

During BY-group processing, SAS automatically creates temporary flags FIRST.variable and LAST.variable (1 for true, 0 for false) in the PDV.

Overwriting hazard: If non-BY variables share the same name across merged datasets, values from later datasets in the MERGE statement overwrite earlier ones.

When a higher-level BY variable changes (FIRST.var1 = 1), SAS automatically forces FIRST.var2 = 1 for nested BY variables.

The IN= dataset option creates temporary flags to distinguish dataset sources, allowing programmers to execute inner, left, or right joins.

Last updated: August 2026

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

Sorting Requirement : Every dataset listed in the MERGE statement MUST be sorted by the variable(s) listed in the BY statement.

PDV Combination : Observations with matching BY values are merged into a single PDV row, aligning variables from both datasets.

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

Temporary Status : FIRST. and LAST. variables exist only in the PDV during execution and are never written to the output dataset.

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 :

  • Region Store FIRST.Region LAST.Region FIRST.Store LAST.Store Notes
  • East 100 1 0 1 0 First obs of East region and Store 100
  • East 100 0 0 0 1 Last obs of Store 100
  • East 200 0 1 1 1 Single record for Store 200; Last obs of East
  • West 100 1 1 1 1 Single record for West region and Store 100
  • 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!