Title stata.com collapse — Make dataset of summary …

1 make dataset of summary statisticsSyntaxMenuDescriptionOptionsRe marks and examplesAcknowledgmentAlso seeSyntaxcollapseclist[if] [in] [weight] [,options]whereclistis either[(stat)]varlist[ [(stat)]..][(stat)]targetvar=varname[tar getvar=varname..] [ [(stat)]..]or any combination of thevarlistandtargetvarforms, andstatis one ofmeanmeans (default)sumsumsmedianmediansrawsumsums, ignoring optionally specified weightp11st percentileexcept observations with a weight ofp22nd percentilezero are excluded..3rd 49th percentilescountnumber of nonmissing observationsp5050th percentile (same asmedian)percentpercentage of nonmissing observations..51st 97th percentilesmaxmaximumsp9898th percentileminminimumsp9999th percentileiqrinterquartile rangesdstandard deviationsfirstfirst valuesemeanstandard error of the meanlastlast value(sd/sqrt(n))firstnmfirst nonmissing valuesebinomialstandard error of the mean, binomiallastnmlast nonmissing value(sqrt(p(1-p)/n))sepoissonstandard error of the mean, Poisson(sqrt(mean))Ifstatis not specified,meanis (varlist)groups over whichstatis to be calculatedcwcasewise deletion instead of all possible observationsfastdo not restore the original dataset should the user pressBreak; programmer scommandvarlistandvarnameinclistmay contain time-series operators; see[U] Time-series ,fweights,iweights, andpweights are allowed; see[U] weight, and not be used withsd,semean,sebinomial, may not be used withsemean,sebinomial, may not be used not appear in the dialog.

2 collapse age educ income, by(state). collapse (mean) age educ (median) income, by(state). collapse (mean) age educ income (median) medinc=income, by(state). collapse (p25) gpa [fw=number], by(year)12 collapse make dataset of summary statisticsMenuData>Create or change data>Other variable-transformation commands> make dataset of means, medians, the dataset in memory into a dataset of means, sums, medians, to numeric variables : See [D]contractif you want to collapse to a dataset of Options by(varlist)specifies the groups over which the means, etc., are to be calculated. If this option isnot specified, the resulting dataset will contain 1 observation. If it is specified,varlistmay referto either string or numeric casewise deletion. Ifcwis not specified, all possible observations are used for eachcalculated following option is available withcollapsebut is not shown in the dialog box:fastspecifies thatcollapsenot restore the original dataset should the user for use by and the dataset in memory and creates a new dataset containing summary statisticsof the original meaningful variable labels to the variables in this new the syntax diagram forcollapsemakes using it appear more complicated than it is,collapseis best explained with are presented under the following headings:Introductory examplesVariablewise or casewise deletionWeightsA final exampleIntroductory examplesExample 1 Consider the following artificial data on the grade-point average (gpa) of college students: collapse make dataset of summary statistics 3.

3 Use describeContains data from : 12vars: 4 3 Jan 2013 12:05size: 120storage display valuevariable name type format label variable labelgpa float % gpa for this yearhour int % Total academic hoursyear int % 1 = freshman, 2 = sophomore, 3= junior, 4 = seniornumber int % number of studentsSorted by: year. list, sep(4)gpa hour year 30 1 34 1 28 1 30 1 29 2 30 2 35 2 30 3 35 3 33 3 32 4 31 4 2To obtain a dataset containing the 25th percentile of gpa s for each year, we type. collapse (p25) gpa [fw=number], by(year)We used frequency we want to create a dataset containing the mean of gpa and hour for each year.

4 We do nothave to type(mean)to specify that we want the mean because the mean is reported by use , clear. collapse gpa hour [fw=number], by(year). listyear gpa we want to create a dataset containing the mean and median ofgpaandhour, and we wantthe median ofgpaandhourto be stored as variablesmedgpaandmedhour, collapse make dataset of summary statistics. use , clear. collapse (mean) gpa hour (median) medgpa=gpa medhour=hour [fw=num], by(year). listyear gpa hour medgpa 32 Here we want to create a dataset containing a count ofgpaandhourand the minimums ofgpaandhour. The minimums ofgpaandhourwill be stored as variablesmingpaandminhour, use , clear. collapse (count) gpa hour (min) mingpa=gpa minhour=hour [fw=num], by(year). listyear gpa hour mingpa 18 18 12 12 9 9 7 7 31 Now we replace the values ofgpain 3 of the observations with missing use , clear.

5 Replace gpa = . in 2/4(3 real changes made, 3 to missing). list, sep(4)gpa hour year 30 1 34 1 28 1 30 1 29 2 30 2 35 2 30 3 35 3 33 3 32 4 31 4 2If we now want to list the data containing the mean ofgpaandhourfor each year,collapseuses all observations onhourforyear=1, even thoughgpais missing for observations 1 make dataset of summary statistics 5. collapse gpa hour [fw=num], by(year). listyear gpa we repeat this process but specify thecwoption,collapseignores all observations that havemissing use , clear. replace gpa = . in 2/4(3 real changes made, 3 to missing). collapse (mean) gpa hour [fw=num], by(year) cw. listyear gpa 2We have individual-level data from a census in which each observation is a person.

