Collapse function, error - variable not found + error in option by ())

[Lucas Bordure]

Good afternoon to the community.
I would like to calculate the mean and standard deviation of intangible assets per industry.



But when, i run the command of my do-file editor, i get the error "variable industry_n not found" , error in option by())
I share my Stata file "File2.dta" in attachment.

cd "C:\Users\lucas\Documents\GP\"

use"File2.dta", clear

preserve
*mean and standard deviation of intangible_assets per year
collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(year)
asdoc list, replace
*encode string variable industry (video: https://www.youtube.com/watch?v=ZRWHjdIZyxo)
*mean and standard deviation of intangible_assets per industry
collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(industry_n)
asdoc list, replace
restore

*Export dataset
save "File2.dta", replace

export excel using "File2.xlsx", sheetreplace firstrow(variables)

Thank you for your help.

[Andrew Musau]

collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(year)

You lose "industry_n" in the first collapse. If you want the mean by industry and year, specify both variables within -by()-

collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(industry_n year)

[Lucas Bordure]

Thank you for your answer Andrew Musau, but I just want the mean and standard deviation of intangible assets per industry.

[Andrew Musau]

bys year: egen mean_intangible_assets=mean(intangible_assets)
bys year: egen sd_intangible_assets=sd(intangible_assets)
collapse mean_intangible_assets sd_intangible_assets, by(industry_n)

[Lucas Bordure]

Thank your for your fast answer.

I wanted one table to get the mean and standard deviation of intangible_assets per year and one table to get the mean and standard deviation of intangible_assets per industry.
I compiled your ideas and I got this code which is working:

cd "C:\Users\lucas\Documents\GP\"

use"File2.dta", clear

preserve
*mean and standard deviation of intangible_assets per year (to enter in the command stata!)
collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(year)
asdoc list, replace
restore
*encode string variable industry (video: https://www.youtube.com/watch?v=ZRWHjdIZyxo)
*mean and standard deviation of intangible_assets per industry (to enter in the command stata!)
bys year: egen mean_intangible_assets=mean(intangible_assets)
bys year: egen sd_intangible_assets=sd(intangible_assets)
collapse mean_intangible_assets sd_intangible_assets, by(industry_n)
asdoc list, replace
restore

[Andrew Musau]

bys year: egen mean_intangible_assets=mean(intangible_assets)
bys year: egen sd_intangible_assets=sd(intangible_assets)
collapse mean_intangible_assets sd_intangible_assets, by(industry_n)

Note that this code first generates annual means and annual standard deviations and then averages across industries. If you just need the means and standard deviations by industry

restore, preserve
collapse (mean) mean_intangible_assets=intangible_assets (sd) sd_intangible_assets=intangible_assets, by(industry_n)