Generating dummy variable if another dummy variable occurs over time.

[Thijs Kroon]

Hello Statalisters,

I'm currently writing my master thesis in Finance and I want to do a persistence test. I have found a few ways to do this but for this I need dummy variables which I do not know how to create. So I have this data set containing excess return data of 254 funds over a time period of 12 years. I indicated winners and losers for each fund observation by generating dummy variables. Winner (W) which takes the value of 1 if the excess return is above the median and loser (L) which takes the value of 1 if the excess return is below the median. For the persistence test I want to generate a new dummy taking the value of 1 if a fund is a winner in month t & a winner in month t+1. This dummy will be indicated by WW. The same sort of dummy variables are needed for Winner-Loser (WL) and Loser-Loser (LL). I tried to use tsspell but I did not manage to get the results I need.
I hope you understand what I try to do.
hanks in advance for any answer/suggestion!

* Example generated by -dataex-. To install: ssc install dataex
clear
input long time int(_j date) float(excessReturn median_excessReturn W L)
1   1 16802 3.4313195 5.486612 0 1
1   2 16802  6.981278 5.486612 1 0
1   3 16802         . 5.486612 . .
1   4 16802         . 5.486612 . .
1   5 16802         . 5.486612 . .
1   6 16802         . 5.486612 . .
1   7 16802  8.563093 5.486612 1 0
1   8 16802         . 5.486612 . .
1   9 16802         . 5.486612 . .
1  10 16802  7.770547 5.486612 1 0
1  11 16802         . 5.486612 . .
1  12 16802         . 5.486612 . .
1  13 16802         . 5.486612 . .
1  14 16802  7.977136 5.486612 1 0
1  15 16802         . 5.486612 . .
1  16 16802  6.994602 5.486612 1 0
1  17 16802 4.5341887 5.486612 0 1
1  18 16802         . 5.486612 . .
1  19 16802         . 5.486612 . .
1  20 16802   3.94336 5.486612 0 1
1  21 16802         . 5.486612 . .
1  22 16802         . 5.486612 . .
1  23 16802         . 5.486612 . .
1  24 16802         . 5.486612 . .
1  25 16802         . 5.486612 . .
1  26 16802  3.907271 5.486612 0 1
1  27 16802         . 5.486612 . .
1  28 16802         . 5.486612 . .
1  29 16802 3.7624195 5.486612 0 1
1  30 16802         . 5.486612 . .
1  31 16802         . 5.486612 . .
1  32 16802         . 5.486612 . .
1  33 16802         . 5.486612 . .
1  34 16802         . 5.486612 . .
1  35 16802         . 5.486612 . .
1  36 16802         . 5.486612 . .
1  37 16802         . 5.486612 . .
1  38 16802         . 5.486612 . .
1  39 16802         . 5.486612 . .
1  40 16802         . 5.486612 . .
1  41 16802         . 5.486612 . .
1  42 16802         . 5.486612 . .
1  43 16802         . 5.486612 . .
1  44 16802         . 5.486612 . .
1  45 16802         . 5.486612 . .
1  46 16802         . 5.486612 . .
1  47 16802         . 5.486612 . .
1  48 16802         . 5.486612 . .
1  49 16802         . 5.486612 . .
1  50 16802         . 5.486612 . .
1  51 16802         . 5.486612 . .
1  52 16802         . 5.486612 . .
1  53 16802         . 5.486612 . .
1  54 16802  8.679188 5.486612 1 0
1  55 16802         . 5.486612 . .
1  56 16802         . 5.486612 . .
1  57 16802  8.707003 5.486612 1 0
1  58 16802         . 5.486612 . .
1  59 16802  8.579934 5.486612 1 0
1  60 16802         . 5.486612 . .
1  61 16802         . 5.486612 . .
1  62 16802         . 5.486612 . .
1  63 16802         . 5.486612 . .
1  64 16802 3.4701564 5.486612 0 1
1  65 16802         . 5.486612 . .
1  66 16802         . 5.486612 . .
1  67 16802  3.168994 5.486612 0 1
1  68 16802         . 5.486612 . .
1  69 16802         . 5.486612 . .
1  70 16802         . 5.486612 . .
1  71 16802         . 5.486612 . .
1  72 16802         . 5.486612 . .
1  73 16802         . 5.486612 . .
1  74 16802         . 5.486612 . .
1  75 16802         . 5.486612 . .
1  76 16802         . 5.486612 . .
1  77 16802         . 5.486612 . .
1  78 16802         . 5.486612 . .
1  79 16802         . 5.486612 . .
1  80 16802         . 5.486612 . .
1  81 16802         . 5.486612 . .
1  82 16802         . 5.486612 . .
1  83 16802         . 5.486612 . .
1  84 16802         . 5.486612 . .
1  85 16802         . 5.486612 . .
1  86 16802         . 5.486612 . .
1  87 16802         . 5.486612 . .
1  88 16802 2.7816935 5.486612 0 1
1  89 16802 2.7989986 5.486612 0 1
1  90 16802         . 5.486612 . .
1  91 16802         . 5.486612 . .
1  92 16802         . 5.486612 . .
1  93 16802         . 5.486612 . .
1  94 16802  2.790924 5.486612 0 1
1  95 16802 3.9475884 5.486612 0 1
1  96 16802         . 5.486612 . .
1  97 16802         . 5.486612 . .
1  98 16802         . 5.486612 . .
1  99 16802         . 5.486612 . .
1 100 16802         . 5.486612 . .
end
format %tdnn/dd/CCYY date

