Delete zeros at the end of return time-series

[Bibi Crommert]

Hi everyone,

I am currently working on my bachelor thesis.
I want to delete all the zeros at the end of each time-series.

I have a variable Name containing the name of the company. I have a variable mofd containing the month (in Stata language) and i have a variable returns, containing the returns for each company in each month.

I have panel data containing values with zero. I wish to drop any observations at the end of each panel containing just values with zero. How do I do this?
So more specific. I am looking at monthly stock returns for different companies. I use the timeframe 01-01-2013 till 01-05-2022. If a company's returns equals zero at the end of the time-series it means a company got delisted. I want to keep the zeros at the beginning and middle of the panel, but delete the zeros at the end. So starting at the end and checking if a value contains a zero and then deleting it. Then looking one month before that and doing the same. But nog deleting for instance a zero in month 01-04-2022 when 01-05-2022 contains a numeric value which is not zero. So that for every company the latest value is a 'real value' and not zero.

If this is not clear enough, I want to what they did on this Stata page, but then delete zeros at the end instead of missing values.
https://www.stata.com/support/faqs/d...issing-values/


I hope you can help me.

Bibi

[Carlo Lazzaro]

Bibi:
welcome to this forum.
As per FAQ, please provide an excerpt of your data via -dataex-. Thanks.

[Bibi Crommert]

Hi Carlo, thanks for your quick reply. I will share an example of my data.

Name mofd returns
1 2013m1 12.5
1 2013m2 6.8
1 2013m3 0
1 2013m4 0
.... till 2022m5
2 2013m1 14.5
2 2013m2 0
2 2013m3 13.1
2 2013m4 4.5
3 2013m1 9.7
3 2013m2 8.1
3 2013m3 0
4 2013m1 .
4 2013m2 0

When looking at this example I would like to remove the zeros and replace them with a missing value. But I don't want to replace Name 2 2013m2 with a missing value because it's not a zero at the end of a month. I hope you understand what I mean. Thanks in advance.

My dataset is very big and I don't know if I can share it. It is on a different computer as well. I hope this helps.

[Nick Cox]

Please use dataex as requested. https://www.statalist.org/forums/help#stata explains.

Here is a sketch. Reverse time first, and then for each stock, drop observations if and only if the cumulative sum is zero, as it will be for zeros and missings.

gen negdate = -mofd 
bysort Name (negdate) : drop if sum(returns) == 0 
sort Name mofd

See also this concurrent thread, and its references:

https://www.statalist.org/forums/for...for-each-group

[Bibi Crommert]

Hi Nick,

I will look into the link and use dataex next time, thanks.

I just tried the code and it works! Thank you so much!! (also for the quick reply)

[Bibi Crommert]

Hi everyone,

I have one more dynamic screen that I need to apply to my data and I can't figure it out.

I need to 'delete observations of stocks that show non-zero price changes in less than 50% of the traded months in previous 12 months'. My timespan is till 2022m5. So this means that I will need to check non-zero price changes from 2021m6 till 2022m5. In these 12 months I want to count the number of zero returns. If the number of zero returns is bigger or equal to 6 (so the opposite of 'non-zero price changes in less than 50%), I want to delete the observations that have non-zero price changes.

So in my head the steps would be:
1. check how many zero-return observations a company has between 2021m6 till 2022m5.
2. if equal or more than 6...
3. delete other observations between 2021m6 and 2022m5 that do contain another value than zero.

Considering I deleted all the observations at the end containing a zero, it is possible that I for instance have no observation of 2022m5 or 2022m5 and 2022m4. But then I could still have equal or more observations containing zero (between 2021m6-2022m5).

I hope you can help me.
Bibi