6.2 Sorting Data Sets with PROC SORT

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.2 Sorting Data Sets with PROC SORT

Key Takeaways Explain This Topic AI

PROC SORT rearranges observations in a SAS dataset based on one or more BY variables, operating in ascending order by default unless DESCENDING is specified before each applicable BY variable.

The OUT= option directs sorted output to a new dataset, preserving the original input dataset in its un-sorted order; omitting OUT= overwrites the input dataset in place.

The NODUPKEY option eliminates observations with duplicate values of the specified BY variables, keeping only the first observation per BY group.

The NODUP (or NODUPLICATES) option removes duplicate observations only when ALL variables in the dataset are identical across adjacent rows.

The DUPOUT= option creates an audit dataset containing all duplicate rows removed by NODUPKEY or NODUP, facilitating data quality verification.

Last updated: August 2026

6.2 Sorting Data Sets with PROC SORT

Sorting datasets is a foundational data manipulation step in SAS. Many SAS procedures and DATA step operations—most notably BY -group processing, MERGE statements, FIRST.variable / LAST.variable flags, PROC MEANS , and PROC TRANSPOSE —require input data to be sorted by specific keys beforehand.

The PROC SORT procedure orders observations by one or more character or numeric variables and provides powerful options for removing duplicate observations. Mastering PROC SORT syntax and nuances is essential for success on the SAS Base Programming exam.

1. Basic Syntax & Operational Rules

PROC SORT reads an input SAS dataset, sorts its rows according to variables named in a mandatory BY statement, and writes the sorted output back to the input dataset or to a specified target dataset. PROC SORT DATA=input_dataset <OUT=output_dataset> <options>;

BY <DESCENDING> variable-1 <...<DESCENDING> variable-n>;
RUN;
Critical Rules of Operation

In-Place Sorting vs. OUT= Option : If the OUT= option is omitted , SAS replaces the original input dataset with the sorted dataset upon execution. To preserve the original raw file, always supply OUT=libref.output_name .

Default Sort Order : SAS sorts observations in ascending order (lowest value to highest value) by default.

Placement of DESCENDING : The DESCENDING keyword applies only to the variable immediately following it . If sorting by multiple variables in descending order, DESCENDING must precede each variable explicitly. /* Correct multi-variable sorting with mixed ascending/descending order */

proc sort data=work.employee_payroll out=work.payroll_sorted;
by Department descending Salary Employee_ID;
run;
/* Result: Sorted by Department (Ascending), then Salary (Descending), then Employee_ID (Ascending) */
2. Deduplication Options: NODUPKEY vs NODUP

One of the most heavily tested topics on the SAS Base exam is the distinction between the NODUPKEY and NODUP (or NODUPLICATES ) options in PROC SORT .

Option Comparison Scope What gets removed? Typical Use Case

NODUPKEY Compares only the BY variables specified in the BY statement. Deletes subsequent observations that share identical BY variable values with an earlier row, keeping only the first observation per key. Extracting unique customer IDs, creating dimension lookup tables.

NODUP / NODUPLICATES Compares ALL variables in the dataset across adjacent rows. Deletes an observation only if every single variable value matches the preceding row exactly. Cleaning completely duplicated rows logged by system glitches.

Exam Tip : NODUPKEY looks only at the key variables listed in the BY statement and ignores differences in non-BY variables. NODUP checks all columns in the dataset! /* Sample Raw Data: work.transactions */

/* ID Date Amount */
/* 101 01JAN2026 150 */
/* 101 05JAN2026 200 */ <-- Duplicate ID, different Date/Amount
/* 102 10JAN2026 300 */

/* Example 1: NODUPKEY */
proc sort data=work.transactions out=work.unique_ids nodupkey;
by ID;
run;
/* Output work.unique_ids contains 2 rows (IDs 101 and 102). Row 2 (05JAN2026) is deleted. */

/* Example 2: NODUP */
proc sort data=work.transactions out=work.unique_records nodup;
by ID;
run;
/* Output work.unique_records contains all 3 rows because Date and Amount differ between rows 1 and 2. */
3. Auditing Removed Rows with DUPOUT=

When identifying or removing duplicates, data governance standards often require inspecting the deleted records. The DUPOUT= option specifies an output dataset into which SAS writes all duplicate observations eliminated by NODUPKEY or NODUP . proc sort data=work.orders

out=work.orders_clean
nodupkey
dupout=work.orders_duplicates;
by CustomerID OrderDate;
run;

work.orders_clean : Contains unique records (the first record for each CustomerID OrderDate key).

work.orders_duplicates : Contains all dropped secondary/tertiary observations for audit review.

4. Preserving Observation Order: EQUALS vs NOEQUALS

When sorting data with duplicate BY values, SAS must decide whether to preserve the original relative chronological order of those duplicate rows.

EQUALS ( Default ): Ensures that observations with duplicate BY values maintain their original relative order in the output dataset.

NOEQUALS : Allows SAS to discard original relative order to optimize sort speed and CPU memory allocation.

5. Sorting Collating Sequences & Missing Values

Understanding how SAS orders special characters, missing values, numbers, and text is critical when predicting PROC SORT results.

Handling Missing Values in PROC SORT

In SAS, missing values are treated as the smallest possible values :

Numeric Missing Values : . , .A , .B , ..., .Z sort before all negative and positive numbers ( . < .A < .Z < -999 < 0 < 100 ).

Character Missing Values : Blank strings ' ' sort before all printable text characters.

Character Collating Sequence (ASCII Standard)

By default on Windows and Unix/Linux platforms, SAS uses the ASCII collating sequence: Blanks < Special Characters < Digits (0-9) < Uppercase Letters (A-Z) < Lowercase Letters (a-z) \text{Blanks } < \text{ Special Characters } < \text{ Digits (0-9) } < \text{ Uppercase Letters (A-Z) } < \text{ Lowercase Letters (a-z)} Blanks < Special Characters < Digits (0-9) < Uppercase Letters (A-Z) < Lowercase Letters (a-z) /* Example demonstrating ASCII sorting precedence */

/* Input values: 'apple', 'Apple', '100', ' 50', '$50' */
/* Sorted order: ' 50' (blank lead), '$50' (special), '100' (digit), 'Apple' (upper), 'apple' (lower) */

Note : To enforce case-insensitive character sorting (where 'apple' and 'Apple' sort together), programmers can specify the SORTSEQ=LINGUISTIC(STRENGTH=PRIMARY) option in the PROC SORT statement.

6. Common Pitfalls & Exam Watchouts

Syntax Error with DESCENDING : Writing by Region Sales descending; attaches DESCENDING to a non-existent variable or causes a syntax error. DESCENDING must precede Sales ( by Region descending Sales; ).

Accidental Overwriting : Omitting OUT= replaces your primary input dataset. If deduplication ( NODUPKEY ) is run without OUT= , deleted records are permanently lost from the source dataset.

Unsorted BY Group Error : Attempting a DATA step MERGE or SET ... BY statement without running PROC SORT first produces ERROR: Data set WORK.DATASET is not sorted in ascending order.