6.3 Restructuring Data Sets with PROC TRANSPOSE

6.3 Restructuring Data Sets with PROC TRANSPOSE

Data structures in relational databases and transactional systems are often organized in narrow (long) format—where each observation represents a single transaction or time point, resulting in many rows per subject. Conversely, statistical procedures, reporting layouts, and predictive models frequently require data in wide format—where each subject occupies a single row with multiple columns for periodic metrics.

PROC TRANSPOSE is the primary SAS procedure used to pivot data structures from long-to-wide or wide-to-long. Mastering its statements, default behaviors, and naming options is a major component of the SAS Base Programming exam.

---

1. Core Syntax & Basic Mechanics

PROC TRANSPOSE creates an output dataset by turning rows from the input dataset into columns, or columns into rows.

PROC TRANSPOSE DATA=input_dataset OUT=output_dataset <options>;
    BY <DESCENDING> variable-1 <...variable-n>;
    ID id_variable;
    VAR transpose_variables;
    COPY copy_variables;
RUN;

Primary Statement Functions

• BY: Transposes data separately within each BY group. Input dataset must be sorted by BY variables.
• VAR: Specifies which variables to transpose (their values become rows of transposed data).
• ID: Specifies a variable whose formatted values will serve as the column names for the new transposed variables.
• COPY: Copies specified variables directly from the input dataset to the output dataset without transposing them.

---

2. Default Behaviors & Naming Conventions

Understanding how PROC TRANSPOSE automatically names variables in the output dataset is essential when writing predictable pipelines.

Default Column Naming without ID Statement

If no ID statement is supplied, PROC TRANSPOSE assigns generic column names to the transposed variables: COL1, COL2, COL3, ..., COLn.

Tracking Source Variable Names (_NAME_ and _LABEL_)

By default, PROC TRANSPOSE creates two automatic character variables in the output dataset:

• _NAME_: Stores the variable name of the transposed input variable.
• _LABEL_: Stores the label of the transposed input variable (if a label exists).

/* Renaming default automatic variables using procedure options */
proc transpose data=work.wide_sales 
               out=work.long_sales 
               prefix=Sales_
               name=Source_Metric
               label=Metric_Label;
    by CustomerID;
    var Qtr1 Qtr2 Qtr3 Qtr4;
run;

• prefix=Sales_: Transforms generic column names COL1-COL4 into Sales_1, Sales_2, Sales_3, Sales_4 (or prefixes ID variable values).
• name=Source_Metric: Renames _NAME_ column to Source_Metric.
• label=Metric_Label: Renames _LABEL_ column to Metric_Label.

---

3. Omission Behaviors (What Happens When Statements Are Left Out?)

The SAS Base exam frequently tests what occurs when specific statements are omitted from PROC TRANSPOSE:

/* Example: Demonstrating Omitted VAR Statement */
/* Input work.employee: ID (num), Age (num), Salary (num), Dept (char) */

proc transpose data=work.employee out=work.emp_trans;
    by ID;
run;
/* Transposed variables: Age and Salary (numeric). Dept (character) is ignored! */

---

4. Narrow-to-Wide vs. Wide-to-Narrow Transformations

Scenario A: Restructuring Narrow Data to Wide (Using BY and ID)

Suppose you have monthly transactional sales data (Narrow) and want a single row per customer with columns for each month (Wide).

/* Narrow Input: CustomerID, Month, Amount */
/* 101, Jan, 500 */
/* 101, Feb, 600 */

proc sort data=work.narrow_sales;
    by CustomerID Month;
run;

proc transpose data=work.narrow_sales out=work.wide_sales;
    by CustomerID;
    id Month;
    var Amount;
run;
/* Output columns: CustomerID, _NAME_ ('Amount'), Jan, Feb */

Scenario B: Restructuring Wide Data to Narrow (Using BY and VAR)

Suppose you have quarterly revenue columns (Wide) and want to transpose them into rows (Narrow).

/* Wide Input: StoreID, Q1, Q2, Q3, Q4 */
proc transpose data=work.wide_store out=work.narrow_store(rename=(col1=Revenue))
               name=Quarter;
    by StoreID;
    var Q1 Q2 Q3 Q4;
run;
/* Output columns: StoreID, Quarter ('Q1'..'Q4'), Revenue (transposed values) */

Exam Trap: There is no VALUE= option on PROC TRANSPOSE. The column that holds the transposed values is named COL1 by default. Rename it with a RENAME= data set option on OUT=, or change its stem with PREFIX= (prefix=Revenue yields Revenue1). Only NAME= and LABEL= rename the automatic _NAME_ and _LABEL_ columns.

---

5. Duplicate ID Values & The LET Option

When using an ID statement, the values of the ID variable must be unique within each BY group. If duplicate ID values occur within the same BY group, PROC TRANSPOSE halts execution with an error: ERROR: The ID value "..." occurs twice in the same BY group.

To override this error and force PROC TRANSPOSE to keep the value from the last observation encountered for duplicate ID values, specify the LET option in the PROC TRANSPOSE statement.

proc transpose data=work.survey_data out=work.survey_wide let;
    by RespondentID;
    id QuestionCode;
    var AnswerScore;
run;

---

6. Common Pitfalls & Exam Watchouts

• Character Variables Disappearing: Forgetting to list character variables in a VAR statement when transposing character data causes SAS to ignore them completely.
• Unsorted BY Group Error: Failing to sort input data by BY variables prior to running PROC TRANSPOSE triggers a fatal ERROR: Data set WORK.DATA is not sorted...
• Invalid Column Names from ID: If values of an ID variable start with numbers or contain spaces/special characters, SAS converts them to valid SAS variable names (e.g., '1st Quarter' becomes _1st_Quarter) unless system option VALIDVARNAME=ANY is set.