Summary Statistics

[Sartaj Hussain]

I have developed below code to generate time-series average of monthly cross-sectional summary statistics. Before i use it, i want it to be verified first. Moreover, this code can handle one variable at a time. Can it be modified to generate summary statistics for all the given variables in one go. For example, below code generates for rt variable in the data. There are other variables also like size, beta, bmr, etc. Example data file is also appended.

capture program drop my_first_program
program my_first_program
if !missing(rt){
          summarize rt, detail
          gen mean = r(mean)
          gen sd = r(sd)
          gen skew =  r(skewness)
          gen kurt =  r(kurtosis) 
          gen min = r(min)
          gen five = r(p5)
          gen twentyfive = r(p25)
          gen fifty = r(p50)
          gen seventyfive = r(p75)
          gen ninetyfive = r(p95)
          gen max = r(max)
                    }
          list
          end
runby my_first_program, by(mdate)
collapse mean sd skew kurt min five twentyfive fifty seventyfive ninetyfive max, by(mdate)
collapse (mean) mean sd skew kurt min five twentyfive fifty seventyfive ninetyfive max

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock float(mdate rt idiovol beta size bmr mom rev illiq maxi coskew idioskew)
1 "3M India Ltd."                             726    .1184294  .09459936  .7734153  9.960372 .09615385  -.12996833   .02703781 .002441493  .03761568   1.368282   .6790003
1 "3M India Ltd."                             727  -.03988438  .05236398  .7752008 10.074716  .0857633   -.2152363   .12113695 .001953788  .07394652  1.3123722    .627429
1 "3M India Ltd."                             728  -.09855641   .0842787  .7816986 10.036778  .0890472  -.09494337 -.037226338 .004319215 .027859757   1.347158   .6377106
1 "3M India Ltd."                             729   .01961513  .04649255  .7565242  9.936073 .10193679   .06562351   -.0958004 .002738117  .05458176   .8789977  1.0903519
1 "3M India Ltd."                             730    .1622525  .05208552  .7399639  9.958071  .0997009  .007493806    .0222412   .0061781  .02459667   .7867246   .7262015
1 "3M India Ltd."                             731  -.04832564  .07155111   .752092   10.1105 .08561644   -.1782513   .16466135 .004562018  .02994828   .7868449   .6019028
1 "3M India Ltd."                             732  -.08436494 .037118677  .7516811 10.063624 .09191176  -.14356777  -.04579532 .002862106  .03751709    .813815   .6115115
1 "3M India Ltd."                             733    .2041851  .05653918  .7609979  9.978482  .1001001   .06156775  -.08161782 .004022864  .02046493  1.0389457   .6813276
1 "3M India Ltd."                             734    .3066516   .1024171  .7687907 10.166446 .08298755  -.05719534   .20678906 .002653694  .02615827   .9148077   .9854182
1 "3M India Ltd."                             735   -.1565654   .0761481  .7105001 10.435995 .06523157  -.03935781    .3093739 .001557301  .07665813  1.1646752   .6190909
1 "3M India Ltd."                             736   -.0071751  .11266434  .7428703 10.268948   .077101   .25042298  -.15384005 .002476133  .05088467 -4.0272284   .6511417
1 "3M India Ltd."                             737  -.04703041   .0739418  .7119708 10.264553 .07739938    .5325485 -.004385145 .002661822   .0595725   -7.57249   .5659527
2 "A B B India Ltd."                          726  -.05421241  .10871843   .635006  9.903271 .18018018   -.4943995    .2693031 .002090505  .02334491  -.6459077  -.7134031
2 "A B B India Ltd."                          727   .05935313 .067627124  .6154015  9.850392 .18450184   -.6902285  -.05150485 .001740758  .10209841  -.7429201  -.6914729
2 "A B B India Ltd."                          728  -.09854768  .06322751  .6211821  9.910557  .1736111  -.27073988   .06201117 .001524547  .03217625  -.8175949   -.694613
2 "A B B India Ltd."                          729    .0414137  .06247413  .5816981  9.809862 .19646364   -.3041169  -.09579168 .002239378 .029989824  -1.505044 -1.1118217
2 "A B B India Ltd."                          730    .1941809   .0660737  .5908761   9.85296  .1883239   -.3790045   .04403976 .004153201  .03133433 -1.4992787 -1.0342741
2 "A B B India Ltd."                          731   .12641639  .14531854  .6199369 10.032434  .1572327    -.433146   .19658974 .002379331  .04850976 -1.4020437  -.3804245
2 "A B B India Ltd."                          732    .1422432  .09560207  .6307346  10.15372 .14044943    -.380908    .1289467  .00081309   .0983456  -1.512243   .9759638
2 "A B B India Ltd."                          733   .10133003  .08255054  .6132156 10.289116 .12254902  -.07253855   .14499031 .001179901  .06081778 -1.6716537   .9935083
2 "A B B India Ltd."                          734  -.07761712  .11415926  .6137214 10.387996 .11111111   .03748436     .103934 .000854745  .04869392 -1.8408986   .9434686
2 "A B B India Ltd."                          735 -.027171543  .06083263  .5688488 10.310148 .12484394   .27713224  -.07489476 .000831385   .0570067  -4.824018  1.1051944
2 "A B B India Ltd."                          736   .20342955  .05801463  .7245877 10.285398  .1242236   .59372085 -.024446186 .001075646  .04662298  .04504089  1.1155165
2 "A B B India Ltd."                          737   .07998375  .07539472  .7797678  10.47289 .10298661   .55238646    .2062195 .000678502  .01911901  -2.998602   1.052994
3 "A B B Power Products & Systems India Ltd." 726   .06354318 .016075514         .  8.179317 .24691357           .  .027660774 .008928002 .012207855          .          .
3 "A B B Power Products & Systems India Ltd." 727   .01940937  .03139144         .  8.243466 .23148148           .   .06625074 .004185926 .011174238          .          .
3 "A B B Power Products & Systems India Ltd." 728  .006240233    .124328         .  8.265292 .22624435           .  .022067415 .005273779  .01331494          .          .
3 "A B B Power Products & Systems India Ltd." 729   .03768517  .05144717         .   8.27425 .22573364           .  .008996238 .008629566  .07176136          .          .
3 "A B B Power Products & Systems India Ltd." 730   .18790914  .09119125         .  8.313769 .21691974           .   .04031124 .010219852 .019192396          .          .
3 "A B B Power Products & Systems India Ltd." 731   .12589514  .11065537         .  8.487988 .18214937           .   .19031797 .005895196 .067923956          .          .
3 "A B B Power Products & Systems India Ltd." 732  -.06910693  .14846125         .  8.608813 .17006803           .   .12842546 .004007472  .08885299          .          .
3 "A B B Power Products & Systems India Ltd." 733    .1898327  .05424834  .3848143  8.540148 .18214937           .  -.06635982 .006626954  .09770461  -2.522134  1.2338934
3 "A B B Power Products & Systems India Ltd." 734  -.05284561   .1228909  .4946857  8.716147  .1529052    .5748071   .19243667 .004192062  .03119638 -4.5057607   .4386818
3 "A B B Power Products & Systems India Ltd." 735   .31662655  .08472132  .4715042  8.664725 .16778523    .5084473  -.05012324 .005133849  .05434606  -5.185458  .52969956
3 "A B B Power Products & Systems India Ltd." 736   .07315451    .096315  .7517154  8.941865 .12706481    .7008839    .3193519 .003610235 .065153554  -7.616825   .7414998
3 "A B B Power Products & Systems India Ltd." 737 -.029378373   .0819109  .9040453  9.015063 .11709602    .5808661   .07594446 .001913635  .09387813   -19.2988   .7670611
4 "A C C Ltd."                                726   .06692457  .05451617  .8448679  10.12716 .46296296   -.2754566   .04787002 .000383079  .04571353 -.09471208   .3893507
4 "A C C Ltd."                                727  -.07505673  .06367326  .8417821 10.194476  .4329004  -.12489666  .069632135 .000221298 .032158077 -.06013961   .4025881
4 "A C C Ltd."                                728   .05191258  .04167877  .8355732 10.119323  .4672897  -.07574595 -.072398685 .000227907   .0380812 -.08534753   .4798272
4 "A C C Ltd."                                729   .17999762   .0552238   .806603  10.17255   .456621   .02001385   .05466858 .000552873 .032650948  -.8553799   .3773949
4 "A C C Ltd."                                730   .03086585  .06496176  .8055444 10.340285  .3861004  -.11255998    .1826237 .000220795  .03598324 -1.0381975   .4498981
4 "A C C Ltd."                                731  -.05234683  .05231039  .7977777 10.373018  .3731343 -.032119803  .033274677 .000154127  .06847412 -1.1048306   .4375994
4 "A C C Ltd."                                732 -.011150457  .06558764   .796766 10.321918  .4166667    .1805269  -.04981651 .000142245 .021748837 -1.1506271   .3936705
4 "A C C Ltd."                                733   .07725016  .05752963  .7933859  10.31348   .420168    .2644357 -.008403346 .000195606  .02595036 -1.1382245   .3952785
4 "A C C Ltd."                                734   .09514549  .05743942  .8060316 10.390305  .3891051   .17308663   .07985412  .00022621 .032816257  -.9135931   .3646762
4 "A C C Ltd."                                735 -.013368968  .07591277  .9312607 10.483675  .3623188    .2866054   .09786785 .000225346  .05000623  -3.238484   .8954543
4 "A C C Ltd."                                736    .0522756  .05981381 1.0057371 10.472975  .3663004    .6341707  -.01064361 .000172868  .06163638 -3.7973716   .6073506
4 "A C C Ltd."                                737  .012311548  .03130351 1.0242304 10.526577  .3472222    .5125553   .05506556 .000268184 .032284934  -4.610758   .6639974
5 "A I A Engineering Ltd."                    726   .02495178  .11163152  .4691647  9.626267  .2463054 -.036174126  -.03901812 .010904412   .0536923 -4.4361167  1.3443053
5 "A I A Engineering Ltd."                    727   .08844092  .07996228  .4725839  9.653551 .23980814 -.000900049  .027659345 .013988122 .070349395 -4.4310584  1.2875285
5 "A I A Engineering Ltd."                    728  .022078203  .08275808   .479377  9.740737  .2197802  .004932698   .09109897  .00487389  .03154746 -4.5206795  1.2331452
5 "A I A Engineering Ltd."                    729   -.0823358  .11070403  .4896109  9.765267 .22271717   .13520043   .02483421 .008477802 .035447124   -4.82041  1.2566768
5 "A I A Engineering Ltd."                    730    .0955906  .08087932  .4708679  9.682201 .24213074  .062894896  -.07970973 .010294677 .066804856  -4.917289  1.3512995
5 "A I A Engineering Ltd."                    731     .051809  .07386222  .4658801  9.775691  .2202643   .12334024   .09799944 .011316602  .05447983  -5.032545   1.288421
5 "A I A Engineering Ltd."                    732 -.009125889  .13231656  .4622294  9.828606  .2159827    .0879128   .05433933 .004404285   .0338277  -5.058676  1.3986684
5 "A I A Engineering Ltd."                    733    -.072096   .0978856  .4792798  9.822206  .2173913   .18195726 -.006378778 .005814801  .11592741  -4.858895  1.3310957
5 "A I A Engineering Ltd."                    734   .12311248  .06436446  .4620979  9.750182 .23364484   .15896326 -.069492035 .004135308  .05344504  -4.902668  1.4442155
5 "A I A Engineering Ltd."                    735  -.09286427  .06380035  .3770808  9.868707  .2118644    .1484778   .12583485 .004267094 .034010034  -8.677715   1.302048
5 "A I A Engineering Ltd."                    736   .03399865  .04568206 .50878507  9.774243 .23310024    .3046567  -.09013891 .007538488  .03457905  -6.043911  1.0549867
5 "A I A Engineering Ltd."                    737    .1047434 .071264036  .4872116  9.810371  .2247191   .26809368  .036788605 .004553978 .024216985   -6.33198  1.0595125
6 "A P L Apollo Tubes Ltd."                   726    .1711525  .06370289 1.0974617  8.263031  .3289474 -.002012309    .0262603  .10102758  .08019336  -3.525239   .6283053
6 "A P L Apollo Tubes Ltd."                   727    .3106615   .1343903 1.0962794 8.4233265 .28011206   .13840456   .17386007  .06555298  .04885535   -3.65607   .7112734
6 "A P L Apollo Tubes Ltd."                   728   .21097997  .09383714 1.1086982  8.698171  .2132196   .23652282    .3133195  .04387161  .09002724  -3.689252   .7226738
6 "A P L Apollo Tubes Ltd."                   729   .07558625  .08646914  1.112936  8.891835  .1814882    .4555692   .21373597 .025005063  .06019804  -3.730247    .768245
6 "A P L Apollo Tubes Ltd."                   730    .0652984   .0876136 1.0986406  8.967626 .16806723    .7421367   .07821232   .0412361  .10558265 -3.8472404   .7838255
6 "A P L Apollo Tubes Ltd."                   731    .3143294 .066069156 1.0921849  9.033645  .1574803    .8858544   .06770723  .02108777  .06084066 -3.8359215   .8239514
6 "A P L Apollo Tubes Ltd."                   732  .021805216   .1009317  1.116252  9.309193 .12453301    .8910163    .3168597 .009066988  .05772143  -3.739347   .8350854
6 "A P L Apollo Tubes Ltd."                   733   .17827347  .08637045 1.1104505 9.3334255  .1215067    .7605007  .024552327  .00338194  .06195477  -3.748623   .7883906
6 "A P L Apollo Tubes Ltd."                   734    .3058266   .0683895 1.1005763  9.499694 .10298661   1.0302011   .18087743 .003324847  .05570559   -3.90839   .7919657
6 "A P L Apollo Tubes Ltd."                   735  -.07329819  .17121236  .9897493  9.769517 .08285005    1.067184     .308549 .003410512  .05058444  -5.518927   .5153883
6 "A P L Apollo Tubes Ltd."                   736 -.002590209  .11828837 1.0464791  9.696322 .08912656   1.6050384  -.07057284 .003857096   .0753162  -6.445298  .28469825
6 "A P L Apollo Tubes Ltd."                   737   .24788477   .1114527  .9669085  9.696514 .08912656    1.767418  .000199746 .004597923  .06224928 -2.0432703   .2863312
7 "A U Small Finance Bank Ltd."               726    .3652917  .14167368   .853076  9.719072  .2754821   .08082742    .3848704 .001885934  .04989343 -3.5486624   .0484161
7 "A U Small Finance Bank Ltd."               727   -.1039307  .14123882  .8708094 10.032588 .20120725    -.206587    .3679993 .001281672   .0499951 -3.6275396 .031356942
7 "A U Small Finance Bank Ltd."               728  -.02253566  .09675895  .8738738  9.925946 .22371365    .2142261  -.10127266 .000874132        .05  -3.660249  .05433789
7 "A U Small Finance Bank Ltd."               729    .1833404  .08047066  .9199474  9.906085 .24449877   .58922505 -.019779654 .001992176  .04174134  -3.209266  .07161495
7 "A U Small Finance Bank Ltd."               730   .07679974  .09965545  .9488076 10.076657 .20618556   .51295793   .18596646 .001599407   .0491497  -3.035593  .09861518
7 "A U Small Finance Bank Ltd."               731   .01532577  .09570134  .9583536 10.153445  .1908397    .4288709   .07920858 .000972757  .09319128 -2.8275106  -.2055848
7 "A U Small Finance Bank Ltd."               732   .02171895  .07963484  .9831188 10.171297  .2057613    .4376474  .017856091 .000603365  .05666437  -2.718196 -.21354687
7 "A U Small Finance Bank Ltd."               733   .28827286  .05495229  .9816545  10.19554  .2008032    .5379943   .02446606 .000625268  .04377429 -2.5011506  -.4505863
7 "A U Small Finance Bank Ltd."               734    .0853433  .09901675 1.0247761  10.45187   .155521   .22913617   .29087684 .000524272 .027254846  -2.046643  -.3897894
7 "A U Small Finance Bank Ltd."               735   -.1844175  .13246192  1.229345 10.552818 .16393442   .16660504   .08806566 .000466805  .07605524  -7.378787   .1787469
7 "A U Small Finance Bank Ltd."               736 -.018984085   .1121216 1.6434085 10.352456  .2004008   1.0195731  -.18169214 .000370574  .09695206   6.175886  .10894027
7 "A U Small Finance Bank Ltd."               737   .04734747  .10038955 1.7543415 10.337925 .20325203    1.039151  -.01619413 .000243057  .05563205 -2.9424515 -.02547243
8 "Aarti Drugs Ltd."                          726    .3652348  .13313535  .8086121  7.993387 .23474178    .3807678    .3087569 .009140558  .09490426  -.4706872   1.089217
8 "Aarti Drugs Ltd."                          727    .5602086  .14255284   .785423 8.3067045  .1715266    .8077216    .3679424 .005949514  .08292234   -.928286  1.0338839
8 "Aarti Drugs Ltd."                          728   .13304557    .335134   .818833  8.753226  .1097695   1.2455642    .5628667 .002243741  .09870462 -1.2160615    1.31322
8 "Aarti Drugs Ltd."                          729  -.12141288   .0926184  .8414894  8.880565 .10638298   1.6029565   .13580157 .001181235  .18303907 -1.1839818   1.255108
8 "Aarti Drugs Ltd."                          730   .04973112  .20832872  .8730475  8.754108 .12062726   2.1920085  -.11878681 .001514468  .04999422  -.7056916   .9567614
8 "Aarti Drugs Ltd."                          731  -.01636965  .10740176   .856287 8.8049345  .1112347   2.1481087   .05213995  .00066296    .078699  -.9146856   .8857307
8 "Aarti Drugs Ltd."                          732  -.04739946  .06569554  .8500131 8.7909975 .12254902   2.0610538  -.01383933 .000391531  .04995947  -.9434087   .8830878
8 "Aarti Drugs Ltd."                          733  -.06662952  .08209758  .8354392  8.745317  .1283697    2.000185  -.04465235  .00048818  .05262806  -1.022623    .892891
8 "Aarti Drugs Ltd."                          734    .0987361  .05586975   .786799 8.6791525 .13717422   1.9199233  -.06402556 .000405053  .04554066  -1.016265    .969326
8 "Aarti Drugs Ltd."                          735  .018791644  .11462262  .6638222  8.775787 .13280211   1.7510244   .10145847  .00070765  .03851528   4.930764  1.0558859
8 "Aarti Drugs Ltd."                          736  .021796184 .063948065  .7778706  8.797075 .12987013   1.9580123     .021517 .000570753   .0970576 -10.418921  1.1215001
8 "Aarti Drugs Ltd."                          737  -.02986646   .1548877  .8339178  8.814905 .11876485   1.6459476   .02458614 .000303436 .033678792 -21.961113  1.1842566
9 "Aarti Industries Ltd."                     726   .05996685  .11119487  .7404691  9.694935 .18382353    .2986297  -.04509121 .001419539  .03675639  -1.653559   .5665228
9 "Aarti Industries Ltd."                     727   .05581495  .05761338  .7325048  9.755724  .1730104   .23099522   .06267442 .001240451  .06682057 -1.7685193   .6045336
9 "Aarti Industries Ltd."                     728 -.033955272  .10733598  .7316916  9.812551  .1633987   .25026777     .058473 .001201547  .02979867 -1.8458003   .8301944
9 "Aarti Industries Ltd."                     729  -.01980157 .070201054  .7370305  9.780855  .1754386    .3552699 -.031199267 .001872892  .05382604 -2.0215871    .807709
end
format %tm mdate

