• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Analysis Factor

The Analysis Factor

Statistical Consulting, Resources, and Statistics Workshops for Researchers

  • Home
  • Our Programs
    • Membership
    • Online Workshops
    • Free Webinars
    • Consulting Services
  • About
    • Our Team
    • Our Core Values
    • Our Privacy Policy
    • Employment
    • Collaborate with Us
  • Statistical Resources
  • Contact
  • Blog
  • Login

Using the Collapse Command in Stata

by Jeff Meyer 12 Comments

by Jeff Meyer

Have you ever worked with a data set that had so many observations and/or variables that you couldn’t see the forest for the trees? You would like to extract some simple information but you can’t quite figure out how to do it.

Get to know Stata’s collapse command–it’s your new friend. Collapse allows you to convert your current data set to a much smaller data set of means, medians, maximums, minimums, count or percentiles (your choice of which percentile).

Let’s take a look at an example. I’m currently looking at a longitudinal data set filled with economic data on all 67 counties in Alabama. The time frame is in decades, from 1960 to 2000. Five time periods by 67 counties give me a total of 335 observations.

What if I wanted to see some trend information, such as the total population and jobs per decade for all of Alabama? I just want a simple table to see my results as well as a graph. I want results that I can copy and paste into a Word document.

Here’s my code:

preserve
collapse (sum) Pop Jobs, by(year)
graph twoway (line Pop year) (line Jobs year), ylabel(, angle(horizontal))
list

And here is my output:
image002
image004

By starting my code with the preserve command it brings my data set back to its original state after providing me with the results I want.

What if I want to look at variables that are in percentages, such as percent of college graduates, mobility and labor force participation rate (lfp)? In this case I don’t want to sum the values because they are in percent.

Calculating the mean would give equal weighting to all counties regardless of size.

Fortunately Stata gives you a very simple way to weight your data based on frequency. You have to determine which variable to use. In this situation I will use the population variable.

Here’s my coding and results:

Preserve
collapse (mean) lfp College Mobil [fw=Pop], by(year)
graph twoway (line lfp year) (line College year) (line Mobil year), ylabel(, angle(horizontal))
list

image006
image008
It’s as easy as that. This is one of the five tips and tricks I’ll be discussing during the free Stata webinar on Wednesday, July 29th.

Jeff Meyer is a statistical consultant with The Analysis Factor, a stats mentor for Statistically Speaking membership, and a workshop instructor. Read more about Jeff here.

Bookmark and Share

Getting Started with Stata
Jeff introduces you to the consistent structure that Stata uses to run every type of statistical analysis.

Tagged With: collapse, graph, preserve, Stata

Related Posts

  • Stata Loops and Macros for Large Data Sets: Quickly Finding Needles in the Hay Stack
  • Using Stored Calculations in Stata to Center Predictors: an Example
  • Loops in Stata: Making coding easy
  • Statistical Software Access From Home

Reader Interactions

Comments

  1. Biswas says

    August 12, 2020 at 4:50 am

    Hello,

    I am using a logit model where the dependent variable is risk of infection (high/low) and independent variables are gender, age, income, and pathogen load. the pathogen load data is not for household level, but represents the pathogen load in waterways for a cluster of households (10-20). so the independent variable is repeated for multiple households. Is there any way to account for low variability in such a variable?

    Reply
  2. Zinhle says

    November 25, 2019 at 10:35 am

    Hello
    I have 11 variables which are yes/no answers. I have coded yes = 1 and no= 0. Which stat can I use to retain the 1 and 0 outputs? If I collapse (mean) I get decimals.

    Thanks

    Reply
    • Jeff Meyer says

      December 2, 2019 at 2:18 pm

      Hi,

      We collapse our data using the “by” statement. As a result, the variables that are being collapsed are summarized in some manner. This is due to reducing the number of observations for the variable in the “by” statement to just one observation. Thus, it’s not possible to keep your 0’s and 1’s as separate observations. The collapse command isn’t the command you want to use.

      Jeff

      Reply
  3. Ismail Muhammad says

    August 2, 2019 at 6:15 pm

    I have dataset in stata and I would like to perform clustered bar graph with error bars. Please help me out

    Reply
  4. Diana says

    March 23, 2018 at 5:43 am

    Thanks for detailed explanation! This helped me a lot.

    Reply
    • Jeff Meyer says

      March 26, 2018 at 11:03 am

      Excellent, thanks for the feedback.

      Jeff

      Reply
  5. maria says

    March 11, 2018 at 9:56 pm

    Hi! I want to collapse my data by three variables, all of them have many observations repeated, but I’m having problems with the option by (var1 var2 var3) because after the moment I collapse there’s no one just 1 of the observations repeated, there’re a lot
    Thanks

    Reply
    • Jeff Meyer says

      March 12, 2018 at 8:41 pm

      Hi Maria,

      If you collapsing by 3 categorical variables the number of responses you get will be the number of categories in var1 times the number of categories in var2 times the number of categories in var3. That is the number of unique groups. For each of the unique groups you will get the statistical result that you specify after the collapse command.

      Jeff

      Reply
  6. Dylan says

    February 9, 2018 at 4:48 pm

    How do I get back to my original data? I used the preserve command and my data is still intact, but I can’t seem to run code on other variables after collapsing.

    Reply
    • Jeff Meyer says

      February 14, 2018 at 6:14 pm

      Hi, in my do-file I always have the statement for opening the original file. If I want to keep the collapsed data I save that first and then reopen the original.

      Reply
  7. Eve says

    August 9, 2017 at 1:57 am

    Hello, I want to generate a line graph to summarise longitudinal data with confidence limits included. Please help me with the syntax.

    thanks

    Reply
    • Jeff Meyer says

      August 10, 2017 at 9:17 am

      Hi Eve,

      Here is a link to an example using a bar graph. You would create your code in the same manner but would use a line graph rather than a bar graph. This example shows you how to use the collapse command to generate the standard deviation of your variable of interest and then generate the confidence interval.
      https://stats.idre.ucla.edu/stata/faq/how-can-i-make-a-bar-graph-with-error-bars/

      Jeff Meyer

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Please note that, due to the large number of comments submitted, any questions on problems related to a personal study/project will not be answered. We suggest joining Statistically Speaking, where you have access to a private forum and more resources 24/7.

Primary Sidebar

This Month’s Statistically Speaking Live Training

  • Member Training: Analyzing Pre-Post Data

Upcoming Free Webinars

Poisson and Negative Binomial Regression Models for Count Data

Upcoming Workshops

  • Analyzing Count Data: Poisson, Negative Binomial, and Other Essential Models (Jul 2022)
  • Introduction to Generalized Linear Mixed Models (Jul 2022)

Copyright © 2008–2022 The Analysis Factor, LLC. All rights reserved.
877-272-8096   Contact Us

The Analysis Factor uses cookies to ensure that we give you the best experience of our website. If you continue we assume that you consent to receive cookies on all websites from The Analysis Factor.
Continue Privacy Policy
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT