Panel Data: Compute Rolling Conditional Sum

[Sun Jeong]

Dear Statalists,


I have panel data with repeated time values (‘order’). I would like to calculate sum of ‘score’ for previous three orders for each type by 'item_id'.

Here is the data. The last three columns show the desired output.

input item_id post_id type_1 type_2 order score sum_rolling3_type_0 sum_rolling3_type_1 sum_rolling3_type_2
1001 31 0 0 1 4 . . .
1001 32 0 0 2 1 4 0 0
1001 33 0 0 3 4 5 0 0
1001 34 0 0 3 2 5 0 0
1001 35 1 0 4 1 11 0 0
1001 36 0 1 4 5 11 0 0
1001 37 0 1 4 5 11 0 0
1001 38 1 0 5 5 7 1 10
1001 39 0 0 6 3 6 6 10
1002 40 0 0 1 2 . . .
1002 41 0 1 2 5 2 0 0
1002 42 0 0 3 4 2 0 5
1002 43 0 0 4 5 6 0 5
1002 44 0 0 5 4 9 0 5
1002 45 0 0 5 5 9 0 5
1002 46 0 0 5 3 9 0 5
1002 47 0 1 5 4 9 0 5
1002 48 0 0 6 5 21 0 4
end

I tried the following code but it does not seem to work:

by item_id order, sort: egen test_sum_rolling3_type_0 = sum(cond((int(order -3 -1) & (type_1 == 0 & type_2 == 0)), score, .))
by item_id order, sort: egen test_sum_rolling3_type_1 = sum(cond((int(order -3 -1) & (type_1 == 1 & type_2 == 0)), score, .))
by item_id order, sort: egen test_sum_rolling3_type_2 = sum(cond((int(order -3 -1) & (type_1 == 0 & type_2 == 1)), score, .))


Basically, I need to restrict what ‘rangestat’ does to each type:

rangestat (sum) sum_rolling3 = score, by(item_id) int(order -3 -1)


I would much appreciate if anyone can help or provide any suggestions.

Many thanks in advanced!


Sun

[Sandra Bloem]

Does this code generate the desired variables?

clear
input item_id post_id type_1 type_2 order score sum_rolling3_type_0 sum_rolling3_type_1 sum_rolling3_type_2
1001 31 0 0 1 4 . . .
1001 32 0 0 2 1 4 0 0
1001 33 0 0 3 4 5 0 0
1001 34 0 0 3 2 5 0 0
1001 35 1 0 4 1 11 0 0
1001 36 0 1 4 5 11 0 0
1001 37 0 1 4 5 11 0 0
1001 38 1 0 5 5 7 1 10
1001 39 0 0 6 3 6 6 10
1002 40 0 0 1 2 . . .
1002 41 0 1 2 5 2 0 0
1002 42 0 0 3 4 2 0 5
1002 43 0 0 4 5 6 0 5
1002 44 0 0 5 4 9 0 5
1002 45 0 0 5 5 9 0 5
1002 46 0 0 5 3 9 0 5
1002 47 0 1 5 4 9 0 5
1002 48 0 0 6 5 21 0 4
end


generate type_0 = (type_1==0 & type_2==0)
order type_0, before(type_1)


foreach q in 0 1 2 {
    generate score`q' = score if type_`q'==1
    rangestat (sum) newsum3_type_`q' = score`q', by(item_id) int(order -3 -1)
    *drop score`q'
}

[Sun Jeong]

Yes, it does. Thank you so much, Sandra! I really appreciate it.