Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Data Management

Sorting and the by Prefix

Sort data with sort and gsort, run commands per group with by and bysort, use _n and _N, find duplicates, create lags and running totals and rank within groups.

Real data has structure: students within schools, patients with several visits, firms observed every year. Stata handles group-wise work with the by prefix and two special variables, _n and _N. Once you master this lesson you can compute group totals, find duplicates, build lagged values and keep "the latest record per person" without a loop.

Sorting

STATA
sort id                      // ascending by id
sort region income           // by region, then by income within region
gsort -income                // descending (note the minus sign)
gsort region -income         // region ascending, income descending

sort is fast but only sorts ascending; gsort can sort descending. Neither is stable in a guaranteed way for ties unless you add , stable, so if the order of equal observations matters, add another key or use sort ..., stable. Missing numeric values sort last in ascending order (they are "larger than any number").

Sorting changes the row order but not the data. Because in 1/5 and _n depend on row order, always sort explicitly before using them.

The by prefix

by varlist: command repeats the command for each group. The data must be sorted by the same variables first, or you get error not sorted (r(5)).

STATA
sort foreign
by foreign: summarize price          // one summary per group

bysort foreign: summarize price      // sorts for you
bysort region year: egen total = sum(sales)
by foreign, sort: tabulate rep78     // the sort option sorts inside by

bysort (also bys) is sort plus by in one word, and it is the form you will use nearly every time. A by() option on commands such as egen or summarize works similarly but is not a prefix.

flowchart LR
    A["Sorted data"] --> B["Group 1<br/>(region North)"]
    A --> C["Group 2<br/>(region South)"]
    B --> D["run command"]
    C --> E["run command"]
    D --> F["results per group"]
    E --> F

bysort region: command runs the command once per group.

_n and _N

Inside a by group, two built-in variables tell you where you are:

  • _n is the position of the current observation (1, 2, 3, ...) within its group.
  • _N is the total number of observations in the group.

Without by, _n runs over the whole dataset and _N is the dataset size.

STATA
bysort id: generate visit = _n            // visit number per person
bysort id: generate nvisits = _N          // how many visits each person has
bysort id (date): generate first = (_n == 1)    // first visit by date
bysort id (date): generate last  = (_n == _N)   // last visit by date
bysort id (date): keep if _n == _N        // keep each person's latest record

The parentheses in bysort id (date): mean group by id but sort by date inside each group without making date part of the group definition. That is how you define "first" and "last".

Lags, leads and running totals

References like x[_n-1] refer to the previous observation:

STATA
bysort id (year): generate lag_income = income[_n-1]    // previous year's value
bysort id (year): generate lead_income = income[_n+1]   // next year's value
bysort id (year): generate change = income - income[_n-1]
bysort id (year): generate cum_sales = sum(sales)       // running total

For the first observation of each group, income[_n-1] is missing, which is what you want. Be careful: x[_n-1] is the previous row, not necessarily the previous year. If a year is missing for a person, the lag silently refers to an earlier year. For panel data, declare the structure with xtset id year and use time-series operators (L.income, D.income), which respect gaps (see the panel chapter).

Finding and handling duplicates

STATA
duplicates report id                 // how many observations per id
duplicates list id                   // show the rows
duplicates tag id, generate(dup)     // dup = number of other copies
duplicates drop id, force            // keep only the first copy (verify first!)
isid id                              // error unless id is unique
bysort id: assert _N == 1            // another uniqueness check

Use duplicates report first. Dropping duplicates is a decision: are they exact repeated rows (safe to drop) or two different people with the same id (a data problem that needs investigation)?

Group statistics without collapsing

STATA
bysort region: egen mean_inc = mean(income)
bysort region: egen n_obs = count(income)
bysort region: egen max_inc = max(income)
generate relative_inc = income / mean_inc

This adds the group statistic to every row, which is exactly what you need to compare each observation to its group. To make a smaller dataset of group statistics instead, use collapse (the descriptive chapter).

Rank within a group

STATA
bysort region (income): generate rank = _n          // 1 = lowest income
gsort region -income
by region: generate rank_desc = _n                  // 1 = highest income

gsort needs the by to be written out without bysort because bysort would re-sort ascending and undo the order. Check that the data are in the right order before using by on a gsort-ed dataset.

Stand-in: _n and _N in groups

The Python model sorts records by group and date, then shows _n, _N, a lag and "keep the last" exactly as bysort id (date): does.

Python Stand-in: _n, _N and lags inside groups

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.

Python Stand-in: duplicates report

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.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does bysort id (date): keep if _n == _N do?

    Inside each id sorted by date, _n == _N is the last (latest) observation.
  2. 2. Why must data be sorted before using by?

    Without sorting, Stata stops with 'not sorted'; bysort sorts for you.
  3. 3. What is income[_n-1] for the first observation of a group?

    There is no previous row in the group, so the lag is missing.
  4. 4. How do you sort in descending order?

    gsort accepts a minus sign for descending order; sort only goes ascending.

Summary

  • sort and gsort order the rows; missing numbers sort last in ascending order.
  • bysort group: command runs per group; parentheses bysort id (date): set the order inside groups.
  • _n is the position in the group and _N the group size; they give first, last and counts.
  • x[_n-1] is the previous row; for panel data prefer xtset and L.x.
  • duplicates report, tag and drop manage duplicates; investigate before dropping.

Next: Merging and Appending Datasets