6 Among othervariables, the dataset contains the numeric variablesage,educ, andincomeand the string variablestate. We want to create a 50-observation dataset containing the means of age, education, andincome for each collapse age educ income, by(state)The resulting dataset contains means becausecollapseassumes that we want means if we do notspecify otherwise. To make this explicit, we could have typed. collapse (mean) age educ income, by(state)Had we wanted the mean forageandeducand the median forincome, we could have typed. collapse (mean) age educ (median) income, by(state)or if we had wanted the mean forageandeducand both the mean and the median forincome, wecould have typed. collapse (mean) age educ income (median) medinc=income, by(state)This last dataset will contain three variables containing means age,educ, andincome and onevariable containing the median of income medinc. Because we typed(median) medinc=income,Stata knew to find the median for income and to store those in a variable namedmedinc.

7 Thisrenaming convention is necessary in this example because a variable namedincomecontaining themean is also being collapse make dataset of summary statisticsVariablewise or casewise deletionExample 3 Let s assume that in our census data, we have 25,000 persons for whom age is recorded but only15,000 for whom income is recorded; that is,incomeis missing for 10,000 observations. If wewant summary statistics forageandincome,collapsewill, by default, use all 25,000 observationswhen calculating the summary statistics forage. If we prefer thatcollapseuse only the 15,000observations for whichincomeis not missing, we can specify thecw(casewise) option:. collapse (mean) age income (median) medinc=income, by(state) cwWeightscollapseallows all four weight types; the default isaweights. Weight normalization affectsonly thesum,count,sd,semean, observations andiindex by-groups. Here are the definitions forcountandsumwithweights:count:unweight ed:Ni, the number of observations in groupiaweight:Ni, the number of observations in groupifweight,iweight,pweight: wj, the sum of the weights over observations ingroupisum:unweighted: xj, the sum ofxjover observations in groupiaweight: vjxjover observations in groupi;vj=weightsnormalized to sum toNifweight,iweight,pweight: wjxjover observations in groupiWhen theby()option is not specified, the entire dataset is treated as one with weights returns the square root of the bias-corrected variance, which isbased on the factor Ni/(Ni 1), whereNiis the number of observations.

8 Statisticssd,semean,sebinomial, andsepoissonare not allowed withpweighted data. Otherwise, the statistic ischanged by the weights through the computation of the weighted count, as outlined instance, consider a case in which there are 25 observations in the dataset and a weightingvariable that sums to 57. In the unweighted case, the weight is not specified, and the count is the analytically weighted case, the count is still 25; the scale of the weight is irrelevant. In thefrequency-weighted case, however, the count is 57, the sum of the withaweights ignores the weight, with one exception: observations withzero weight will not be included in the make dataset of summary statistics 7 Example 4 Using our same census data, suppose that instead of starting with individual-level data andaggregating to the state level, we started with state-level data and wanted to aggregate to the regionlevel.

9 Also assume that our dataset containspop, the population of each obtain unweighted means and medians of age and income, by region, along with the totalpopulation, we could type. collapse (mean) age income (median) medage=age medinc=income (sum) pop,> by(region)To obtain weighted means and medians of age and income, by region, along with the totalpopulation and using frequency weights, we could type. collapse (mean) age income (median) medage=age medinc=income (count) pop> [fweight=pop], by(region)Note: Specifying(sum) popwould not have worked because that would have yielded thepop-weighted sum ofpop. Specifying(count) agewould have worked as well as(count) popbecausecountmerely counts the number of nonmissing observations. The counts here, however, arefrequency-weighted and equal the sum obtain the same mean and medians as above, but using analytic weights, we could type. collapse (mean) age income (median) medage=age medinc=income (rawsum) pop> [aweight=pop], by(region)Note: Specifying(count) popwould not have worked because, with analytic weights,countwouldcount numbers of physical observations.

10 Specifying(sum) popwould not have worked becausesumwould calculate weighted sums (with a normalized weight). Therawsumfunction, however, ignoresthe weights and sums only the specified variable, with one exception: observations with zero weightwill not be included in the have worked as the solution to all three final exampleExample 5We have census data containing information on each state s median age, marriage rate, and divorcerate. We want to form a new dataset containing various summary statistics, by region, of the variables:8 collapse make dataset of summary statistics. use , clear(1980 Census data by state). describeContains data from : 50 1980 Census data by statevars: 7 6 Apr 2013 15:43size: 1,700storage display valuevariable name type format label variable labelstate str14 %14s Statestate2 str2 %-2s Two-letter state abbreviationregion int % cenreg Census regionpop long % Populationmedian_age float % Median agemarriage_rate float % float % by: region.