5x5 sorting by two variables (sequential)

[Christian Rumpelnik]

Dear Statalist experts,

I have a dataset that includes data about asset returns (dependent variable), market capitalization and book-to-market ratio (sorting variables) and would like to double sort the data into 5x5 (25) portfolios, by both market capitalization and btm ratio.

So far I have been using the xtile command, but this command only allows for univariate sorting.

Here an example of my data:

---

* Example generated by -dataex-. To install: ssc install dataex
clear
input float time long asset_id double marketcap float(bookmarket return_excess)
471 1   285.75  .4291846           .
472 1  5168.92 .40983605   .04476772
473 1   3367.1  .4484305   -.0837072
474 1  2501.73  .4385965   .01930092
475 1  8231.77  .3690037    .1877892
476 1  5291.55  .4464286  -.06938182
477 1   499.76  .3558719   .25501707
478 1   273.52  .4255319   -.1661615
479 1   301.78  .4237288  .004904651
480 1   1342.5  .4166667   .01655045
481 1  2176.93  .4032258   .03320909
482 1  2575.69  .4545455  -.11630896
483 1  5878.48  .4347826   .04878993
484 1  7909.11  .3816794   .13540544
485 1   1792.2  .3937008 -.031833332
486 1     3713  .4524887  -.12963003
487 1  1984.67  .4081633   .11058916
488 1   423.47  .4830918  -.05583778
489 1  1156.14  .4651163   .03487463
490 1   275.56  .4065041   .14734545
491 1   168.48  .3875969   .04839626
492 1 12575.48  .3194888   .21062507
493 1  2753.51  .2739726   .16761035
494 1   307.03 .27027026  .011552137
495 1   299.92 .27247956 -.007591764
496 1 13171.23 .28735632  -.05073192
497 1  9425.54 .24390244   .17571037
498 1  3957.96 .20491803   .19094335
499 1  5763.44  .2079002      -.0141
500 1  7762.84  .2793296  -.08985518
501 1   4523.1 .27855152  .004701961
502 1  2730.92 .23148148   .20167384
503 1   178.39 .24570024  -.05914059
504 1  6943.92 .23419204   .05098039
505 1  4069.29     .3125        -.25
506 1 14634.06  .3012048  .036318406
507 1  6507.79  .3030303 -.006240998
508 1  6050.19  .3636364  -.16763285
509 1   163.16  .3205128   .13755079
510 1  9144.04  .3048781   .05102041
511 1   141.29   .310559 -.019417476
512 1  2229.94 .44444445  -.07475247
513 1   7728.9  .3508772   .27126807
514 1  3709.55  .3472222  .007996633
515 1  7904.25  .3144654   .10480167
516 1   734.66 .29154518   .08049887
517 1   355.65 .26595744   .09341728
518 1  7844.43  .2469136     .079307
519 1  7865.13  .2352941   .04771784
520 1  7867.32 .23419204  .005374823
521 1  6238.22 .23364486  .002813731
522 1   608.78  .2159827     .082211
523 1  1741.57  .2096436   .02929738
524 1   112.79 .29411766  -.04382871
525 1   174.26 .27855152     .056902
526 1  7135.76 .28089887 -.009471585
527 1  6813.43  .3021148  -.06844489
528 1  7706.05 .25906736    .1647758
529 1  3155.08  .2375297   .08998144
530 1   534.91  .2352941  .009361702
531 1  8077.29 .24752475  -.04932546
532 1    152.9  .2247191   .10266075
533 1   958.94 .20242915   .10999396
534 1  7945.29 .18214937   .11195652
535 1   770.12 .17421603   .04463995
536 1  3599.46  .2173913  .007485964
537 1  2054.44  .1953125    .1133127
538 1  4085.73  .2053388  -.04866518
539 1 12524.57 .23041475   -.1103017
540 1   188.43  .2105263    .0954979
541 1   176.36  .1968504   .06856567
542 1   169.35  .1930502   .02010202
543 1  4666.98  .1858736   .03812882
544 1  6435.68 .18248175   .01960265
545 1   166.34  .1883239 -.031956352
546 1  7536.49  .1669449   .12765093
547 1  7986.76  .1644737  .015944788
548 1  1525.12  .3164557  .027406886
549 1  5724.81 .29239765   .08116735
550 1   161.33 .25575447   .14192323
551 1 13582.17 .24570024   .04247461
552 1 11601.29  .2747253   -.1064659
553 1     5267 .28985506   -.0519429
554 1   323.76  .3021148  -.04035968
555 1  7676.19  .2967359  .018422553
556 1  8190.82  .3134796  -.05477107
557 1  5536.35  .3012048   .04119511
558 1   395.03 .29498526   .02108696
559 1  3038.12  .3184713  -.07408985
560 1   325.35  .3289474   .14706026
561 1  3940.67  .3215434   .02254983
562 1   354.75  .3225806 -.005000039
563 1  1055.16  .3205128  .008369569
564 1   296.56 .29239765     .096775
565 1 10980.75  .3095975  -.05636781
566 1  3042.04  .3144654 -.017458491
567 1   7025.7 .24752475   .27217343
568 1  1123.61 .23980816  .031291485
569 1   7980.3  .2114165   .13569163
570 1  1930.54 .21097046 .0011883278
end
format %tm time
label values asset_id asset_id
label def asset_id 1 "AA", modify

---

Thanks in advance and sorry for the finance language; I am not sure how to put it differently,

Christian

[Clyde Schechter]

It isn't clear to me whether you want to do this separately for each month, or, if not, how the time variable plays into this. In any case, since you say you have been able to use -xtile- to do this for a single variable, the answer is easy. Use -xtile- twice, generating quintiles for bookmarket the first time and for marketcap the second. Let's call those two variables quint_bookmarket and quint_marketcap. Then to get the portfolios it's just:

egen byte portfolio = group(quint_bookmarket quint_marketcap), label

[Christian Rumpelnik]

Thanks a lot! The portfolios should be resorted each month; but from what I can see from the outcome this works with the double xtile and your command!