Creating dummy variable based on observation count of a time series

[Sera Sarkar]

Hi, I have a time series and want to create a dummy variable based on the number of observations in time=1 and compare it with the number of observation in time 2. Consider the following data::

time	Stock	obscount
1	a	22
1	b	21
1	c	20
1	d	22
1	e	22
1	f	22
2	a	22
2	b	22
2	c	23
2	d	19
2	e	22
2	f	18
3	a	21
3	b	20
3	c	23
3	d	17
3	e	22
3	f	22
3	g	22

In the above table for time 1 , stock a the obscount is 22 , then the output dummy variable=1 if the obscount for stock a in time 2 is also 22, otherwise it will be zero.
In the above example for stock b, the dummy variable output should be 0 considering above condition. This should be repeated for all t separately, (time 2 obscount will be compared with time 3). Also, in case the stock is missing in the next time series the dummy variable should be 0.)
Kindly help with this (hope I am clear in conveying what I want). Thank you in advance.

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte time str2 stock byte obscount
1 "a " 22
1 "b " 21
1 "c " 20
1 "d " 22
1 "e " 22
1 "f " 22
2 "a " 22
2 "b " 22
2 "c " 23
2 "d " 19
2 "e " 22
2 "f " 18
3 "a " 21
3 "b " 20
3 "c " 23
3 "d " 17
3 "e " 22
3 "f " 22
3 "g " 22
end

encode stock, gen(nstock)
xtset nstock time
gen byte wanted = obscount == F1.obscount

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Sera Sarkar]

Thank you for the response. Sorry about the dataex. I will keep in mind the next time.
So, I used the above code, it works ok if my stock variable is names like a, b, c but if my stock variable contains numbers (using permno like 123, 345 etc) , it shows the error "not possible with numeric variable" for the encode function. Is there any other way to do this? sorry for the inconvenience. Thank you again.

[Clyde Schechter]

The only reason the -encode- command is there is because -xtset- will not accept a string variable. So if in your real data your stock variable is numeric, just omit the -encode- command and change the -xtset- command to use the stock variable.

Moral of the story: Stata code is often crucially dependent on small details of the data. When showing example data, while the actual values shown do not need to be real, they need to be realistic. Replacing numbers with strings or vice versa will often cause code to break.

[Sera Sarkar]

Thank you so much. This works.