Collapsing time series data

[Caroline Shaw]

I have two time series datasets; one weekly and one daily. I want to collapse the daily one to weekly and create means on the variable values. The issue I am struggling with is that I want to collapse the daily series from Monday to Sunday (to match the variable in the weekly series which is measured on a Monday). Stata's weekly variable starts on a Sunday so I can't use that (as far as I can see) to collapse on. I've been trying to create a 7 day subgroup variable (with the aim that I would collapse on that) using generate and _n, but I can't get the coding for that right (it's probably blindingly obvious sorry). Thanks for any help.

[Nick Cox]

Weeks need care in Stata. For moderately detailed discussion see

http://www.stata-journal.com/sjpdf.h...iclenum=dm0052

http://www.stata-journal.com/article...article=dm0065

http://www.stata-journal.com/article...ticle=dm0065_1

The first of these articles is accessible to all.

There are various tricks in this territory but here's another. I suppose that you have a daily date variable, say day

 
sort day 
gen week = day if dow(day)== 1 
replace week = week[_n-1] if missing(week)

Here we define weeks by the Mondays that start them, the diagnostic being that the day of the week is returned as 1. Then we copy each Monday date downwards in the dataset as needed, so that Tuesdays ... Sundays are all tagged with the previous Monday's date. Then you can collapse on the new variable. This works with panel data too although you would use panel identifier as well.

See also http://www.stata.com/support/faqs/data-management/replacing-missing-values/

[Caroline Shaw]

Thanks for that. It worked a treat.

[Nick Cox]

For the record: The method in #2 presupposes that all Mondays you need to copy are present in the data. Otherwise some days will get tagged incorrectly.

This is safer and shorter:

 
gen week = cond(dow(day) == 0, day - 6, day - dow(day) + 1)

That is, given that dow() returns 0 to 6 for Sunday...Saturday

1. If the day of the week is 0 (it's Sunday), subtract 6 to get the previous Monday.
2. Otherwise, subtract the day of the week and add 1.