Consecutive tagging in Stata

0
votes

The task is to identify which consecutive week a product (in a specific store) has been on promotion.

clear
input ///
upc week store promo
1 1 86 1
1 2 86 1
1 3 86 1
1 4 86 1
3 1 86 0
3 2 86 1
4 1 86 0
4 2 86 1
4 3 86 1
end

The end result should look something like this:

upc week store promo promocount
1 1 86 1 1
1 2 86 1 2
1 3 86 1 3
1 4 86 1 4
3 1 86 0 0
3 2 86 1 1
4 1 86 0 0
4 2 86 1 1
4 3 86 1 2
end

I have 800K obs., and I am encountering a problem with the real data set. When I run bysort upc week store promo: gen prcount = _n if promo==1, my data set is sorted in a different way (which, as a result, yields wrong tagging):

upc week store promo
1 1 86 1
3 1 86 0
4 1 86 0
1 2 86 1
3 2 86 1
4 2 86 1
1 3 86 1
4 3 86 1
1 4 86 1

Anyway, I now realize my code is wrong. Any suggestions?

1
stata

1 Answers

1
votes

I think

. quietly input ///
> upc week store promo

. generate promocount = 0

. bysort store upc (week): replace promocount = 1+cond(_n==1,0,promocount[_n-1]) if promo>0
(7 real changes made)

. list, clean noobs

    upc   week   store   promo   promoc~t  
      1      1      86       1          1  
      1      2      86       1          2  
      1      3      86       1          3  
      1      4      86       1          4  
      3      1      86       0          0  
      3      2      86       1          1  
      4      1      86       0          0  
      4      2      86       1          1  
      4      3      86       1          2

does do what you want.