Here there,
One of my tables, called mr, looks like this,
code ndate mr
130042 199402 0.96
130042 199403 0.97
130042 199404 0.95
130042 199405 1
130042 199406 1.1
130042 199407 1
130042 199408 1
130042 199409 1
130043 199205 0.98
130043 199206 1.01
130043 199207 1.03
130043 199208 1
130043 199209 1.02
130043 199210 1
130043 199211 1
130043 199212 1
130044 199801 0.89
130044 199802 1.02
...
code is an identifier of firms, ndate is a column of year and month, and
mr is gross monthly stock return. This table is a time series of monthly
returns for many firms. The problem of this data is that some firms have
stagnant returns (mr equal to 1) for some months in the ending part of
the firm's return series. For example, months 199407, 199408, and 199409
for firm 130042, and months 199210, 199211, 199212 for firm 130043. The
reason for this error is that people who construct the return series
keep adding 1's to the return series after the firm dies or gets delisted.
I need to remove these stagnant returns from the end of monthly return
time series up to the last non-stagnant return (mr not equal to 1).
Therefore, I want the table mr to look like this
code ndate mr
130042 199402 0.96
130042 199403 0.97
130042 199404 0.95
130042 199405 1
130042 199406 1.1
130043 199205 0.98
130043 199206 1.01
130043 199207 1.03
130043 199208 1
130043 199209 1.02
130044 199801 0.89
130044 199802 1.02
...
Notice that for '130042-199405' and '130043-199208', the returns are
also 1's, but I don't want to remove them because they are meaningful
observations before the firm dies or gets delisted. Only the stagnant
returns (mr equal to 1) from the end of return series up to the last
non-stagnant return need to be removed.
What is a good way of doing this in MySQL? Thanks.
I have not figured out a way yet, but I am looking into two potential
solutions:
1, user-defined variables.
2. select non-1 returns to a new table, find out the date of the last
non-1 return for each firm, say last_day, and add to the new table the
returns equal to one's and before last_day for each firm.
Best,
Jia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
