Taking average across countries and years

[Muhammad Ramzan]

Assalam-o-Alaikum

I am working on panel data , having seven countries covering the time period 1970-2010.

I have to make the following table

Major Debt Indicators in South Asia (1970–2010) in Percentage

External Debt to GDP ratio	1970-1979	1980-1989	1990-1994	1995-1999	2000-2004	2005-2009
Bangladesh
Bhutan
India
Maldives
Nepal
Pakistan
Sri Lanka

how can i do this in stata any code for this.

Regards

[ben earnhart]

How is your data currently structured? Is it *wide* (years going across) or *long* (one obs per country per year?

[Muhammad Ramzan]

[Muhammad Ramzan]

ben earnhart I have posted the snapshot of the data

[ben earnhart]

I can't make anything out of your image file. Can you tell me how the data is structured, or show a snippet of data in a post?

[Muhammad Ramzan]

https://www.dropbox.com/s/ilo0hfwqpu...help.xlsx?dl=0

[Muhammad Ramzan]

link of the panel data file

[E. David Aja]

I can't really see your snippet very well, so I assume your variable names are countryname and year. It looks like your data are in long form.

I learned some of this trick from Nick Cox:

gen years = 10 * floor((year - 1)/10)
quietly ds countryname years, not 
collapse `r(varlist)' , by(countryname years)
table countryname years, c(mean debttogdp)

You'll want to tweak it since it seems like you want some intervals in five years and some in ten year.

[ben earnhart]

You never did answer my question, nor did you post a snippet of the data as asked. Nevertheless, here is syntax:

*==========setting years into blocks
gen yrgrp=.
replace yrgrp=1 if year >=1970 & year<1980
replace yrgrp=2 if year >=1980 & year<1990
replace yrgrp=3 if year >=1990 & year<1995
replace yrgrp=4 if year >=1995 & year<2000
replace yrgrp=5 if year >=2000 & year<2005
replace yrgrp=6 if year >=2005 & year<2010

*=========getting averages by year-blocks and country
egen avgedg=mean(edg), by (countryna yrgrp)

*=====going wide
*===getting rid of extraneous variables and years
drop year edg dsx
drop if yrgrp==.
drop if yrgrp==yrgrp[_n+1] & countryna==countryna[_n+1]
reshape wide avgedg, i(countryna) j(yrgrp)

[Muhammad Ramzan]

Thanks ben earnhart for your responce. One thing how this can be done for more than one variables like egd . dsx and esd etc. and how the results can be saved into word file. Thanks a lot