Question: 5 year average growth rate

[Kaas de vries]

Hi all,

Intro:
I want compute a regression analysis from some variables (x) on a 5 year average growth rate (y). My simplified dataset is below, presenting: Country - Regioncode (unique identifier for a city), Year and Growthrate.

Question:
How do I create two new variables representing the average growth rate from 2018-2022, while matching the regioncode? I want to do this in two different ways, so I can see how the results in the final regression analysis differ:

1: (2018+2019+2020+2021+2022)/5
2: (1+growth2018*1+growth2019*1+growth2020*1+growth20 21*1+growth2022)^(1/5)

Dataset:

Country	Regioncode	Year	Growthrate
Armenia	1	2015	8,4
Armenia	1	2016	8,6
Armenia	1	2017	8,69
Armenia	1	2018	8,9
Armenia	1	2019	9,16
Armenia	1	2021	9,24
Armenia	1	2022	9,26
Armenia	2	2016	33,73
Armenia	2	2017	34,27
Armenia	2	2018	35,36
Armenia	2	2019	35,92
Armenia	2	2020	36,56
Armenia	2	2021	37,24
Armenia	2	2022	37,29

Code:
clear
input str7 country str10 regioncode str4 year str10 Growth
"Armenia" "1" "2012" "8,4"
"Armenia" "1" "2013" "8,6"
"Armenia" "1" "2014" "8,69"
"Armenia" "1" "2015" "8,9"
"Armenia" "1" "2016" "9,16"
"Armenia" "1" "2017" "9,24"
"Armenia" "1" "2018" "9,26"
"Armenia" "2" "2012" "33,73"
"Armenia" "2" "2013" "34,27"
"Armenia" "2" "2014" "35,36"
"Armenia" "2" "2015" "35,92"
"Armenia" "2" "2016" "36,56"
"Armenia" "2" "2017" "37,24"
"Armenia" "2" "2018" "37,29"
end
[/CODE]



What did I try so far?
I tried creating new variables with the help of similar posts on growth rates on this forum, in a step-by-step approach. e.g. transforming the growth rates by dividing them by 100 & adding 1, using generate & bysort regioncode (year) while generating a new variable.

Best,

Kaas

[Jared Greathouse]

Please reformat your data example such that it looks

like this

. Also, please show the exact verbatim code you've tried, not just "I tried x y x". The details matter here. So, please, do show the exact syntax you've tried to get what you want.

[Kaas de vries]

Hi Jared!

Thanks a lot for your response and your feedback. I should indeed have specified better.

For option 1 I tried:

egen Averagegrowth = mean(Growth / (year >= 2014 & year <= 2018)), by(regioncode)

For option 2 I tried:

ssc install egenmore
gen growth2= (Growth/100)+1
egen Averagegrowth2 = gmean(growth2 / (year >= 2014 & year <= 2018)), by(regioncode)

Both codes got me almost what I want for both options. The averages are calculated correctly for the timeframe I want. However, the average values are also shown for the years outside 2014-2018. I think I need to add one more term that causes the observations with years outside the given frame to show zero or ".".

[Andrew Musau]

You will want to use the -if- qualifier to restrict observations. Other useful functions in this area are -cond()- and -inrange()-.

bys regioncode: egen wanted= mean(Growth) if inrange(year, 2014, 2018)

[Kaas de vries]

Many thanks Andrew, that was exactly what I needed. I added "if inrange (year, 2014, 2018)" to my initial code and got it working.

Many many thanks Andrew and Jared!