preparing summary statistics table

[rahul kumar singh]

hello community members,
I have panel data for 6 years and have 54 variables in it. I want to prepare a summary table containing mean, max, min, count, and sd for each variable for each year in the format which i have attached in an image.
the panel data set is given below.
please help me

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 HH_ID int YEAR float(FARM_DIST FARM_WORK_DAYS FARM_AVG_WORK_HRS FARM_WAGES_CASH FARM_WAGES_KIND FARM_AMT_SPENT FARM_WAGES_NET_INC NF_DIST NF_WORK_DAYS)
"IMPA0060" 2013         .   .        .     .     .  .     .         .    .
"IAPA0238" 2013         0  88        8 11050     0  0 11050         0  126
"IAPA0232" 2009         .   .        .     .     .  .     .         0  429
"IAPA0232" 2014         .   .        .     .     .  .     .         0  189
"IAPA0234" 2009         0  37        8  2540  1500  0  4040         0  342
"IAPA0236" 2011         0 142        8 12290  3700  0 15990         0   92
"IAPA0239" 2009         0  34        8  2390     0  0  2390         0  432
"IAPA0043" 2014         0 202        8 23440     0  0 23440         0   10
"IAPA0301" 2010         0 195        8 19960  2550  0 22510  50.90909  693
"IAPA0240" 2012         0 120        8 14400     0  0 14400  43.07692  848
"IAPA0053" 2012         .   .        .     .     .  .     .        20  295
"IAPA1006" 2011 2.3076923 109        8 10900     0  0 10900        70    6
"IAPA0238" 2009         0  34        8  4700     0  0  4700         0   54
"IAPA0297" 2011         0  96        8  9960   300  0 10260  10.90909  254
"IAPA0297" 2013         0 177        8 25450     0  0 25450         0  263
"IAPA0043" 2009         0  96 7.333333  5300  3000  0  8300         0  380
"IAPA0300" 2011         0 144        8 13420  1900  0 15320         0  389
"IAPA0312" 2014         0 239        8 21450 20400  0 41850  41.90476  418
"IAPA0315" 2009         .   .        .     .     .  .     .         .    .
"IAPA0301" 2012 1.3636364 216        8 27670     0  0 27670        55  591
"IAPA0312" 2012         0 369        8 49530   400  0 49930        30  820
"IAPA0304" 2010         .   .        .     .     .  .     .         0  698
"IAPA0306" 2009         .   .        .     .     .  .     .         .    .
"IAPA0239" 2012         0  16        8  2250     0  0  2250  4.551724  669
"IAPA0309" 2010         .   .        .     .     .  .     .         .    .
"IAPA0309" 2011         .   .        .     .     .  .     .         .    .
"IAPA0311" 2010         0 484      8.8 46067  3000  0 49067         0  109
"IAPA0312" 2010         0 189        8 18155  3600  0 21755         9  561
"IAPA0312" 2011         0  70        8  8080     0  0  8080 27.413794  739
"IAPA0314" 2011         .   .        .     .     .  .     .         0  360
"IAPA0304" 2011         .   .        .     .     .  .     .         0  720
"IAPA0315" 2014         .   .        .     .     .  .     .         .    .
"IAPA0317" 2009         0 259 9.166667 20370  2130  0 22500         0  383
"IAPA0328" 2010         0 109        8 10040  2550  0 12590         0  324
"IAPA0329" 2009         0  18        8   400  2100  0  2500         0  281
"IAPA1124" 2009         0  81        8  4530  3000  0  7530  6.363636  254
"IAPA0328" 2013         0  63        8  8440     0  0  8440         0  278
"IAPA1006" 2012         0 200        8 23230     0  0 23230        70    6
"IAPA1124" 2011         0  92        8  7270  5000  0 12270        42  506
"IAPA0330" 2013         .   .        .     .     .  .     .         0  240
"IAPA0241" 2011         .   .        .     .     .  .     .         .    .
"IAPA0239" 2013         0  44        8  6330     0  0  6330         4  671
"IAPA0308" 2009         .   .        .     .     .  .     .         0   15
"IAPA0330" 2012         .   .        .     .     .  .     .         0  162
"IAPA0333" 2013         0  62        8  7860     0  0  7860  53.33333  410
"IAPA0239" 2011         0  98        8 10490     0  0 10490         0  479
"IAPA0010" 2013         0  83        8 11420     0  0 11420         0  293
"IAPA0203" 2014         0 129        8 17500  2200  0 19700         0   50
"IAPA0308" 2010         .   .        .     .     .  .     .         0    4
"IAPA0302" 2010     1.875 216        8 21730  2900  0 24630         0   69
"IAPA0036" 2012         .   .        .     .     .  .     .         .    .
"IAPA0242" 2011         .   .        .     .     .  .     .         .    .
"IAPA0048" 2013         .   .        .     .     .  .     .        80  291
"IAPA0238" 2014         0 135        8 23790     0  0 23790         0   88
"IAPA0315" 2010         .   .        .     .     .  .     .         .    .
"IAPA0307" 2014         0  15        8  2130     0  0  2130        .4  244
"IAPA0233" 2012         0 194        8 24400     0  0 24400        70  291
"IAPA0229" 2012         0  92        8 11720     0  0 11720         0   49
"IAPA0330" 2010         .   .        .     .     .  .     .         0  728
"IAPA0307" 2012         0 175        6 14150     0  0 14150         0  299
"IAPA0210" 2013         0  20        8  2700     0  0  2700         0  296
"IAPA0307" 2013         0  99        7  9550     0  0  9550         0  205
"IAPA0217" 2010         .   .        .     .     .  .     .        25  581
"IAPA0044" 2011         0  80        8  8650     0  0  8650        20 1086
"IAPA0317" 2013         0 398     8.12 60450    80 80 60450         0  502
"IAPA0030" 2009         .   .        .     .     .  .     .         0  377
"IAPA0242" 2010         .   .        .     .     .  .     .         .    .
"IAPA0239" 2014         .   .        .     .     .  .     . 18.181818  223
"IAPA0237" 2011         .   .        .     .     .  .     .         0   49
"IAPA0335" 2014         .   .        .     .     .  .     .         .    .
"IAPA0208" 2010         0  16        8     0  2400  0  2400 35.744682 1101
"IAPA0233" 2009         0 111        8  4780  6350  0 11130  4.285714  263
"IAPA0203" 2009         0  56        8  6500     0  0  6500         0  247
"IAPA0302" 2012     2.625 223        8 28125     0  0 28125 18.666666  308
"IAPA0055" 2010         .   .        .     .     .  .     .         0  508
"IAPA0048" 2014         0  13        8  1560     0  0  1560  5.818182  153
"IAPA0316" 2013         .   .        .     .     .  .     .         0  307
"IAPA0297" 2012         0 415        8 62340     0  0 62340         0   45
"IAPA0030" 2014         .   .        .     .     .  .     .         0  327
"IAPA0058" 2011         .   .        .     .     .  .     .         0   57
"IAPA0309" 2014         .   .        .     .     .  .     .         .    .
"IAPA0226" 2014         .   .        .     .     .  .     .         0  210
"IAPA0046" 2013         0 179 7.888889 28840     0  0 28840         0  263
"IAPA0316" 2010         .   .        .     .     .  .     .         0  561
"IAPA0238" 2011         .   .        .     .     .  .     .         0   90
"IAPA0219" 2013         .   .        .     .     .  .     .         .    .
"IAPA0309" 2012         0   4        8   800     0  0   800         .    .
"IAPA0008" 2013         0 129        8 20330     0  0 20330         0  151
"IAPA0302" 2013         0 124        8 14480     0  0 14480  28.88889  249
"IAPA0203" 2013         0  90        8 13940     0  0 13940         0   71
"IAPA0212" 2012         0 222        8 31010     0  0 31010  40.47826  518
"IAPA0330" 2011         .   .        .     .     .  .     .         0  512
"IAPA0055" 2014         0  16        5  8000     0  0  8000         0  146
"IAPA0317" 2012  .5882353 657 8.705882 95575     0  0 95575         0  695
"IAPA0234" 2012  .8823529 226        8 29390  1000  0 30390  5.333333  311
"IAPA0306" 2011         .   .        .     .     .  .     .         .    .
"IAPA0305" 2013         .   .        .     .     .  .     .  6.666667  386
"IAPA0046" 2012         0 148        8 19570     0  0 19570         0  134
"IAPA0329" 2010         0 104        8 10880  1400  0 12280         0  374
"IAPA0228" 2011       6.4 258        8 26740  1000  0 27740         5  520
end

