Constructing 2x2 sorts with STATA

[Thomas Jansen]

Hi all,

I have this dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double fyear long gvkey float Re double at
2000  29194  -.18969555       7579
1999  29194  -.10294118       7140
2011 201336    .3377265   2372.034
2010 201336    .2873807   1740.242
2014  29194    .1223898   3081.721
2010  29194     .479786   3012.535
2008 201336   -.3052828     685.67
2009 201336   -.3654105   1577.141
2007 201336    .5821006    347.139
2012 201336  -.55849755   2162.731
1997  14802    .4586288     146428
2012 100243    -.057179  11602.097
2001  14802  -.08021621 185336.512
1999  14802   .23149113 165864.077
1996  14802    .1915493     137439
2013 100243    .2771586   11384.03
2011 100165 -.069070406  16210.931
1995  14802   .16013072     111117
2011 100243    .4304338  11723.959
2007 100243    .2166247   7762.727
2004 100243  -.12072893   7151.105
2008  14802   -.8910908  519133.93
1994  17525   .22047244   4100.553
2005 100243   .06217616   7540.982
2010 100243    .3357501    9591.05
2003  14802   .15052934 270161.039
2002  14802    .4210939 204883.402
2004  14802  -.06067286  297952.24
2009 100165   .14549153  10201.042
1994  14802 -.006493506  93153.375
2015  14802  -.26058862 670446.504
2014 100243    .0606028   9141.645
2009  14802     .418635 577195.488
2006 100243 -.031707317   7538.428
1997  17525   .21084337   4782.111
2013  14802   .21066134 755232.503
2011  14802   -.1138735 734460.821
2013 100165   .13580354  18401.776
2012  14802   .21916106 792697.892
1998  14802   -.2228525 149266.402
2010  14802  -.09618006 661257.728
2014  14802  -.55635536 771740.084
2000  14802  -.06011854 186101.096
1995  17525  .032258064   4242.977
1996  17525       .0375    4759.94
2014 100165   -.4842234  18262.569
2005  14802   .28214285 320691.108
2010 100165   -.7340791  13936.982
2012 100165  -.08160663  17704.476
2014 177925   .12172145     2432.7
2015 241637   .11289174     134635
2015 177925  -.02405039     2589.9
2014 241637   .05504415     142550
2013 177925    .4328885     2392.8
2015 153130   .10041104  19981.891
2015 177983    .3195985   2793.489
2014 100424    .1651692      66457
2014 165262   .06871345   1378.095
2013 175688    .1381295     5211.6
2013 177983    .1851613   2689.367
2014 153130  .073996216  19955.736
2012 177983  .036789298   2605.927
2014 138861  -.18133686   1668.318
2012 100424    .3177471      63460
2009  65500  -.11111111    946.578
2005 100424     .081761      18440
2006 138861   .50126904   1751.663
2012 178552   .08035714    3476.08
2011 138861  -.42494226   1930.869
2015 138861  -.23201293   1509.475
2014 178552  -.14669318   4358.977
2014 177983  .030484486   2742.537
2007 100424   .29439253      22213
2007 138861   .21555367   1988.437
2013 162387   .13049233   4219.594
2014 162387  .006789979   3964.418
2014 175688  .024020227     3579.4
2008  65500   .02833638    813.878
2011 100424   .06931818      58297
2010 100424   .45791915      48076
2010  65500  -.13266666   1083.083
2015 165262   .10670315   1423.805
2010 138861   .28106508   2137.714
2010  64355    .6794872    541.953
2007 152249   .14078732   7270.727
2012 138861    .5649264   1892.027
2015 152249  .069351606  13241.525
2013 138861   .29255775   1879.866
2009 100424    .6313514      39127
2009 138861    .3237598    2072.69
2013 178552   .27844882   3908.983
2013 153130    .3732679  19634.784
2013 100424  -.15629032      63835
2015  65500   .54319763   1244.626
2015 100424   -.2004922      66955
2005 138861    .5323286   1415.562
2014 205942   .11883086  13591.739
2010 222111  -.20637584     308683
2005 222111    .7916038      78625
2008 209382   -.6293235   80147.11
end

What I want to do is construct groups (portfolios) based on 2x2 sorts, based on two Re groups and two groups of the second sorting variable at.

Does someone know how to construct this? If so, I would be very glad with some helping hand.

[William Lisowski]

As stated, your question assumes the reader understands what you mean by

construct groups (portfolios) based on 2x2 sorts, based on two Re groups and two groups of the second sorting variable at.

I'll be the first to admit that that phrasing - especially "based on 2x2 sorts" - doesn't convey anything to me. Perhaps you've used technical jargon from the field you're working in to describe what you want. Nothing wrong with that, but it limits the pool of potential respondents to those who are familiar with the jargon. An explanation of what you want in more general terms might increase the likelihood of a response. An example of doing so would be particularly helpful.

[Thomas Jansen]

As stated, your question assumes the reader understands what you mean by



