Time series aggregate data

[Omar Okasha]

Dear Experts,
I have an aggregate time-series data from 2000 to 2013. For each year, the data is aggregated by number of events per cohort (each cohort is the population size in each age-gender combination), as shown below

year	age	sex	events	cohort
2000	18	F	42	32913
2000	18	M	138	34813
2000	19	F	36	31815
2000	19	M	542	33211
2000	20	M	330	33131
2000	20	F	33	31474

1. From this data, I need to create a dataset in which age is categorized into 5 groups: 18-39, 40-64, 65-74, 75-85, and >85. Also, I need to sum the numbers of males and female from each age-year ; so that it looks like this:

year	age	events	cohort (males and females)
2000	18-39	xxx	xxxxx
2000	40-64	xxx	xxxxx

So events would be the sum of events for those aged 18-39, both males and females, in the year 2000, and so forth.

2. Based on the first table, and if I want to declare the data as panel time-seris in tsset, it seems I have two panel variables (age and gender), I keep getting an error message if I put both of them in the command, so what should I do about this?

Sincerely
Omar

[Carlo Lazzaro]

Omar:
as far as your first question is concerned, you may want to try what follows:

g age_category=1 if age >=18 & age<=39
replace age_category=2 if age >=40 & age<=64
replace age_category=3 if age >=65 & age<=74
replace age_category=4 if age >=75 & age<=85
replace age_category=5 if age >85
collapse (sum) events cohort, by( year sex age_category )
label define age_category 1 "18-39" 2 "40-64" 3 "65-74" 4 "75-85" 5 ">85"
label val age_category age_category

As far as your second question is concerned, you shoud have a panelid (i.e., the patient under investigation across the selected span of time) and year, as a time-series identifier:

xtset panelid year

[Omar Okasha]

Thank you very much, Carlo! That is very helpful
Best Wishes