Adding up observations succesively per month

[Johanna Krenz]

Hi,

I have data on variable X for only single days of a month. The other days of a month have a zero for the observation. I want to succesively add up the observations for one month, i.e. Xcum_t = X_t + X_t-1. At the first of each month I want to start a new sum. I tried the following:

format %td date
tsset date
tsfill
replace X = 0 if X == .

generate month = month(date)
generate year = year(date)
generate yearmonth = ym(year,month)

sort date
bysort yearmonth: gen cumX = X + L.X

But this always gives me "not sorted".

What am I doing wrong?

Best, Johanna

[Clyde Schechter]

When you use the lag operator, Stata requires that the data be sorted on the variable that was used with -tsset- (or, in panel data, the time variable in the -xtset- command). Your data were -tsset- on date, not yearmonth.

That said, if you change the -tsset- to yearmonth, you will get the error that you have repeated values of yearmonth.

So you just can't do this with the lag operator. I'm not sure what you are trying to calculate from your description, but if it is a running sum of X:

by yearmonth (date), sort: gen cumX = sum(x)

[Nick Cox]

As a side note to @Clyde Schechter's helpful answer know that you can get a monthly date out of a daily date directly by

gen mdate = mofd(date)
format mdate %tm

[Johanna Krenz]

Great, that's what I was looking for. Thank you!