I'll be the first to admit that that phrasing - especially "based on 2x2 sorts" - doesn't convey anything to me. Perhaps you've used technical jargon from the field you're working in to describe what you want. Nothing wrong with that, but it limits the pool of potential respondents to those who are familiar with the jargon. An explanation of what you want in more general terms might increase the likelihood of a response. An example of doing so would be particularly helpful.

Hi, excuse me for being unclear. I meant that I would like to construct portfolios (groups) of stocks (gvkey) PER YEAR which are first split up into two groups based on the variable Re. After forming the two groups I want to further sort (group) the two groups by again creating two groups which are now based on At.

Essentially there will be 4 groups (2x2):

SL, SH, BL and BH, where:

S= Small stock (low Re)
B = Big stock (high Re)
L = Low value (low At)
H = High value (High At)

[Nick Cox]

Given some definitions that sounds like

local small = 42
local low = 666
gen group = cond(Re < `small', cond(At < `low', 1, 2), cond(At < `low', 3, 4))
replace group = . if missing(Re, At)
label def group 1 SL 2 SH 3 BL 4 BH
label val group group.

I guess I know even less about finance than William, so supply total guesses for what small and low are.

[William Lisowski]

That takes me further along the path of understanding, and makes sense to me. The answer is fairly straightforward, using the xtile command, perhaps.

Let me try rephrasing what you seek to test my understanding, and to point out some ambiguities.

• separately for each year
• divide the values of Re into two groups
• divide the values of At into two groups
• then any observation falls into one of four groups

How do we divide the values of Re?

• Half into the "small stock" group and half into the "big stock" group?
• And we determine a new dividing line each year?

How do we divide the values of At?

• Half into the "low value" group and half into the "high value" group?
• Do we have
  • a different dividing line for "small stocks" and "big stocks" - in which case each of the four groups will have approximately 25% of the firms?
  • a single dividing line for all stocks in a given year - so there may be more low value small stocks than high value small stocks, for example?
• And again we determine a new dividing line (or pair of dividing lines) each year?

[William Lisowski]

Well, since you provided such tempting data, I couldn't resist answering the question twice: once assuming a single dividing line for At each year, and a second version assuming a different dividing line within each Re group. Starting with the data you very conveniently provided in post #1 (thank you for using dataex) I run the following.

bysort fyear: egen ReDL = median(Re)
bysort fyear: egen AtDL = median(at)
generate str2 cat1 = cond(Re<ReDL,"S","B")+cond(at<AtDL,"L","H")

generate ReGrp = Re<ReDL
bysort fyear ReGrp: egen AtReDL = median(at)
generate str2 cat2 = cond(Re<ReDL,"S","B")+cond(at<AtReDL,"L","H")

. tab fyear cat1, missing

           |                    cat1
     fyear |        BH         BL         SH         SL |     Total
-----------+--------------------------------------------+----------
      1994 |         0          1          1          0 |         2 
      1995 |         1          0          0          1 |         2 
      1996 |         1          0          0          1 |         2 
      1997 |         1          0          0          1 |         2 
      1998 |         0          1          0          0 |         1 
      1999 |         1          0          0          1 |         2 
      2000 |         1          0          0          1 |         2 
      2001 |         0          1          0          0 |         1 
      2002 |         0          1          0          0 |         1 
      2003 |         1          0          0          0 |         1 
      2004 |         1          0          0          1 |         2 
      2005 |         2          1          1          1 |         5 
      2006 |         0          1          1          0 |         2 
      2007 |         2          1          0          2 |         5 
      2008 |         0          2          2          0 |         4 
      2009 |         2          1          1          2 |         6 
      2010 |         2          3          3          2 |        10 
      2011 |         1          2          2          1 |         6 
      2012 |         2          2          2          2 |         8 
      2013 |         3          3          2          3 |        11 
      2014 |         5          3          2          5 |        15 
      2015 |         2          3          3          2 |        10 
-----------+--------------------------------------------+----------
     Total |        28         26         20         26 |       100 


. tab fyear cat2, missing

           |                    cat2
     fyear |        BH         BL         SH         SL |     Total
-----------+--------------------------------------------+----------
      1994 |         0          1          1          0 |         2 
      1995 |         1          0          0          1 |         2 
      1996 |         1          0          1          0 |         2 
      1997 |         1          0          1          0 |         2 
      1998 |         0          1          0          0 |         1 
      1999 |         0          1          1          0 |         2 
      2000 |         1          0          1          0 |         2 
      2001 |         0          1          0          0 |         1 
      2002 |         0          1          0          0 |         1 
      2003 |         1          0          0          0 |         1 
      2004 |         0          1          1          0 |         2 
      2005 |         2          1          1          1 |         5 
      2006 |         1          0          0          1 |         2 
      2007 |         1          2          1          1 |         5 
      2008 |         1          1          1          1 |         4 
      2009 |         2          1          2          1 |         6 
      2010 |         3          2          2          3 |        10 
      2011 |         2          1          2          1 |         6 
      2012 |         2          2          2          2 |         8 
      2013 |         3          3          2          3 |        11 
      2014 |         4          4          4          3 |        15 
      2015 |         2          3          2          3 |        10 
-----------+--------------------------------------------+----------
     Total |        28         26         25         21 |       100