Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 37 ยท Data Manipulation

Sorting and BY-Group Processing: PROC SORT, FIRST. and LAST.

Sort data with PROC SORT (BY, DESCENDING, OUT=, NODUPKEY), then process groups in a DATA step with the BY statement and the FIRST. and LAST. automatic variables.

Sorting is the foundation of group-wise work in SAS. Merging, finding the first or last record per customer, totals by region, removing duplicates: all need data in order. This lesson covers PROC SORT and the BY-group processing that lets a DATA step recognize group boundaries.

PROC SORT

SAS
proc sort data=sashelp.class out=class_sorted;
   by sex descending age;
run;
  • data= names the input and out= names a new output. Without out= the input is overwritten with the sorted version, which is risky for permanent data.
  • by lists the keys. The first variable is the major key; the sort is ascending unless you write descending before a variable. The keyword applies only to the variable that follows it.
  • Character values sort in the computer's collating order, in which uppercase letters come before lowercase, unless you set the sort option sortseq or use a locale-aware collating sequence.
  • Missing values sort first in ascending order, since they count as smallest.

Sorting is stable by default when you use equals (the default): rows with equal keys keep their original relative order.

Removing duplicates

Option Effect
nodupkey Keeps only the first row for each unique combination of the by variables
nodup Removes a row only if it is identical to the previous row in all variables
dupout=ds Writes the removed rows into a data set (with nodupkey)
SAS
proc sort data=orders out=first_order nodupkey dupout=repeat_orders;
   by customer_id;
run;

If you want the most recent order per customer, sort by customer_id and descending order_date first, then use nodupkey: the first row per customer is the latest. nodupkey keeps the first row it sees.

The BY statement in a DATA step

When the input is sorted, you can add by to a DATA step that has a set statement. SAS then creates two automatic variables for each BY variable:

  • FIRST.var is 1 for the first observation of a group, otherwise 0.
  • LAST.var is 1 for the last observation of a group, otherwise 0.

They are in the PDV but not written to the output. For this small table sorted by dept:

dept name
A Ana
A Ben
A Cara
B Dev
B Eli

first.dept is 1 on Ana and Dev; last.dept is 1 on Cara and Eli.

SAS
proc sort data=sashelp.class out=by_sex;
   by sex;
run;

data first_in_group;
   set by_sex;
   by sex;
   if first.sex;           /* one row per sex: the first one */
run;

Changing to if last.sex; gives the last row of each group. With more than one by variable, first. and last. work at each level: first.region is true whenever the region changes, and first.city is true whenever the city or any variable before it changes.

If the data is not sorted by the BY variables, SAS stops with ERROR: BY variables are not properly sorted on data set .... Data can also be grouped but not sorted: use notsorted on the by statement (by sex notsorted;) when equal values are adjacent.

Group totals with FIRST. and LAST.

Combining BY processing with the sum statement (explained in RETAIN and Running Totals) produces totals per group:

SAS
data dept_totals;
   set sales;
   by dept;
   if first.dept then dept_total = 0;
   dept_total + amount;            /* sum statement: retained */
   if last.dept then output;       /* one row per dept */
run;

The pattern is: reset at first., accumulate every row, write at last.. You will use it constantly.

Counting within a group

SAS
data numbered;
   set by_sex;
   by sex;
   if first.sex then seq = 0;
   seq + 1;
run;

Each row gets its position within the group (seq), starting at 1 again for each group. This is a standard way to assign sequence numbers.

PROC SORT versus PROC SQL ORDER BY

proc sql can sort with order by, and it can group without sorting first. When you will go on to a DATA step with by, PROC SORT is the natural tool. For big data, check whether data is already sorted: SAS records the sort order in the descriptor, and proc sort on already sorted data does little work. proc contents shows the Sorted flag.

A group processor

This Python stand-in (not SAS) computes FIRST. and LAST. flags over sorted rows and builds group totals with the reset-accumulate-write pattern.

Python Python stand-in: FIRST. and LAST. flags and group totals

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.

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

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

    Without out= SAS overwrites the input. Use out= to keep the original.
  2. 2. What does NODUPKEY keep for each BY group?

    It keeps the first observation for each unique key and removes the rest.
  3. 3. For the group A with three rows sorted by dept, on which row is last.dept equal to 1?

    last.dept is 1 on the final observation of each group.
  4. 4. What error do you get if the BY variables are not sorted?

    SAS stops with 'BY variables are not properly sorted'. Sort first or use notsorted if values are grouped.

Summary

  • proc sort data= out=; by ...; sorts; descending applies to the next variable; use out= to protect the input.
  • nodupkey keeps the first row per key; nodup removes only fully identical neighbors.
  • A by statement in a DATA step creates first.var and last.var.
  • The reset-at-first, accumulate, output-at-last pattern builds group totals and counters.

Next: RETAIN and Running Totals