Creating a count variable based on the value of other variable

[Ahmed Khan]

Hi,

I have the following data where I have the following four variables and one variable that I want to generate i.e., Y. Please see the dataex below.

• ID
• date
• prc
• X
• Y (it is the variable that I want to generate)

Now, I want a variable Y, which will have the count of X if X is equal to 1. The twist is that for the same ID, each time X == 0, the count counter should reset and next count should start from 1 where the value of X == 1.

Below is the example of the data and I need the variable Y.

* Example generated by -dataex-. For more info, type help dataex
clear
input double(ID date prc X Y)
1 14269 5 0 0
1 14270 5 0 0
1 14271 5 0 0
1 14272 . 1 1
1 14273 . 1 2
1 14274 . 1 3
1 14275 5 0 0
1 14276 5 0 0
1 14277 5 0 0
1 14278 . 1 1
1 14279 . 1 2
1 14280 . 1 3
1 14281 . 1 4
1 14282 5 0 0
1 14283 5 0 0
end
format %tdMonth_dd,_CCYY date

Any help would be greatly appreciated.

Thanks

[Ahmed Khan]

I have found the solution to this question. Thanks to one of a previous example by Nick Cox

* Example generated by -dataex-. For more info, type help dataex
clear
input double(ID date prc X)
1 14269 5 0
1 14270 5 0
1 14271 5 0
1 14272 . 1
1 14273 . 1
1 14274 . 1
1 14275 5 0
1 14276 5 0
1 14277 5 0
1 14278 . 1
1 14279 . 1
1 14280 . 1
1 14281 . 1
1 14282 5 0
1 14283 5 0
end
format %tdMonth_dd,_CCYY date

the solution is

gen Y = cond(_n == 1 | X == 0, X, .) 
replace Y = Y[_n-1] + X if missing(Y)