How to create the mean (with missing values) across multiple variables in Time Series data?

Hi all,

First post here, I used Statalist to write basically all my code for research on stock IPOs (so thank you!), but now I have a problem that I cannot seem to solve myself.

I have several financial metrics for about 60,000 stocks (+2.5M observations), and a sample of my Return on Assets data below:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN float ID str49 NAME int YEAR float(ROA_OFFYEAR1_n ROA_OFFYEAR2_n ROA_OFFYEAR3_n ROA_AVG)
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1980    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1981    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1982    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1983    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1984    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1985    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1986    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1987    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1988    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1989    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1990    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1991    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1992    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1993    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1994    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1995    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1996    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1997    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1998    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   1999    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2000    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2001    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2002    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2003    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2004    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2005    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2006    .    . 11.93    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2007    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2008    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2009    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2010    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2011    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2012    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2013    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2014    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2015    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2016    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2017    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2018    .    .     .    .
"AEA000101013" 1 "AL AIN AHLIA INSURANCE"   2019    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1980    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1981    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1982    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1983    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1984    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1985    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1986    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1987    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1988    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1989    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1990    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1991    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1992    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1993    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1994    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1995    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1996    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1997    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1998    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      1999    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2000    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2001    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2002    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2003    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2004 2.42    .     . 2.42
"AEA000201011" 2 "ABU DHABI COML.BANK"      2005    . 4.23     . 4.23
"AEA000201011" 2 "ABU DHABI COML.BANK"      2006    .    .  3.87 3.87
"AEA000201011" 2 "ABU DHABI COML.BANK"      2007    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2008    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2009    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2010    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2011    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2012    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2013    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2014    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2015    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2016    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2017    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2018    .    .     .    .
"AEA000201011" 2 "ABU DHABI COML.BANK"      2019    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1980    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1981    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1982    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1983    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1984    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1985    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1986    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1987    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1988    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1989    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1990    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1991    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1992    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1993    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1994    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1995    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1996    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1997    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1998    .    .     .    .
"AEA000301019" 3 "ABU DHABI NATIONAL HTLS." 1999    .    .     .    .
end

So f.i. look at the Abu Dhabi Commercial Bank with Unique ID 2. Now I used the code below to generate ROA_AVG, though I want ROA_AVG to return the mean (3.51) for EVERY year with this unique ID, so all years for the Abu Dhabi Bank, and if values are missing (like for ID 1) it also needs to work.

Maybe good to say is that the three ROA_OFFYEAR vars are (t+1), (t+2) and (t+3), so that years outside that are missing is fine.

Code I have now:

Code:

egen ROA_AVG=rmean(ROA_OFFYEAR1_n ROA_OFFYEAR2_n ROA_OFFYEAR3_n) if ID==2

forvalues i=2(1)67029 {
replace ROA_AVG=rmean(ROA_OFFYEAR1_n ROA_OFFYEAR2_n ROA_OFFYEAR3_n) if ID==`i'
}

Hope someone can help!

Regards, Olivier

BJ Data Tech Solution

Home / Data Cleaning / Data management / Data Processing / How to create the mean (with missing values) across multiple variables in Time Series data?
How to create the mean (with missing values) across multiple variables in Time Series data?

0 Response to How to create the mean (with missing values) across multiple variables in Time Series data?

Post a Comment

Home / Data Cleaning / Data management / Data Processing / How to create the mean (with missing values) across multiple variables in Time Series data? How to create the mean (with missing values) across multiple variables in Time Series data?

Related Posts with How to create the mean (with missing values) across multiple variables in Time Series data?

0 Response to How to create the mean (with missing values) across multiple variables in Time Series data?

Post a Comment

Home / Data Cleaning / Data management / Data Processing / How to create the mean (with missing values) across multiple variables in Time Series data?
How to create the mean (with missing values) across multiple variables in Time Series data?