------------------ copy up to and including the previous line ------------------

[Carlo Lazzaro]

Rahul:
do you mean something along the following lines?

. bysort YEAR : tabstat FARM_DIST FARM_DIST FARM_WORK_DAYS FARM_AVG_WORK_HRS FARM_WAGES_CASH FARM_WAGES_KIND FARM_AMT_SPENT FARM_WAGES_
> NET_INC NF_DIST NF_WORK_DAYS, stats(count mean sd p50 min max)

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2009

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |         9         9         9         9         9         9         9         9        12        12
    mean |         0         0  80.66667  8.055556  5723.333  2008.889         0  7732.222  .8874458  288.0833
      sd |         0         0  73.95607  .4714047  5790.628  2037.115         0  6227.521  2.119444  135.4249
     p50 |         0         0        56         8      4700      2100         0      6500         0     311.5
     min |         0         0        18  7.333333       400         0         0      2390         0        15
     max |         0         0       259  9.166667     20370      6350         0     22500  6.363636       432
--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2010

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |         7         7         7         7         7         7         7         7        13        13
    mean |  .2678571  .2678571  187.5714  8.114286  18118.86  2628.571         0  20747.43  9.281059  485.4615
      sd |  .7086834  .7086834  147.9469  .3023717  14396.13  674.4486         0   14697.3  16.96964  307.2007
     p50 |         0         0       189         8     18155      2550         0     21755         0       561
     min |         0         0        16         8         0      1400         0      2400         0         4
     max |     1.875     1.875       484       8.8     46067      3600         0     49067  50.90909      1101
