Sorting 2000 variables as descending values

[Aleksander Lind]

Hi there.

I have a dataset with 2001 variables, where I want to sort each of variables 2 to 2000 in a descending order. I have tried gsort, but it sorts all variables by the 2nd variable.
The dataset is a merge of 569 t-statistics from 2000 datasets, and I want the t-statistics in each variable to be descending. Hence, Y=1,...,2000 and X=1,...,569, and I want X to be descending in each Y.

How can I do that?

Thank you.

[Joro Kolev]

If I understand correctly, the observation number has no meaning in this dataset.

You should do the sorting before you merge.

Once you merge them, "detaching" the variables from one another becomes probably impossible.

Another possibility is that you do not fiddle with your data, but you do the sorting before you do something with the 1st variable, then you are done, you sort by the second and then you do what you have to do with the second, and you are done, then you sort the 3rd, etc.

[Nick Cox]

I would reshape long and then you have fewer variables to worry about. Otherwise put, why merge rather than append?

[William Lisowski]

The following example uses 20 observations of 5 variables and an identifier rather than 569 observations of 2000 variables and an identifier. I think I understand what you are seeking. Like Joro, I note that the identifier, whch I call crsp_fundno based on your previous topic, is meaningless once the variables are sorted, and indeed must be discarded, since each observation after sorting will have values from different observations in the original dataset.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(crsp_fundno t1 t2 t3 t4 t5)
100    .059 -1.4144  -.0488  -.3415  -.3808
101 -1.0222  -.0227  -.0298  -.2216    .618
102   -.377   .1949   .4431  -.0101  -.2606
103  1.0908  -.4614   .8798 -2.4778   .8323
104  -.1807 -2.6924   .8097   .6347   .2721
105 -1.0857 -1.0345  -.6125   .2132 -1.2494
106   -.576  -.0281   .4111 -1.1425 -1.3091
107  -.0099  -.3558    .229    .017 -1.6603
108  1.6304  1.7682  -.4885  -.7644   .6374
109   .3081  -.8227    .638   1.541  -1.008
210  1.3246  1.5077   .8016 -1.4095     .11
211  -.7546  1.8251   .2852  -1.989 -1.3213
212   .0847   .1531   .4048  1.4475   .0795
213 -1.9447   -.408 -1.5936  1.6366 -1.0939
214  1.2036   .2479   .7547  -.3015  2.1146
215  -.7906     .58    .599  1.9064   .0024
216    .289    .162  -.3377  -.2391   .9486
217   .9165   .2171 -1.2435   .0145   .8849
218   .4984  -.8414  -.4909   .8466  -.5014
219   1.278   1.049  -.7722  -.5958   .1585
end
generate obsno = _n
reshape long t, i(obsno) j(varno)
drop obsno
generate mt = -t
bysort varno (mt): generate obsno = _n
drop mt
drop crsp_fundno
reshape wide t, i(obsno) j(varno)
drop obsno
list, clean

. list, clean

            t1        t2        t3        t4        t5  
  1.    1.6304    1.8251     .8798    1.9064    2.1146  
  2.    1.3246    1.7682     .8097    1.6366     .9486  
  3.     1.278    1.5077     .8016     1.541     .8849  
  4.    1.2036     1.049     .7547    1.4475     .8323  
  5.    1.0908       .58      .638     .8466     .6374  
  6.     .9165     .2479      .599     .6347      .618  
  7.     .4984     .2171     .4431     .2132     .2721  
  8.     .3081     .1949     .4111      .017     .1585  
  9.      .289      .162     .4048     .0145       .11  
 10.     .0847     .1531     .2852    -.0101     .0795  
 11.      .059    -.0227      .229    -.2216     .0024  
 12.    -.0099    -.0281    -.0298    -.2391    -.2606  
 13.    -.1807    -.3558    -.0488    -.3015    -.3808  
 14.     -.377     -.408    -.3377    -.3415    -.5014  
 15.     -.576    -.4614    -.4885    -.5958    -1.008  
 16.    -.7546    -.8227    -.4909    -.7644   -1.0939  
 17.    -.7906    -.8414    -.6125   -1.1425   -1.2494  
 18.   -1.0222   -1.0345    -.7722   -1.4095   -1.3091  
 19.   -1.0857   -1.4144   -1.2435    -1.989   -1.3213  
 20.   -1.9447   -2.6924   -1.5936   -2.4778   -1.6603

