Averaging string variables and number of institutions categorised by unique IDs

[Duncan Mphande]

Please help me out with generating averages for variables: number of tickers (a string variable) over all sample (2007-2019) and subperiods (C 2007-2013, D 2014-2019) in the sample data, and averaging the number of institutions for each institution type which have been categorized as 1 for banks, 2 for pension funds etc up to 7. Each investor is identified by a unique ID. My aim is to generate average tickers traded by each group of investor type, averages for greater than eaqual to 1, 3 and 5 institutions. I need to group them and find averages

My sample data looks like this

* Example generated by -dataex-. For more info, type help dataex
clear
input int investor_id byte investor_type str3 ticker float qdate
  2 2 "PUG" 206
  4 3 "BGK" 234
  4 3 "PUG" 191
  4 3 "BGK" 234
  4 3 "BGK" 234
  5 1 "BGK" 237
  5 1 "BGK" 236
  5 1 "BGK" 235
  5 1 "BGK" 235
  5 1 "BGK" 236
  5 1 "BGK" 238
  5 1 "BGK" 238
  5 1 "BGK" 238
  5 1 "BGK" 236
  5 1 "BGK" 235
  5 1 "BGK" 235
  7 2 "PUG" 209
  7 2 "BGK" 208
 15 1 "BGK" 236
 15 1 "BGK" 236
 15 1 "BGK" 214
 15 1 "BGK" 231
 15 1 "BGK" 238
 15 1 "BGK" 231
 15 1 "BGK" 207
 15 1 "BGK" 237
 15 1 "BGK" 239
 15 1 "BGK" 240
 15 1 "BGK" 239
 34 5 "PUG" 208
 35 1 "BGK" 228
 35 1 "BGK" 224
 35 1 "BGK" 221
 35 1 "BGK" 225
 35 1 "BGK" 225
 40 5 "BGK" 203
 40 5 "BGK" 207
 40 5 "BGK" 207
 40 5 "BGK" 207
 40 5 "BGK" 208
 41 2 "PUG" 191
 41 2 "BGK" 208
 41 2 "PUG" 191
 50 6 "PUG" 206
 56 2 "PUG" 206
 56 2 "BGK" 208
 70 2 "BGK" 238
 71 2 "BGK" 239
 71 2 "BGK" 233
 71 2 "BGK" 239
 71 2 "BGK" 233
 71 2 "BGK" 233
 71 2 "BGK" 233
 71 2 "BGK" 239
 72 5 "PUG" 206
 72 5 "PUG" 206
 72 5 "PUG" 206
 72 5 "PUG" 206
 73 2 "PUG" 206
 74 6 "PUG" 206
 81 3 "PUG" 206
 82 4 "BGK" 207
 83 4 "PUG" 206
 83 4 "BGK" 200
 86 4 "PUG" 206
 93 7 "PUG" 206
 96 2 "BGK" 237
 97 2 "PUG" 206
 97 2 "BGK" 230
 97 2 "BGK" 208
107 2 "BGK" 208
107 2 "PUG" 206
109 6 "PUG" 193
109 6 "BGK" 190
110 2 "BGK" 208
113 7 "PUG" 206
end
format %tq qdate

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


[/CODE]

My aim is to generate a descriptives stats for the data in a table whose sample output is below

* Example generated by -dataex-. For more info, type help dataex
clear
input str55 PanelANoofinstitutionalinv byte(Averageallperiods C D)
"No of institutions"                                       8  7  8
"No of Banks"                                              3  3  2
"No of pension funds"                                      5  4  5
""                                                         .  .  .
"Panel B:  Average Number of tickers with"                 .  .  .
">=1 trader"                                              32 25 28
">=3 traders"                                             12 15 10
">=5 traders"                                             18 14 18
">=2 banks"                                                8  8  8
">=2 pension funds"                                       12 12 10
""                                                         .  .  .
"Panel C: Average No of tickers held by each institution"  .  .  .
"all institutions"                                        14 14 14
"banks"                                                   12 12 11
"pension funds"                                           14 14 14
end

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


[/CODE]

I will be grateful for your kind help

[Carlo Lazzaro]

Duncan:
see -collapse- and -table- suite of commands provided by Stata 17.

[Duncan Mphande]

Carlo Lazzaro Many thanks