.

[Clyde Schechter]

Well, there is, in fact, a probable mistake in your program--perhaps one that never bites, but in principle it could. The difficulty is with -if !missing(rt)-. The program is called iteratively by -runby- with one mdate's worth of data at a time. When -if !missing(rt)- is encountered, that tells Stata to look at the first value of rt in that chunk of data, and if that is not missing, to do the -summarize- and other commands inside the curly braces. But if that value is missing, all of that is skipped. So if it happens that rt has a missing value for 3M India Ltd. in some month, that entire month's data for all stocks is simply omitted from your calculations and results. Maybe that's what you want, but it seems odd to single out one stock in that way. I think you meant to say that you want -summarize- performed only on non-missing values of rt. But that is automatic: you don't have to tell Stata anything to make that happen.

Now, after that is fixed, the code can be generalized to cover all the variables. But the most direct generalization will lead to an extremely wide file with a single observation. That file will be very difficult to work with in terms of further analysis, and will be unwieldy for human eyes to read because it will require a lot of scrolling. So I will recommend that instead, we write a slightly different program that computes the same statistics for all of these variables but leaves them in the form of a data set with one observation for each variable from rt through idioskew, and the variables are called mean, sd, etc.

frame create summary_stats str32 variable  int mdate float(mean sd skew kurt min five ///
    twentyfive fifty seventyfive ninetyfive max)

