3.2 Combining Data Sets: Concatenating & Interleaving

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.2 Combining Data Sets: Concatenating & Interleaving

Key Takeaways Explain This Topic AI

Concatenating lists multiple data sets in a single SET statement (SET ds1 ds2;), reading all observations from ds1 sequentially followed by all observations from ds2.

Interleaving combines datasets in sorted order using a single SET statement paired with a BY statement (SET ds1 ds2; BY variable;).

Interleaving strictly requires all input data sets to be pre-sorted by the variable(s) listed in the BY statement.

If variables exist in one input dataset but not another, missing values (. or blank) are populated for observations originating from datasets lacking those variables.

If variable data types conflict across concatenated datasets (numeric vs character), SAS halts compilation with a syntax error.

Last updated: August 2026

3.2 Combining Data Sets: Concatenating & Interleaving

Quick Answer: Concatenating ( SET ds1 ds2; ) appends datasets sequentially, reading all rows from ds1 followed by all rows from ds2 . Interleaving ( SET ds1 ds2; BY var; ) combines pre-sorted datasets while preserving ascending order of the BY variable.

Combining data sets vertically is a fundamental task in SAS data management. SAS provides two distinct methods for vertical combination within a single SET statement: Concatenating and Interleaving . Understanding the mechanical differences in Program Data Vector (PDV) handling, attribute alignment, and sorting requirements is crucial for the SAS Base certification exam.

1. Concatenating Data Sets

Concatenation is the process of appending one dataset to the end of another. It occurs when two or more SAS data sets are listed after a single SET statement without an accompanying BY statement. data work.q1_q2_combined;

• set work.q1_sales work.q2_sales;
• run;
• Execution Order & Behavior

SAS opens work.q1_sales and reads all observations sequentially from observation 1 to the End-of-File (EOF).

SAS closes work.q1_sales and immediately opens work.q2_sales , reading all observations sequentially until its EOF.

The total observation count of the output dataset equals the sum of the observations in work.q1_sales and work.q2_sales .

Variable Attributes & Missing Values During Concatenation

Union of Variables : The output dataset contains all unique variables present in any of the input datasets.

Missing Value Imputation : If a variable exists in q1_sales but not in q2_sales , observations originating from q2_sales will have a missing value ( . for numeric, blank " " for character) for that variable.

Attribute Determination : Variable attributes (type, length, label, format) are established during the compilation phase based on the dataset in which the variable first appears . /* Scenario: Length Conflict */

• data work.ds1;
• length City $10;
• City = "Dallas";
• run;

• data work.ds2;
• length City $20;
• City = "San Francisco";
• run;

• data work.combined;
• set work.ds1 work.ds2; /* City length is set to $10 based on ds1 */
• run;

Exam Trap: In work.combined , the length of City is determined by work.ds1 ($10). When SAS reads San Francisco from work.ds2 , the string is truncated to San Franci ! To prevent truncation, define the maximum required length using a LENGTH statement before the SET statement.

2. Type Conflict Errors

If a variable is defined as numeric in one dataset and character in another, SAS cannot complete compilation. /* Compilation Error Example */

• data work.ds_num; ID = 101; run;
• data work.ds_char; ID = "102"; run;

• data work.fail;
• set work.ds_num work.ds_char;
• run;

SAS Log Error: ERROR: Variable ID has been defined as both character and numeric.

Compilation stops immediately, and no output dataset is produced. You must explicitly convert variable types using INPUT() or PUT() functions prior to concatenating.

3. Interleaving Data Sets

Interleaving combines multiple SAS data sets into a single sorted dataset based on common key variables. Interleaving requires a SET statement listing all input datasets followed immediately by a BY statement. data work.interleaved_sales;

• set work.q1_sorted work.q2_sorted;
• by Region StoreID;
• run;
• Mandatory Requirement for Interleaving

Rule: All input data sets listed in the SET statement MUST be pre-sorted by the variables listed in the BY statement (or indexed accordingly). If any input dataset is not sorted, SAS halts execution with a runtime error.

How Interleaving Works in the PDV

SAS sets up input buffers for each dataset listed in the SET statement and compares the BY variable values of the current observation in each buffer.

SAS selects the observation with the lowest BY variable value (in ascending order), copies it into the PDV, and writes it to the output dataset.

The pointer for the dataset that supplied the observation advances to its next record.

Tie-Breaking Rule : If matching observations across datasets have identical BY values, SAS breaks the tie according to the order of datasets listed in the SET statement (e.g., q1_sorted first, then q2_sorted ).

4. Concatenation vs. Interleaving Comparison

• Feature Concatenation ( SET ds1 ds2; ) Interleaving ( SET ds1 ds2; BY var; )
• BY Statement None Required
• Sorting Requirement Datasets do NOT need to be sorted Datasets MUST be sorted by BY variable(s)
• Observation Order Sequential (all of ds1 , then all of ds2 ) Sorted by BY variable values across datasets
• PDV Reading Logic Reads single stream to EOF, then switches Compares buffer pointers across streams per row
• Tie-Breaker Logic Not applicable Input dataset order in SET statement
• Output Row Count Sum of all input rows Sum of all input rows
• 5. Best Practices & Code Examples
• Handling Length and Name Mismatches Correctly data work.all_customers;
• /* 1. Explicitly set attribute length to prevent truncation */
• length CustomerName $35 AccountID 8;

• /* 2. Concatenate datasets */
• set work.retail_cust work.online_cust;
• run;