Written and maintained by CASRAI Editorial Board
Last updated
Stata’s collapse command replaces the current dataset in memory with one row per group, computed from one or more statistics you specify: collapse (stat) varlist, by(groupvars). Run collapse (mean) score, by(class) on a student-level dataset and the six rows of raw scores become three rows of class means — and the six rows of raw data are gone from memory the moment the command finishes.
That destructiveness is the single most important thing to understand about collapse before using it on a dataset you have not already saved. This page covers the syntax for multiple statistics and multiple by-groups, what exactly collapse destroys and how to work around it with preserve/restore, and a frequency-weight behaviour that surprises people who assume collapse ignores weights the way a simple egen call would.
A worked example: student scores collapsed to class means
Start with a small dataset of six students across three classes — the kind of row-level data a gradebook export or a survey-response file typically arrives in:
. list
+--------------------------+
| student class score |
|--------------------------|
1. | 1 A 78 |
2. | 2 A 85 |
3. | 3 B 91 |
4. | 4 B 88 |
5. | 5 C 73 |
6. | 6 C 80 |
+--------------------------+Collapsing to the mean score per class:
. collapse (mean) score, by(class)
. list
+---------------+
| class score |
|---------------|
1. | A 81.5 |
2. | B 89.5 |
3. | C 76.5 |
+---------------+The dataset now has 3 observations, not 6. The student variable is gone entirely — it was neither a by-group variable nor a variable named in a statistic, so collapse dropped it. This is the behaviour to internalise before running the command on anything you have not already saved: collapse is not a view or a summary table layered on top of your data, it replaces the data.
Syntax: multiple statistics, multiple variables, multiple by-groups
The general form is:
collapse (stat1) varlist1 (stat2) varlist2 ..., by(groupvar1 groupvar2 ...)Each parenthesised statistic applies to the variable(s) listed immediately after it, until the next parenthesised statistic or the end of the varlist. Omitting the parenthesis defaults to mean. The by() option is optional — without it, collapse produces one row summarising the entire dataset, which is a fast way to get a single-row table of statistics without writing a separate summarize call for each variable.
The commonly used statistic keywords: mean, median, sum, rawsum, count, sd, semean, max, min, iqr, first, firstnm, last, lastnm, and percentiles p1 through p99 (e.g. p50 for the median, equivalent to the median keyword). first/last take the first or last observation’s value within each group in current sort order, which is useful for carrying along a group-level identifier or label that does not vary within the group — firstnm/lastnm do the same but skip missing values.
To compute several statistics on the same variable in one pass, and give each result a distinct name (required, since a variable cannot appear twice under the same name), name each result explicitly with newname=oldname:
. collapse (mean) avg_score=score (sd) sd_score=score (count) n_score=score ///
(min) min_score=score (max) max_score=score, by(class)
. list
+---------------------------------------------------------+
| class avg_score sd_score n_score min_score max_score |
|---------------------------------------------------------|
1. | A 81.5 4.949747 2 78 85 |
2. | B 89.5 2.121320 2 88 91 |
3. | C 76.5 4.949747 2 73 80 |
+---------------------------------------------------------+Multiple by-group variables work the same way — by(class year) produces one row per unique combination of class and year that actually appears in the data, not a full cross-product of every possible combination (there is no row for a class/year pair with zero observations).
What collapse destroys, and how to preserve/restore around it
Because collapse overwrites the dataset currently in memory, the standard safeguard is Stata’s preserve/restore pair, which snapshots the current dataset and lets you return to it:
preserve
collapse (mean) score, by(class)
list
restore
* the original 6-observation, student-level dataset is back in memorypreserve takes a snapshot before the collapse; restore discards the collapsed dataset and brings the snapshot back. This is the right pattern when you need the collapsed summary for one step of a do-file (for example, to merge class-level means back onto the student-level file, or to export a summary table) but still need the row-level data afterward. Nesting preserve calls is not supported — only one snapshot exists at a time — so a do-file that needs to collapse and restore more than once in sequence should pair each preserve with its own restore before the next one runs.
If you need both the row-level data and the collapsed summary as separate objects afterward (rather than just returning to where you started), save a copy to disk or to a tempfile before collapsing instead of relying on preserve/restore:
tempfile studentlevel
save `studentlevel'
collapse (mean) score, by(class)
* work with the class-level summary here
* use `studentlevel' later to get back the row-level fileEither way, the underlying rule is the same: never run collapse as the first destructive step against a dataset that only exists in memory and has not been saved anywhere. If the do-file errors out partway through, or you simply want to try a different by() grouping, the row-level data is unrecoverable without a save point.
The frequency-weight gotcha
collapse accepts weights, and fweight (frequency weight) is where the surprises happen. A frequency weight tells Stata that each observation actually represents w identical observations — commonly the case when a dataset has already been aggregated once and carries a count column. Applying [fweight=n] to a collapse call changes what the statistics mean:
. list
+-------------------+
| region rate n |
|-------------------|
1. | N 0.62 40 |
2. | N 0.71 10 |
3. | S 0.55 30 |
4. | S 0.68 20 |
+-------------------+
. collapse (mean) rate [fweight=n], by(region)
. list
+---------------+
| region rate |
|---------------|
1. | N 0.638 |
2. | S 0.602 |
+---------------+Without the fweight, the region-N mean of rate would be the unweighted average of 0.62 and 0.71 — 0.665. With [fweight=n], each row is treated as if it were repeated n times before averaging, which correctly weights the more common value more heavily: (0.62×40 + 0.71×10) / 50 = 0.638. The same logic applies to (count) and (sum): with an fweight attached, (count) returns the weighted total (the sum of n within the group) rather than the literal number of rows in the group — a genuine source of confusion when the unweighted row count and the weighted total look similar in size and nobody checks which one a downstream calculation actually needed.
The practical rule: if a dataset already carries a count or frequency column from a prior aggregation step, decide explicitly whether to attach it as an fweight before collapsing again, and check the resulting count/sum columns against what you expect — don’t assume (count) means “number of rows in the group” once a weight is in play.
collapse vs. contract vs. egen
Three commands get reached for interchangeably and shouldn’t be:
collapse— replaces the dataset with one row per group, computing arbitrary statistics. Use it when the summary itself is the thing you need going forward.contract— also replaces the dataset with one row per unique combination of the variables listed, but only produces a frequency count (_freqby default), not arbitrary statistics. Use it when the question is purely “how many observations have this combination of values,” not “what is the mean/sum/sd within this group.”egenwithby:or agroup()function — adds a new variable to the existing dataset carrying a group-level statistic on every row of that group, without collapsing anything. Use it when you need the row-level data to stay row-level and just want a group summary attached to each row (for example, to compute each student’s deviation from their class mean).
If the row-level detail still needs to exist afterward on the same dataset, the answer is egen, not collapse followed by a merge back — the merge-back pattern works but is more steps than necessary for what egen ..., by(group) does directly.
Frequently asked questions
Does collapse sort the data?
Yes — the resulting dataset is sorted by the by-group variables (or left in a single row if no by() is given). Any prior sort order on the row-level data is irrelevant once it has been collapsed away.
Can I collapse without a by() option?
Yes. collapse (mean) score with no by() produces a single-row dataset with the overall mean — useful when a do-file needs one summary statistic as a scalar-like value further downstream, without a separate summarize call.
What happens to string variables and labels?
A string variable not named in a statistic and not a by-group variable is dropped, the same as any other unmentioned variable. Value labels on a by-group variable carry through to the collapsed dataset; value labels on a variable being summarised do not carry through to the new statistic variable, since the underlying value is no longer the original coded category.
Is there a way to preview the collapse without losing my data?
Wrap it in preserve / list / restore, as shown above — that is the standard pattern precisely because collapse has no non-destructive “preview” mode of its own.
Related reading
For choosing Stata over the alternatives in the first place, see CASRAI’s SPSS vs. Stata comparison and R vs. Stata comparison. If the dataset you are collapsing needs restructuring before or after aggregation, see tidy data rules for structuring research data. For the broader landscape of tools covered here, see the Research Tools & Software hub.