capture program drop my_first_program
program my_first_program
    foreach v of varlist rt-idioskew {
        summarize `v', detail
        frame post summary_stats ("`v'") (mdate[1]) (`r(mean)') (`r(sd)') ///
            (`r(skewness)') (`r(kurtosis)') (`r(min)') (`r(p5)') (`r(p25)') ///
            (`r(p50)') (`r(p75)') (`r(p95)') (`r(max)')
    }
end

runby my_first_program, by(mdate)

frame change summary_stats
isid variable mdate
collapse (mean) mean sd skew kurt min five twentyfive fifty seventyfive ///
    ninetyfive max, by(variable)

Note that this code requires only one -collapse- at the end, because the revised my_first_program now creates only a single observation for each variable for each mdate, so no need to collapse -by(mdate)-. What was originally your second -collapse- remains, but is now done -by(variable)- so as to get the mean values of the summary statistics averaged over all the months.

[Sartaj Hussain]

Thanks. It is indeed comprehensive.

[Sartaj Hussain]

I want to apply code in #2 to generate summary statistics of two time-series variables. So there is no by mdate needed. There are no cross-section of stocks as was previously. What change is to be brought into the code.

[Clyde Schechter]

It makes it a little simpler:

frame create summary_stats str32 variable float(mean sd skew kurt min five ///
    twentyfive fifty seventyfive ninetyfive max)

foreach v of varlist rt-idioskew {
    summarize `v', detail
    frame post summary_stats ("`v'") (`r(mean)') (`r(sd)') ///
        (`r(skewness)') (`r(kurtosis)') (`r(min)') (`r(p5)') (`r(p25)') ///
        (`r(p50)') (`r(p75)') (`r(p95)') (`r(max)')
}

frame change summary_stats