Generating trimestral averages with panel dataset

[Nicolas Morales Alvarez]

Hello,

I have an unbalanced panel for 24 different companies with monthly data from 1995 to 2019 and 26 different variables . The data was given to me in an excel file and I've successfully imported it to Stata and defined it as a panel. My goal is to generate trimestral averages for each of the 26 variables. My intuition was to use the 'egen' command to generate the averages, however I need to generate an average for each company and the option by is not allowed when using a panel dataset. I found an sac called egenmore but I wasn't able to understand how to use it. An example of what I expect is:

For variable v1, I would like to generate trimestral averages by company.


My dofile goes like this until now:

*Dofile Provisiones
clear

import excel "/Users/nicolasmorales/Downloads/Base Bancos 11-2019.xlsx", sheet("Bancos 12-11-2019") firstrow

encode Entidad, gen(entidad)
drop Entidad
encode v44, gen(v_44)
drop v44
rename v_44 v44
rename Fecha fecha
sort entidad fecha
duplicates report entidad fecha
duplicates tag entidad fecha, gen(ind)
tab ind
drop if ind==1

gen monthly_date=mofd(fecha)
format monthly_date %tm
drop fecha
rename monthly_date fecha

xtset entidad fecha, monthly

[Wouter Wakker]

So if my Spanish is not failing me you want to calculate quarterly averages. You already have a monthly variable, which has to be turned into quarters.

gen quarter = qofd(dofm(monthly_date))
format %tq quarter

Then, you can calculate quarterly averages like this.

egen quarterly_average = mean(value), by(panel_id quarter)

Since you need to do this for 26 variables you could use a loop like this.

foreach var of varlist ... {
    egen avg_q_`var' = mean(`var'), by(panel_id quarter)
}

If you don't need the original data at all it would be more convenient to just collapse the data.

collapse (mean) varlist, by(panel_id quarter)

[Nick Cox]

Wouter Wakker gives good advice. I have to add that if your trimester don't match (JFM) (AMJ) (JAS) (OND) -- i.e. (January February March) and so forth -- then you need to tell us!

[Nicolas Morales Alvarez]

Thank you for your help! It worked perfectly