This would have been easier to write had you provided example data of the sort I had to create to write and test this code.

Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Nick Cox]

See also fixsort from SSC.

[Aleksander Lind]

Thank you for the responses. My dataset looks like the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(crsp_fundno BS_t_alpha_1 BS_t_alpha_2 BS_t_alpha_3)
  1 -4.7292647  -5.259379  -5.213035
  2 -1.5519545  -4.105025 -2.2839732
  3 -1.3248347 -2.2488506  -.6400933
  4 -1.4612584 -2.0144663 -1.5948423
  5  -.6909253 -2.0825512 -1.6703316
  6 -1.4984378  -3.409062  -1.881313
  7 -1.6518368 -1.1460838   -.763563
  8  -1.782926 -3.7627256 -2.9061866
  9 -1.4259276  -.9041228  -.7667957
 10  -.7101653 -1.7733793  -2.072814
 11 -1.5494735 -1.4509118 -1.5739057
 12  -1.308266  -2.756132  -2.014613
 13 -1.5684488 -1.9917005   -.962113
 14  -.8946536  -1.736099  -1.977289
 15 -1.0372976 -1.0518152 -2.3063726
 16  -1.481559  -1.895621 -2.3376744
 17  -1.613089 -1.3980983  -1.634999
 18  -1.570538  -1.921176 -1.7369118
 19 -1.3869177 -1.6046526 -1.2423947
 20 -2.1212637 -1.3824284 -1.5856946

William Lisowski I have tried implementing the code you provided:

generate obsno = _n
reshape long BS_t_alpha, i(obsno) j(varno)
drop obsno 
generate mt = -t
bysort varno (mt): generate obsno = _n
drop mt
drop crsp_fundno
reshape wide t, i(obsno) j(varno)
drop obsno
list, clean

However, Stata returns the following:

variable varno contains all missing values.

Any suggestion for why this might be? I am not familiar with reshape.

[William Lisowski]

You should become familiar with reshape, it is an important tool. When you looked at the output of help reshape as you adapted my code to your variables, did you notice that the arguments to the reshape commands were the stubs of variable names that differ only in the number at the end? By not including the underscore before the final number, your first reshape command threw an error because it could not convert the trailing _1_2 etc. to a numeric variable.

generate obsno = _n
reshape long BS_t_alpha_, i(obsno) j(varno)
drop obsno
generate mt = -BS_t_alpha_
bysort varno (mt): generate obsno = _n
drop mt
drop crsp_fundno
reshape wide BS_t_alpha_, i(obsno) j(varno)
drop obsno
list, clean abbreviate(16)

. list, clean abbreviate(16)

       BS_t_alpha_1   BS_t_alpha_2   BS_t_alpha_3  
  1.      -.6909253      -.9041228      -.6400933  
  2.      -.7101653      -1.051815       -.763563  
  3.      -.8946536      -1.146084      -.7667957  
  4.      -1.037298      -1.382428       -.962113  
  5.      -1.308266      -1.398098      -1.242395  
  6.      -1.324835      -1.450912      -1.573906  
  7.      -1.386918      -1.604653      -1.585695  
  8.      -1.425928      -1.736099      -1.594842  
  9.      -1.461258      -1.773379      -1.634999  
 10.      -1.481559      -1.895621      -1.670332  
 11.      -1.498438      -1.921176      -1.736912  
 12.      -1.549474      -1.991701      -1.881313  
 13.      -1.551955      -2.014466      -1.977289  
 14.      -1.568449      -2.082551      -2.014613  
 15.      -1.570538      -2.248851      -2.072814  
 16.      -1.613089      -2.756132      -2.283973  
 17.      -1.651837      -3.409062      -2.306373  
 18.      -1.782926      -3.762726      -2.337674  
 19.      -2.121264      -4.105025      -2.906187  
 20.      -4.729265      -5.259379      -5.213035