[Nick Cox]

As you've used tsspell (SSC, as you are asked to explain) you've tsset your data. So you can go

gen WW = (W == 1) & (F.W == 1)

and so on.

(You're right that tsspell is not the best to proceed here.)

[Thijs Kroon]

Thank you for your fast reply! But I'm still not able to generate the WW variable correctly. If I want to use tsspell (or tsset my data) the following messages appears: “repeated time values in sample”. I suppose this message pops up because I use the tsset time command earlier to tell Stata my return data is a time series. If I use the code you provided me it generates WW not only for a fund winning in two consecutive months, but for all observations when a fund is a winner. It replicates the W dummy. Is there any way to solve this or another way to generate WW without using tsspell? I could provide you my do file if this makes things more clear. Thanks for helping me out!

[Thijs Kroon]

I did still not manage to create the variable I need. It seems like an easy code to write but I’m totally stuck.. Hope someone could help me with this. Your help would really be appreciated!

[Nick Cox]

We want you to show code you tried. I assumed (wrongly) from #1 that you had successfully tsset: it was just that tsspell didn't do what you want directly (which -- as its author -- I confirmed). Manifestly

tsset time

won't work because you have panel data and you need to specify a panel identifier too. If you had shown your code and the results of your code in #1 you would have got better advice.

You need to show a data example that is relevant to the question. In what you show us time is always 1 and it's not clear what is the panel identifier: is it _j? As time is constant in the example and if each panel identifier occurs just once there aren't any full panels to test the code on.

[Thijs Kroon]

* Example generated by -dataex-. To install: ssc install dataex
clear
input long time int(_j date) double(return MktRF RF) float(excessReturn median_excessReturn W L WW LL)
 1 1 16802  3.8161124748939983   6.570201347971683  .3847929295070154  3.4313195   5.486612 0 1 0 1
 2 1 16833    2.23559759243336   .5186172982354644 .32139427249059893  1.9142033  2.6828074 0 1 0 0
 3 1 16861   3.721614802354912  4.0563810082554586 .39101244604618035  3.3306024  3.1608124 1 0 0 0
 4 1 16892   .7399148591121062   5.510018985513227 .41658013124767823   .3233347    .676596 0 1 0 0
 5 1 16922   -5.28222155146393  -3.681738716963189  .4526084716883938   -5.73483  -5.858014 1 0 1 0
 6 1 16953   .7754408328022138 -.21095529723825868  .3983070681853642   .3771338  .34246135 1 0 1 0
 7 1 16983  1.1594813955939647   .9906268306971295  .3934387814879905   .7660426  .08085534 1 0 1 0
 8 1 17014   3.178076482234028  2.6875687659407497 .42679772415146155   2.751279   2.495441 1 0 1 0
 9 1 17045  1.4845485760452566   .6623044480024993 .40350255789432565   1.081046  1.0302596 1 0 1 0
10 1 17075   4.975619464623345   3.810545725607125 .41309780689709275  4.5625215   3.397039 1 0 1 0
11 1 17106  1.8579960185799542  3.9593756116656884  .4825922881190057  1.3754038   1.205093 1 0 1 0
12 1 17136   4.653327128897161   3.261843575418995  .3843575418994414  4.2689695   3.600285 1 0 0 0
13 1 17167   2.285460204535343   .5899764911083305 .42209077465589817  1.8633695   2.325311 0 1 0 0
14 1 17198   .7390019127108403  -.4844388558264542 .39504780188135924   .3439541 -1.7739447 1 0 1 0
15 1 17226   6.429619401052906  3.7749743628698393 .45281134870295103   5.976808  2.8461645 1 0 0 0
16 1 17257   2.578657152124548  5.6485558338629485  .4639022019514747   2.114755   2.840613 0 1 0 1
17 1 17287  1.9288537549407097  1.5264854925900049  .3915408377155887   1.537313  2.1092439 0 1 0 1
18 1 17318  -3.691639522258408 -.14685397026493285 .42191004955844535 -4.1135497 -2.0562878 0 1 0 0
19 1 17348  -3.639877597036568  -2.224207359577356  .4048281175477857  -4.044706 -4.3916793 1 0 1 0
20 1 17379  2.0391108139729335 -2.0848590545135464  .4133026214763629  1.6258082  -.3022934 1 0 0 0
21 1 17410 -.40131040131040874   5.090026449195503  .3812431121886708  -.7825535   .2404101 0 1 0 1
22 1 17440  1.0032069731107631   4.411987361769352 .33779462875197486   .6654124    1.12562 0 1 0 1
23 1 17471  -5.169746804526577  -4.087175863382868  .3607946239911324  -5.530541  -4.408822 0 1 0 0
24 1 17501  -.4120879120879155  -2.117096565132858  .2667077122488659  -.6787956 -1.3607997 1 0 0 0
25 1 17532  -9.586206896551728   -10.1243459526008 .22671762251632965  -9.812924  -9.406405 0 1 0 1
26 1 17563  -1.859267734553765   2.025429246078597  .1588932366334963  -2.018161 -1.3187075 0 1 0 1
27 1 17592  -2.137374914990773    .302916488504424  .2003246390158866 -2.3376997   -.880037 0 1 0 1
28 1 17623   2.670505311228033   4.369607258504055 .16902888653778314  2.5014765   3.455038 0 1 0 1
29 1 17653  -.3577644556178733   .8593395035759361 .18595708876735384 -.54372156  1.1860554 0 1 0 0
30 1 17684    -8.7627365356623  -8.801919374054158 .18702804520719973  -8.949764   -9.29311 1 0 1 0
 1 2 16802   7.366071428571433   6.570201347971683  .3847929295070154   6.981278   5.486612 1 0 1 0
 2 2 16833  7.2279972279972124   .5186172982354644 .32139427249059893   6.906603  2.6828074 1 0 1 0
 3 2 16861   3.561041814774135  4.0563810082554586 .39101244604618035  3.1700294  3.1608124 1 0 0 0
 4 2 16892 -1.7661008487269172   5.510018985513227 .41658013124767823  -2.182681    .676596 0 1 0 0
 5 2 16922  -5.228384473667487  -3.681738716963189  .4526084716883938  -5.680993  -5.858014 1 0 1 0
 6 2 16953  1.7026411047057193 -.21095529723825868  .3983070681853642   1.304334  .34246135 1 0 1 0
 7 2 16983   5.048774057474292   .9906268306971295  .3934387814879905  4.6553354  .08085534 1 0 0 0
 8 2 17014  2.1583636591793183  2.6875687659407497 .42679772415146155   1.731566   2.495441 0 1 0 0
 9 2 17045   5.410883183884045   .6623044480024993 .40350255789432565    5.00738  1.0302596 1 0 1 0
10 2 17075  4.8651168210685745   3.810545725607125 .41309780689709275   4.452019   3.397039 1 0 1 0
11 2 17106   3.622624736081793  3.9593756116656884  .4825922881190057  3.1400325   1.205093 1 0 1 0
12 2 17136    8.49865951742627   3.261843575418995  .3843575418994414   8.114302   3.600285 1 0 0 0
13 2 17167  1.5616506053867045   .5899764911083305 .42209077465589817  1.1395599   2.325311 0 1 0 0
14 2 17198 -.41847112062672637  -.4844388558264542 .39504780188135924  -.8135189 -1.7739447 1 0 0 0
15 2 17226   2.623992181773762  3.7749743628698393 .45281134870295103  2.1711807  2.8461645 0 1 0 1
16 2 17257  -2.147414531949347  5.6485558338629485  .4639022019514747  -2.611317   2.840613 0 1 0 1
17 2 17287  .09731886526203934  1.5264854925900049  .3915408377155887 -.29422197  2.1092439 0 1 0 1
18 2 17318  -9.362695056146999 -.14685397026493285 .42191004955844535  -9.784605 -2.0562878 0 1 0 1
19 2 17348  -7.578439259855201  -2.224207359577356  .4048281175477857  -7.983267 -4.3916793 0 1 0 0
20 2 17379  .13347260909936062 -2.0848590545135464  .4133026214763629    -.27983  -.3022934 1 0 0 0
21 2 17410  -6.102578962619531   5.090026449195503  .3812431121886708  -6.483822   .2404101 0 1 0 1
22 2 17440 -4.5549932107147235   4.411987361769352 .33779462875197486  -4.892788    1.12562 0 1 0 1
23 2 17471 -11.032074495602691  -4.087175863382868  .3607946239911324  -11.39287  -4.408822 0 1 0 1
24 2 17501   -3.93225759558075  -2.117096565132858  .2667077122488659  -4.198965 -1.3607997 0 1 0 0
25 2 17532 -1.1576000605281085   -10.1243459526008 .22671762251632965 -1.3843176  -9.406405 1 0 1 0
26 2 17563  -1.140538885486841   2.025429246078597  .1588932366334963  -1.299432 -1.3187075 1 0 1 0
27 2 17592  -.6658923732094568    .302916488504424  .2003246390158866   -.866217   -.880037 1 0 0 0
28 2 17623 -3.3206017616337915   4.369607258504055 .16902888653778314  -3.489631   3.455038 0 1 0 1
29 2 17653  -4.563412077723129   .8593395035759361 .18595708876735384  -4.749369  1.1860554 0 1 0 1
30 2 17684 -10.602348568049335  -8.801919374054158 .18702804520719973 -10.789376   -9.29311 0 1 0 1
 1 3 16802                   .   6.570201347971683  .3847929295070154          .   5.486612 . . 0 0
 2 3 16833                   .   .5186172982354644 .32139427249059893          .  2.6828074 . . 0 0
 3 3 16861                   .  4.0563810082554586 .39101244604618035          .  3.1608124 . . 0 0
 4 3 16892                   .   5.510018985513227 .41658013124767823          .    .676596 . . 0 0
 5 3 16922                   .  -3.681738716963189  .4526084716883938          .  -5.858014 . . 0 0
 6 3 16953                   . -.21095529723825868  .3983070681853642          .  .34246135 . . 0 0
 7 3 16983                   .   .9906268306971295  .3934387814879905          .  .08085534 . . 0 0
 8 3 17014                   .  2.6875687659407497 .42679772415146155          .   2.495441 . . 0 0
 9 3 17045                   .   .6623044480024993 .40350255789432565          .  1.0302596 . . 0 0
10 3 17075                   .   3.810545725607125 .41309780689709275          .   3.397039 . . 0 0
11 3 17106                   .  3.9593756116656884  .4825922881190057          .   1.205093 . . 0 0
12 3 17136                   .   3.261843575418995  .3843575418994414          .   3.600285 . . 0 0
13 3 17167                   .   .5899764911083305 .42209077465589817          .   2.325311 . . 0 0
14 3 17198                   .  -.4844388558264542 .39504780188135924          . -1.7739447 . . 0 0
15 3 17226                   .  3.7749743628698393 .45281134870295103          .  2.8461645 . . 0 0
16 3 17257  -3.296703296703308  5.6485558338629485  .4639022019514747 -3.7606056   2.840613 0 1 0 1
17 3 17287 -1.9834710743801671  1.5264854925900049  .3915408377155887  -2.375012  2.1092439 0 1 0 1
18 3 17318  -6.903456998313656 -.14685397026493285 .42191004955844535  -7.325367 -2.0562878 0 1 0 1
19 3 17348  -6.045511151364207  -2.224207359577356  .4048281175477857  -6.450339 -4.3916793 0 1 0 0
20 3 17379   .6024822267743102 -2.0848590545135464  .4133026214763629   .1891796  -.3022934 1 0 0 0
21 3 17410 -4.2400287459575905   5.090026449195503  .3812431121886708  -4.621272   .2404101 0 1 0 1
22 3 17440  -4.602876797998758   4.411987361769352 .33779462875197486  -4.940671    1.12562 0 1 0 1
23 3 17471  -8.771469778418773  -4.087175863382868  .3607946239911324  -9.132264  -4.408822 0 1 0 1
24 3 17501  -5.878125898246628  -2.117096565132858  .2667077122488659  -6.144834 -1.3607997 0 1 0 0
25 3 17532 -3.2676744541151224   -10.1243459526008 .22671762251632965  -3.494392  -9.406405 1 0 1 0
26 3 17563  1.8153117600631388   2.025429246078597  .1588932366334963  1.6564186 -1.3187075 1 0 1 0
27 3 17592   1.565891472868225    .302916488504424  .2003246390158866   1.365567   -.880037 1 0 0 0
28 3 17623  -4.106243321630292   4.369607258504055 .16902888653778314 -4.2752724   3.455038 0 1 0 1
29 3 17653  -3.104106972301808   .8593395035759361 .18595708876735384  -3.290064  1.1860554 0 1 0 0
30 3 17684  -7.819944143256127  -8.801919374054158 .18702804520719973  -8.006972   -9.29311 1 0 0 0
end
format %tdnn/dd/CCYY date

I’m sorry for not being clear enough. I hope the following write up will make it more understandable. So my dataset contains return data. In the data example I only included 3 funds over a time period of 30 months but my total data sample includes 254 fund over a time period of 144 months. Not all fund enter the data set in time=1 and not all fund survive until time=144. Since my dataset is downloaded as wide I first reshape it in long format:

 gen long time = _n
reshape long return, i(time)j(_j)

After this I indicate that I use panel data. _j is the fund identifier and time is the month identifier with time = 1 being the first return (return January 2006)

xtset _j time

Since I want to perform a Carhart (1997) performance test I will generate a new variable excessReturn in stead of normal return.

 gen excessReturn = return – RF

To check for the significance of the intercept I will use a newey2 regression to correct for autocorrelation and heteroskedasticity. The optimal number of lags is obtained by the use of ivreg2. (I did not manage to find a code to use this command in panel data so I took a conservative amount of lags by running a few separate time series regressions).

 newey2 excessReturn MktRF  _j time, lag(20)

Now for the problem I opened this post I will first need to construct a dummy variable indication when a fund is a loser, L, and when a firm is a winner, W. A firm is a winner if it has a excessReturn above the median and a loser if it has a excessReturn below the median. For this I will first need to generate the median excessReturn for each point in time.

 foreach v in excessReturn {
egen median_`v' = median(`v'), by (time)
}

Now I can generate the dummy variable W & L

 gen W = (excessReturn > median_excessReturn) if !missing(excessReturn)
gen L = (excessReturn < median_excessReturn) if !missing(excessReturn)

And now I come to the point I got stuck. I need to generate a dummy WW if a funds is winner in t and t+1 and this dummy should only occur at time t+1. So to make it more clear:

time      _j            W            L              WW       LL
1             1             1              0             0             0
2             1             1              0             1             0
3             1             1              0             1             0
4             1             0              1             0             0
5             1             0              1             0             1
6             1             1              0             0             0
7             1             0              1             0             0

The code you so kindly provided me with

 gen WW = (W == 1) & (F.W == 1)

does indeed generate a new variable WW but it does generate it for t while it should only occur at t+1 if t=W and t+1 =W. And it does not create it for t+1 but only for t if we are in a situation of W – W (see time = 14/15 and _j=1). As a follow up, is there an easy command to count the maximum number of consecutive occurrences of WW?

I hope this makes it more clear and hope you could help me. Thanks in advance and again apologize for the unclear start.