Study guide
6.3 Restructuring Data Sets with PROC TRANSPOSE
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
6.3 Restructuring Data Sets with PROC TRANSPOSE
Key Takeaways Explain This Topic AI
PROC TRANSPOSE reshapes a SAS dataset by rotating rows into columns (wide-to-narrow) or columns into rows (narrow-to-wide).
The VAR statement specifies the numeric or character variables to transpose; if omitted, PROC TRANSPOSE transposes all numeric variables not named in BY or ID statements.
The BY statement transposes data separately within each BY group, requiring the input dataset to be sorted by the BY variables beforehand.
The ID statement specifies a variable whose formatted values become the names of the newly transposed columns in the output dataset.
The PREFIX= option prepends a text string to default variable names (COL1, COL2), while NAME= renames the default NAME column that stores transposed variable names.
Last updated: August 2026
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 :
Omitted Statement Default SAS Execution Behavior
Omitted VAR Statement PROC TRANSPOSE transposes all numeric variables in the input dataset that are not listed in a BY or ID statement. Character variables are completely ignored!
Omitted ID Statement Output columns are assigned default names COL1 , COL2 , ..., COLn .
Omitted BY Statement The entire dataset is transposed as a single group. The output dataset will contain one column for every observation in the input dataset. /* 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.