Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Procedures: PROC

PROC SORT and Removing Duplicates

Sort SAS data sets with PROC SORT, use DESCENDING, OUT=, NODUPKEY and NODUP, understand stable ordering and why BY-group processing needs sorted data, with a Python stand-in.

Many SAS features, such as BY statements in DATA steps, MERGE, FIRST. and LAST. flags, and the BY statement of most procedures, need the data sorted by the same variables. PROC SORT is the procedure that puts the rows in order. It is also the standard way to remove duplicate rows, which makes it one of the most used steps in real programs.

SAS cannot run on this site. The sas blocks are read-only; the TryIts are Python stand-ins that model the same behavior on small lists.

Basic sorting

SAS
proc sort data=work.sales out=work.sales_sorted;
  by region descending amount;
run;
  • by lists the sort keys. The first key is the major one; later keys break ties.
  • descending applies only to the variable that follows it. Here region sorts A to Z and amount sorts high to low inside each region.
  • out= writes the result to a new data set. Without out=, PROC SORT replaces the input data set with the sorted version. That is efficient, but you cannot undo it, so beginners should almost always write out=.

Character variables sort by the computer's collating sequence: uppercase letters come before lowercase ones in ASCII, so "Zoe" sorts before "adam". If your data mixes cases, standardize first with upcase() in a DATA step, or use the option sortseq=linguistic (with strength=primary to ignore case) where it is available.

Missing values count as the smallest value: a numeric . sorts first in ascending order and last in descending order.

What the log tells you

After a sort, the log reports the number of observations read and written. If you used out=, the original data set is untouched. When the log says The data set WORK.SALES has 0 observations, check your where statement before trusting anything downstream.

You can filter and trim while sorting, which saves a separate step:

SAS
proc sort data=work.sales (keep=region amount year where=(year=2025))
          out=work.sales25;
  by region;
run;

Stand-in: sorting with several keys

The stand-in uses Python's stable sort. Sort by the minor key first, then by the major key, because Python's sort keeps equal elements in their original order.

Python Stand-in: BY region DESCENDING amount

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the two East rows with amount 450: Lou stays before Ned. SAS also keeps ties in input order by default (EQUALS is the default), so results are repeatable. The option noequals allows SAS to reorder ties for speed, which you rarely want.

Removing duplicates: NODUPKEY and NODUP

Two options remove duplicates but they mean different things:

SAS
/* keep the first row for each customer_id */
proc sort data=work.orders out=work.first_orders nodupkey;
  by customer_id;
run;

/* remove rows identical in every variable that are next to each other */
proc sort data=work.orders out=work.clean nodup;
  by customer_id;
run;
  • nodupkey keeps only the first observation for each unique combination of the BY variables. Everything else in the rows may differ.
  • nodup (also written noduprecs) removes an observation only if it is an exact copy of the previous observation, comparing all variables. Duplicates that are not adjacent after sorting survive, so nodup is less thorough than it sounds.

To keep a record of what was dropped, send duplicates to a separate data set:

SAS
proc sort data=work.orders out=work.unique nodupkey dupout=work.dupes;
  by customer_id order_date;
run;

dupout= collects the removed rows, which is excellent for auditing.

"Keep the latest order per customer" needs a sort order you control: sort by customer_id descending order_date, then apply nodupkey by customer_id. The first row kept is the one with the newest date.

Stand-in: NODUPKEY and DUPOUT

Python Stand-in: keep the latest row per customer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Customer 7 keeps the March order and customer 3 keeps the February order, because the newest date is sorted first before duplicates are discarded.

Sorting is expensive

Sorting a huge table takes time and disk space. Ways to avoid unnecessary sorts:

  • Check whether the data is already sorted. PROC CONTENTS reports Sorted: YES and the sort keys when the sort order is recorded on the data set.
  • Use PROC SQL with ORDER BY or a hash object when you only need a join (see PROC SQL: SELECT Basics).
  • Use CLASS instead of BY in procedures like PROC MEANS, which does not require sorted input (see PROC MEANS and PROC SUMMARY).
  • Sort once, then reuse the sorted data set for several BY steps.

If you apply a BY statement to unsorted data you get the error BY variables are not properly sorted on data set ..., and the step stops. The cure is to run PROC SORT first, not to suppress the error.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens if you run PROC SORT without OUT=?

    Without out=, PROC SORT overwrites the input data set with the sorted rows.
  2. 2. Which option keeps only the first observation for each combination of the BY variables?

    NODUPKEY compares only the BY variables and drops later rows with the same key.
  3. 3. In 'by region descending amount', which variable is sorted from high to low?

    DESCENDING applies only to the variable written right after it.
  4. 4. How does NODUP differ from NODUPKEY?

    NODUP compares complete observations and only against the adjacent previous one; NODUPKEY compares just the BY variables.

Summary

  • PROC SORT orders rows by the BY variables; use out= so the original stays intact.
  • descending precedes the variable it affects; ties keep their input order by default.
  • nodupkey keeps one row per key, nodup removes only identical adjacent copies, dupout= saves the removed rows.
  • Sort order decides which duplicate survives: control it with the sort keys.
  • Many tools need sorted data (BY, MERGE); others (CLASS, SQL) do not.

Next: PROC FREQ and Tables