collapse all Variables in Stata

[Muhammad Ramzan]

Hi I have number of variables in Stata and I want to collapse in Stata using mean statistics.


collapse (mean) DHSCLUST-time_var , by (id)
type mismatch


I am getting the type mismatch error

[Muhammad Ramzan]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Child_male int(DHSCLUST DHSYEAR) byte HH_Male double(LATNUM LONGNUM)
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
1 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 2 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
2 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
1 1 2017 1 36.44991823 72.57155787
end
label values Child_male B4
label def B4 1 "male", modify
label def B4 2 "female", modify
label values HH_Male V151
label def V151 1 "male", modify
label def V151 2 "female", modify

------------------ copy up to and including the previous line ------------------

[William Lisowski]

If you do

describe DHSCLUST-time_var

you will likely see that you have one or more string variables within that list of variables.

[Muhammad Ramzan]

William Lisowski

What I can do to collapse all the variable Please what is the most efficient way

[William Lisowski]

Without a sample of the data that causes the problem you describe in post #1 it is difficult to offer any advice. And it should include the id variable. Your example in post #1 does not allow us to reproduce your problem.

[Muhammad Ramzan]

Contains data from E:\OneDrive - Clark University\Health and air quality\DHS data\PK_2017-18_DHS_Stata\PKBR71DT\DHS.DTA
obs: 454,455
vars: 52 2 Dec 2021 15:42
size: 44,082,135
-----------------------------------------------------------------------------------------------------------------------------------------------
storage display value
variable name type format label variable label
-----------------------------------------------------------------------------------------------------------------------------------------------
DHSCLUST int %8.0g cluster number
household_num~r byte %8.0g household number
Mother_month_~h byte %8.0g respondent's month of birth
Mother_year_b~h int %8.0g respondent's year of birth
v011 int %8.0g date of birth (cmc)
mother_age byte %8.0g respondent's current age
v013 byte %8.0g V013 age in 5-year groups
v023 byte %8.0g V023 stratification used in sample design
v024 byte %8.0g V024 region
urban byte %8.0g V025 type of place of residence
v026 byte %8.0g V026 na - de facto place of residence
v102 byte %8.0g V102 type of place of residence
v103 byte %8.0g V103 na - childhood place of residence
v104 byte %8.0g V104 na - years lived in place of residence
v105 byte %8.0g V105 na - type of place of previous residence
v105a byte %8.0g V105A na - region of previous residence
v106 byte %8.0g V106 highest educational level
v107 byte %8.0g V107 highest year of education
v130 byte %8.0g V130 na - religion
ethnicity byte %8.0g na - ethnicity
education byte %8.0g V133 education in single years
members byte %8.0g number of household members (listed)
HH_Male byte %8.0g V151 sex of household head
v152 byte %8.0g V152 age of household head
wealth_index double %12.0g wealth index factor score combined (5 decimals)
v701 byte %8.0g V701 husband/partner's education level
Partner_edu byte %8.0g V702 husband/partner's highest year of education (at level in v701)
Partner_occ long %12.0g V704 husband/partner's occupation
v705 byte %8.0g V705 husband/partner's occupation (grouped)
v714 byte %8.0g V714 respondent currently working
v714a byte %8.0g V714A respondent has a job, but currently absent
partner_edu byte %8.0g V715 husband/partner's total number of years of education
Mother_occ byte %8.0g V716 respondent's occupation
Partner_age byte %8.0g V730 husband/partner's age
birth_order byte %8.0g birth order number
b0 byte %8.0g B0 child is twin
b1 byte %8.0g month of birth
b2 int %8.0g year of birth
Child_male byte %8.0g B4 sex of child
b5 byte %8.0g B5 child is alive
b7 int %8.0g age at death (months, imputed)
b8 byte %8.0g current age of child
nmr float %6.0f neonatal mortality
pnm float %6.0f
imr float %6.0f
cmr float %6.0f
ufmr float %6.0f
time_var float %9.0g
id float %9.0g
time9_var float %tm
month float %9.0g
year float %9.0g
-----------------------------------------------------------------------------------------------------------------------------------------------
Sorted by: DHSCLUST id

[Muhammad Ramzan]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int DHSCLUST byte(household_number Mother_month_birth) int(Mother_year_birth v011) byte(mother_age v013 v023 v024 urban v026 v102 v103 v104 v105 v105a v106 v107 v130 ethnicity education members HH_Male v152) double wealth_index
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 1 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 2 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 3 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 4 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 5 1 1 12 1982  996 35 5 5 3 2 . 2 . . . . 2 5 . . 10 7 1 44 -1.39014
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 6 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 7 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 8 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
 9 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
10 1 4  6 1980  966 37 5 5 3 2 . 2 . . . . 0 . . .  0 6 2 37 -1.03034
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
11 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
12 1 5 10 1985 1030 32 4 5 3 2 . 2 . . . . 0 . . .  0 7 1 70 -1.48579
end
label values v013 V013
label def V013 4 "30-34", modify
label def V013 5 "35-39", modify
label values v023 V023
label def V023 5 "kpk rural", modify
label values v024 V024
label def V024 3 "kpk", modify
label values urban V025
label def V025 2 "rural", modify
label values v026 V026
label values v102 V102
label def V102 2 "rural", modify
label values v103 V103
label values v104 V104
label values v105 V105
label values v105a V105A
label values v106 V106
label def V106 0 "no education", modify
label def V106 2 "secondary", modify
label values v107 V107
label values v130 V130
label values education V133
label values HH_Male V151
label def V151 1 "male", modify
label def V151 2 "female", modify
label values v152 V152

------------------ copy up to and including the previous line ------------------

[Rich Goldstein]

I can't use the command you used in #1 because there is no variable called "time_var" in the sample you sent; however, the following works fine:

collapse (mean) DHSCLUST-wealth_index , by (id)

[William Lisowski]

Your output from the describe command in post #6 shows us that all the variables are numeric. And the data in post #7 does not reproduce the problem you report in post #1, as Rich confirms in post #8.

Testing with an invented dataset I discovered (using Stata 17) that including a string variable in the list of variables to be collapsed gives the following results.

 set seed 666

. set obs 1000
Number of observations (_N) was 0, now 1,000.

. generate byte v1 = runiform(0,10)

. generate str2 v2 = "42"

. generate id = ceil(_n/5)

. collapse (mean) v1-v2, by(id)
string variable v2 not allowed with statistic mean
r(109);

So my guess about the string variable was incorrect; the error message in that case (at least with Stata 17) is much more informative than the one you obtained.

I suggest you rerun your collapse command with the describe command immediately in front of it to make sure your dataset hasn't been changed by earlier code.

describe DHSCLUST-time_var
collapse (mean) DHSCLUST-time_var , by (id)

If this fails, please copy the describe command, its output, and the collapse command and its output, and pasted this into your next post. Please surround the results with code delimiters [CODE] and [/CODE], as dataex does, to make the output as readable as possible.