Time-lagged variable for cross-sectional data

[Christoph Steinhardt]

Hi,

I have a seemingly simple recoding problem, which I could not solve by reading the documentation or existing discussions I found.

This is my data structure:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(monthly a) byte b float(monthly_a monthly_b)
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  1 0  4 0
648  0 0  4 0
648  0 0  4 0
648  1 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  1 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  1 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
648  0 0  4 0
649  0 0 37 4
649  0 0 37 4
649  0 0 37 4
649  0 0 37 4
649  0 0 37 4
649  0 0 37 4
649  2 0 37 4
649 34 4 37 4
649  1 0 37 4
649  0 0 37 4
649  0 0 37 4
649  0 0 37 4
650  0 0  3 3
650  0 0  3 3
650  1 2  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  2 1  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
650  0 0  3 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  1 0 22 3
651  0 0 22 3
651  0 0 22 3
651  2 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
651  0 0 22 3
end
format %tmMon/CCYY monthly

"monthly_a" and "monthly_b" are monthly totals of a and b created with

 by monthly, sort: egen monthly_a = total(a)

.

What I need is a time-lagged variable where the values of "monthly_a" and "monthly_b" for month "648" are assigned to month "649" etc.

My data is cross-sectional event data. Thus, the time series operator "L1" does not work. When I

 tsset monthly

I get the error message "repeated time values in sample".

I get what I want if I replace all values one by one.

gen Lmonthly_a=.
replace Lmonthly_a = . if monthly == 648
replace Lmonthly_a = 4 if monthly == 649
replace Lmonthly_a = 37 if monthly == 650
...

However, I want to avoid this "by hand" solution, because I have a larger number of lagged variables to create and altogether 29 months in my data.

Is there, for instance, a way to tell Stata

replace Lmonthly_a = "value of monthly_a" if monthly == 648

instead of specifying a specific number?

Any help would be greatly appreciated. Apologies if I have overlooked a very simple solution to my problem.

Cheers,

Christoph

[Phil Bromiley]

Welcome to Stata list. It is very hard to understand your problem - you seem to have piles of duplicated observations for a given month. What you can do is collapse the data by month. Then you can refer to the previous observation with [_n-1] that is

g priorx=x[_n-1]

save the collapsed data with the lag set up. Then merge the collapsed data with the original data.

[Christoph Steinhardt]

Apologies for not stating the problem more clearly and thanks for your suggestion, Phil.

Just now I came across a solution for the same problem on Stackoverflow. It did the trick.


https://stackoverflow.com/questions/...ction-in-stata