3.2 Combining Data Sets: Concatenating & Interleaving

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

1. SAS opens work.q1_sales and reads all observations sequentially from observation 1 to the End-of-File (EOF).
2. SAS closes work.q1_sales and immediately opens work.q2_sales, reading all observations sequentially until its EOF.
3. 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

1. 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.
2. 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.
3. The pointer for the dataset that supplied the observation advances to its next record.
4. 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

---

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;