--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2011

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |         9         9         9         9         9         9         9         9        15        15
    mean |  .9675214  .9675214       121         8  11977.78  1322.222         0     13300  11.68819     390.6
      sd |  2.175447  2.175447  57.18391         0  5873.427  1859.286         0  6048.343  20.54667  309.5042
     p50 |         0         0        98         8     10490       300         0     10900         0       389
     min |         0         0        70         8      7270         0         0      8080         0         6
     max |       6.4       6.4       258         8     26740      5000         0     27740        70      1086
--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2012

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |        15        15        15        15        15        15        15        15        16        16
    mean |  .3639483  .3639483  218.4667  7.913725     28944  93.33333         0  29037.33  22.31918  377.5625
      sd |  .7506103  .7506103  163.4813  .5597589  24434.18  271.1527         0  24461.05   25.8797   278.112
     p50 |         0         0       200         8     24400         0         0     24400        12     303.5
     min |         0         0         4         6       800         0         0       800         0         6
     max |     2.625     2.625       657  8.705882     95575      1000         0     95575        70       848
--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2013

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |        13        13        13        13        13        13        13        13        17        17
    mean |         0         0  119.6923  7.923761  16987.69  6.153846  6.153846  16987.69  10.16993  294.2353
      sd |         0         0  95.84396   .281541  15065.09  22.18801  22.18801  15065.09  22.88721  141.6283
     p50 |         0         0        90         8     11420         0         0     11420         0       278
     min |         0         0        20         7      2700         0         0      2700         0        71
     max |         0         0       398      8.12     60450        80        80     60450        80       671
--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------
-> YEAR = 2014

   stats |  FARM_D~T  FARM_D~T  FARM_W~S  FARM_A~S  FARM_W~H  FARM_W~D  FARM_A~T  FARM_W~C   NF_DIST  NF_WOR~S
---------+----------------------------------------------------------------------------------------------------
       N |         7         7         7         7         7         7         7         7        11        11
    mean |         0         0       107  7.571429  13981.43  3228.571         0     17210  6.027705  187.0909
      sd |         0         0  94.24967  1.133893  9868.792  7616.148         0  14448.71  13.11843  118.6486
     p50 |         0         0       129         8     17500         0         0     19700         0       189
     min |         0         0        13         5      1560         0         0      1560         0        10
     max |         0         0       239         8     23790     20400         0     41850  41.90476       418
--------------------------------------------------------------------------------------------------------------

.

[rahul kumar singh]

No sir,
i want years in column and variables in rows.
I have shown in picture also

[Nick Cox]

See also

help table

but the fact of the matter is that just as anyone can think up all kinds of tables there is no guarantee that there is a canned command to do exactly and easily what you want.

You may need to do your own programming, or just buckle down to an editing job.

Before you do that, (1) is someone insisting on this? (2) is anyone really going to spend time reading a table with 1620 entries?

[rahul kumar singh]

Mr. Nick,
It is necessary for my research work. my prof. wanted me to prepare a table in this format. this table format is easier to see the trend over the years for a large number of variables. after analysis of trends, we will model and use fixed and random effect