6.2 Sorting Data Sets with PROC SORT

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

1. 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.
2. Default Sort Order: SAS sorts observations in ascending order (lowest value to highest value) by default.
3. 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.

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:

$\text{Blanks } < \text{ Special Characters } < \text{ Digits (0-9) } < \text{ Uppercase Letters (A-Z) } < \text{ 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.