Table with unique firms and firm-year observations by country

Marius Mai

Join Date: Jan 2018

Posts: 15
#1

Table with unique firms and firm-year observations by country

21 Jan 2018, 09:40

Hi all,
I have a dataset containing information on banks in multiple countries over a period from 2000-2015. I would like to create a table that displays the unique banks by country and the bank-year observations (observations across all years in the dataset of one bank) by country, just like the table below. Ideally, the table can be exported to excel or word. How can I do it?

Thanks in advance!
Tags: None
Clyde Schechter

Join Date: Apr 2014

Posts: 30101
#2

21 Jan 2018, 10:16

I have a dataset containing information on banks in multiple countries over a period from 2000-2015.

I'm sure that in your own mind it is quite clear what you have. But I can think, off the top of my head, of at least half a dozen ways such a data set could be organized, and they would require different code to get the information you want. If you go to an online map application for directions, you have to specify where you are, not just where you want to go.

So please use the -dataex- command (see FAQ #12 if you are not familiar with -dataex-) and post an example of your data. Then it is likely you will get a timely and helpful response.
Comment

Marius Mai

Join Date: Jan 2018
Posts: 15

21 Jan 2018, 10:50

Sorry, I am very new to stata and this forum..

Here is an example of my dataset. I hope that helps

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str130 Bank_Name double year str38 country float Deposits double(BV_Equity BV_Assets)
"1ST BANK"                               2005 "UNITED STATES OF AMERICA" .8026316  42 304
"1ST BANK"                               2006 "UNITED STATES OF AMERICA" .7876923  43 325
"1ST BANK"                               2007 "UNITED STATES OF AMERICA" .8026316  67 456
"1ST BANK"                               2008 "UNITED STATES OF AMERICA" .7430407  72 467
"1ST BANK"                               2009 "UNITED STATES OF AMERICA" .6466974 102 651
"1ST BANK"                               2010 "UNITED STATES OF AMERICA" .6550279 107 716
"1ST BANK"                               2011 "UNITED STATES OF AMERICA" .6819338  94 786
"1St Century Bank, National Association" 2008 "UNITED STATES OF AMERICA" .6100386  53 259
"1St Century Bank, National Association" 2009 "UNITED STATES OF AMERICA" .7728938  43 273
"1St Century Bank, National Association" 2010 "UNITED STATES OF AMERICA" .8474026  41 308
"1St Century Bank, National Association" 2011 "UNITED STATES OF AMERICA" .8246914  44 405
"1St Century Bank, National Association" 2012 "UNITED STATES OF AMERICA" .8376753  48 499
"1St Century Bank, National Association" 2013 "UNITED STATES OF AMERICA" .8457249  53 538
"1St Century Bank, National Association" 2014 "UNITED STATES OF AMERICA" .8666667  58 585
"1St Security Bank Of Washington"        2005 "UNITED STATES OF AMERICA" .8196079  34 255
"1St Security Bank Of Washington"        2006 "UNITED STATES OF AMERICA" .8054475  34 257
"1St Security Bank Of Washington"        2007 "UNITED STATES OF AMERICA" .7924528  33 265
"1St Security Bank Of Washington"        2008 "UNITED STATES OF AMERICA"  .844358  30 257
"1St Security Bank Of Washington"        2009 "UNITED STATES OF AMERICA"  .822695  23 282
"1St Security Bank Of Washington"        2010 "UNITED STATES OF AMERICA" .8390411  25 292
"1St Security Bank Of Washington"        2011 "UNITED STATES OF AMERICA" .8697183  27 284
"1St Security Bank Of Washington"        2012 "UNITED STATES OF AMERICA" .8412256  48 359
"1St Security Bank Of Washington"        2013 "UNITED STATES OF AMERICA" .8353222  50 419
"1St Security Bank Of Washington"        2014 "UNITED STATES OF AMERICA" .8443114  56 501
"1St State Bank"                         2007 "UNITED STATES OF AMERICA"     .824  11 125
"1St State Bank"                         2008 "UNITED STATES OF AMERICA" .7826087  12 138
"1St State Bank"                         2009 "UNITED STATES OF AMERICA" .7792208  14 154
"1St State Bank"                         2010 "UNITED STATES OF AMERICA"   .76875  16 160
"1St State Bank"                         2011 "UNITED STATES OF AMERICA"  .770115  17 174
"1St State Bank"                         2012 "UNITED STATES OF AMERICA" .7888889  19 180
"1St State Bank"                         2013 "UNITED STATES OF AMERICA" .8201058  19 189
"1St State Bank"                         2014 "UNITED STATES OF AMERICA" .8657407  19 216
"1St Trust Bank, Inc."                   2009 "UNITED STATES OF AMERICA" .9082569  10 109
"1St Trust Bank, Inc."                   2010 "UNITED STATES OF AMERICA" .9112903  10 124
"1St Trust Bank, Inc."                   2011 "UNITED STATES OF AMERICA" .9124088  12 137
"1St Trust Bank, Inc."                   2012 "UNITED STATES OF AMERICA" .9150327  13 153
"1St Trust Bank, Inc."                   2013 "UNITED STATES OF AMERICA" .8717949  13 156
"1St Trust Bank, Inc."                   2014 "UNITED STATES OF AMERICA" .9076923  15 195
"1st Advantage Bank"                     2008 "UNITED STATES OF AMERICA" .7619048  11 126
"1st Advantage Bank"                     2009 "UNITED STATES OF AMERICA" .7719298  10 114
"1st Bank"                               2007 "UNITED STATES OF AMERICA" .8823529  10 102
"1st Bank"                               2008 "UNITED STATES OF AMERICA" .8415841  10 101
"1st Bank"                               2009 "UNITED STATES OF AMERICA" .8446602  11 103
"1st Bank & Trust"                       2007 "UNITED STATES OF AMERICA" .8942308  10 104
"1st Bank & Trust"                       2008 "UNITED STATES OF AMERICA" .8796296  12 108
"1st Bank & Trust"                       2009 "UNITED STATES OF AMERICA"  .880734  13 109
"1st Bank & Trust"                       2010 "UNITED STATES OF AMERICA" .9074074  10 108
"1st Bank & Trust"                       2011 "UNITED STATES OF AMERICA" .8909091  12 110
"1st Bank & Trust"                       2012 "UNITED STATES OF AMERICA" .8823529  13 119
"1st Bank & Trust"                       2013 "UNITED STATES OF AMERICA" .8823529  14 119
"1st Bank & Trust"                       2014 "UNITED STATES OF AMERICA" .8828125  15 128
"1st Bank Oklahoma"                      2005 "UNITED STATES OF AMERICA" .8175676  11 148
"1st Bank Oklahoma"                      2006 "UNITED STATES OF AMERICA" .7974684  11 158
"1st Bank Oklahoma"                      2007 "UNITED STATES OF AMERICA" .8139535  14 172
"1st Bank Oklahoma"                      2008 "UNITED STATES OF AMERICA" .6990291  17 206
"1st Bank Oklahoma"                      2009 "UNITED STATES OF AMERICA" .6652893  23 242
"1st Bank Oklahoma"                      2010 "UNITED STATES OF AMERICA" .6830189  25 265
"1st Bank Oklahoma"                      2011 "UNITED STATES OF AMERICA" .8359789  19 189
"1st Bank Yuma"                          2005 "UNITED STATES OF AMERICA" .9189189   9 111
"1st Bank Yuma"                          2006 "UNITED STATES OF AMERICA" .8833333  11 120
"1st Bank Yuma"                          2007 "UNITED STATES OF AMERICA" .8534483  12 116
"1st Bank Yuma"                          2008 "UNITED STATES OF AMERICA" .8166667  11 120
"1st Bank Yuma"                          2009 "UNITED STATES OF AMERICA" .8709677  14 124
"1st Bank Yuma"                          2010 "UNITED STATES OF AMERICA" .8880597  15 134
"1st Bank Yuma"                          2011 "UNITED STATES OF AMERICA" .8611111  19 144
"1st Bank Yuma"                          2012 "UNITED STATES OF AMERICA" .8629442  21 197
"1st Bank Yuma"                          2013 "UNITED STATES OF AMERICA" .8844221  21 199
"1st Bank Yuma"                          2014 "UNITED STATES OF AMERICA" .8831776  23 214
"1st Bank of Sea Isle City"              2005 "UNITED STATES OF AMERICA" .8663595  15 217
"1st Bank of Sea Isle City"              2006 "UNITED STATES OF AMERICA" .8298969  16 194
"1st Bank of Sea Isle City"              2007 "UNITED STATES OF AMERICA" .8196721  16 183
"1st Bank of Sea Isle City"              2008 "UNITED STATES OF AMERICA"      .83  17 200
"1st Bank of Sea Isle City"              2009 "UNITED STATES OF AMERICA" .8760331  17 242
"1st Bank of Sea Isle City"              2010 "UNITED STATES OF AMERICA" .9152542  18 236
"1st Bank of Sea Isle City"              2011 "UNITED STATES OF AMERICA" .9045643  20 241
"1st Bank of Sea Isle City"              2012 "UNITED STATES OF AMERICA" .8953975  21 239
"1st Bank of Sea Isle City"              2013 "UNITED STATES OF AMERICA" .8888889  22 234
"1st Bank of Sea Isle City"              2014 "UNITED STATES OF AMERICA" .8841202  23 233
"1st Bank, National Association"         2006 "UNITED STATES OF AMERICA" .8081396  16 172
"1st Bank, National Association"         2007 "UNITED STATES OF AMERICA" .8146341  17 205
"1st Bank, National Association"         2008 "UNITED STATES OF AMERICA" .8221154  19 208
"1st Bank, National Association"         2009 "UNITED STATES OF AMERICA" .8601398  23 286
"1st Bank, National Association"         2010 "UNITED STATES OF AMERICA" .8459017  31 305
"1st Bank, National Association"         2011 "UNITED STATES OF AMERICA" .8745981  29 311
"1st Bank, National Association"         2012 "UNITED STATES OF AMERICA" .8686869  31 297
"1st Bank, National Association"         2013 "UNITED STATES OF AMERICA"     .875  31 304
"1st Bank, National Association"         2014 "UNITED STATES OF AMERICA" .8580247  39 324
"1st Capital Bank"                       2008 "UNITED STATES OF AMERICA" .7862595  28 131
"1st Capital Bank"                       2009 "UNITED STATES OF AMERICA" .8541667  27 192
"1st Capital Bank"                       2010 "UNITED STATES OF AMERICA" .8678414  28 227
"1st Capital Bank"                       2011 "UNITED STATES OF AMERICA" .8888889  32 288
"1st Capital Bank"                       2012 "UNITED STATES OF AMERICA" .8966565  34 329
"1st Capital Bank"                       2013 "UNITED STATES OF AMERICA" .8992248  38 387
"1st Capital Bank"                       2014 "UNITED STATES OF AMERICA" .9096774  41 465
"1st Centennial Bank"                    2005 "UNITED STATES OF AMERICA" .8815789  50 456
"1st Centennial Bank"                    2006 "UNITED STATES OF AMERICA"  .831216  58 551
"1st Centennial Bank"                    2007 "UNITED STATES OF AMERICA" .6952105  62 689
"1st Choice Bank Inc"                    2005 "UNITED STATES OF AMERICA" .9139344  20 244
"1st Choice Bank Inc"                    2006 "UNITED STATES OF AMERICA" .9124579  24 297
"1st Choice Bank Inc"                    2007 "UNITED STATES OF AMERICA" .9042904  27 303
end

Comment

Nick Cox

Join Date: Mar 2014
Posts: 35698

21 Jan 2018, 11:16

Thanks for the data example. This may help:

Code:

bysort country : gen years = _N
egen tag = tag(country Bank_Name)
egen distinct = total(tag), by(country)
tabdisp country, c(distinct years)

-------------------------------------------------
                 country |   distinct       years
-------------------------+-----------------------
UNITED STATES OF AMERICA |         15         100
-------------------------------------------------

Many threads here with similar questions. See e.g.

https://www.statalist.org/forums/for...tions-by-group

https://www.statalist.org/forums/for...s-of-a-varlist

"Distinct" I suggest to be a better term than "unique".

Last edited by Nick Cox; 21 Jan 2018, 11:19.

Comment

Marius Mai

Join Date: Jan 2018

Posts: 15
#5

21 Jan 2018, 13:40

Thanks, that is what I needed. However, is there a way to include a row at the end of the table calculating the totals of each column, i.e., distinct and years? To my knowledge -tabdisp- does not have such option.
Comment
Nick Cox

Join Date: Mar 2014

Posts: 35698
#6

21 Jan 2018, 16:40

Can a bank be present in different countries? If so, the number of distinct banks in the data will be less than the total across countries.
Comment
Marius Mai

Join Date: Jan 2018

Posts: 15
#7

24 Jan 2018, 10:34

no, banks can be present in one country only. Thus, the table should display the correct values.
Comment

Nick Cox

Join Date: Mar 2014
Posts: 35698

24 Jan 2018, 11:38

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str130 Bank_Name double year str38 country float Deposits double(BV_Equity BV_Assets)
"1ST BANK"                               2005 "UNITED STATES OF AMERICA" .8026316  42 304
"1ST BANK"                               2006 "UNITED STATES OF AMERICA" .7876923  43 325
"1ST BANK"                               2007 "UNITED STATES OF AMERICA" .8026316  67 456
"1ST BANK"                               2008 "UNITED STATES OF AMERICA" .7430407  72 467
"1ST BANK"                               2009 "UNITED STATES OF AMERICA" .6466974 102 651
"1ST BANK"                               2010 "UNITED STATES OF AMERICA" .6550279 107 716
"1ST BANK"                               2011 "UNITED STATES OF AMERICA" .6819338  94 786
"1St Century Bank, National Association" 2008 "UNITED STATES OF AMERICA" .6100386  53 259
"1St Century Bank, National Association" 2009 "UNITED STATES OF AMERICA" .7728938  43 273
"1St Century Bank, National Association" 2010 "UNITED STATES OF AMERICA" .8474026  41 308
"1St Century Bank, National Association" 2011 "UNITED STATES OF AMERICA" .8246914  44 405
"1St Century Bank, National Association" 2012 "UNITED STATES OF AMERICA" .8376753  48 499
"1St Century Bank, National Association" 2013 "UNITED STATES OF AMERICA" .8457249  53 538
"1St Century Bank, National Association" 2014 "UNITED STATES OF AMERICA" .8666667  58 585
"1St Security Bank Of Washington"        2005 "UNITED STATES OF AMERICA" .8196079  34 255
"1St Security Bank Of Washington"        2006 "UNITED STATES OF AMERICA" .8054475  34 257
"1St Security Bank Of Washington"        2007 "UNITED STATES OF AMERICA" .7924528  33 265
"1St Security Bank Of Washington"        2008 "UNITED STATES OF AMERICA"  .844358  30 257
"1St Security Bank Of Washington"        2009 "UNITED STATES OF AMERICA"  .822695  23 282
"1St Security Bank Of Washington"        2010 "UNITED STATES OF AMERICA" .8390411  25 292
"1St Security Bank Of Washington"        2011 "UNITED STATES OF AMERICA" .8697183  27 284
"1St Security Bank Of Washington"        2012 "UNITED STATES OF AMERICA" .8412256  48 359
"1St Security Bank Of Washington"        2013 "UNITED STATES OF AMERICA" .8353222  50 419
"1St Security Bank Of Washington"        2014 "UNITED STATES OF AMERICA" .8443114  56 501
"1St State Bank"                         2007 "UNITED STATES OF AMERICA"     .824  11 125
"1St State Bank"                         2008 "UNITED STATES OF AMERICA" .7826087  12 138
"1St State Bank"                         2009 "UNITED STATES OF AMERICA" .7792208  14 154
"1St State Bank"                         2010 "UNITED STATES OF AMERICA"   .76875  16 160
"1St State Bank"                         2011 "UNITED STATES OF AMERICA"  .770115  17 174
"1St State Bank"                         2012 "UNITED STATES OF AMERICA" .7888889  19 180
"1St State Bank"                         2013 "UNITED STATES OF AMERICA" .8201058  19 189
"1St State Bank"                         2014 "UNITED STATES OF AMERICA" .8657407  19 216
"1St Trust Bank, Inc."                   2009 "UNITED STATES OF AMERICA" .9082569  10 109
"1St Trust Bank, Inc."                   2010 "UNITED STATES OF AMERICA" .9112903  10 124
"1St Trust Bank, Inc."                   2011 "UNITED STATES OF AMERICA" .9124088  12 137
"1St Trust Bank, Inc."                   2012 "UNITED STATES OF AMERICA" .9150327  13 153
"1St Trust Bank, Inc."                   2013 "UNITED STATES OF AMERICA" .8717949  13 156
"1St Trust Bank, Inc."                   2014 "UNITED STATES OF AMERICA" .9076923  15 195
"1st Advantage Bank"                     2008 "UNITED STATES OF AMERICA" .7619048  11 126
"1st Advantage Bank"                     2009 "UNITED STATES OF AMERICA" .7719298  10 114
"1st Bank"                               2007 "UNITED STATES OF AMERICA" .8823529  10 102
"1st Bank"                               2008 "UNITED STATES OF AMERICA" .8415841  10 101
"1st Bank"                               2009 "UNITED STATES OF AMERICA" .8446602  11 103
"1st Bank & Trust"                       2007 "UNITED STATES OF AMERICA" .8942308  10 104
"1st Bank & Trust"                       2008 "UNITED STATES OF AMERICA" .8796296  12 108
"1st Bank & Trust"                       2009 "UNITED STATES OF AMERICA"  .880734  13 109
"1st Bank & Trust"                       2010 "UNITED STATES OF AMERICA" .9074074  10 108
"1st Bank & Trust"                       2011 "UNITED STATES OF AMERICA" .8909091  12 110
"1st Bank & Trust"                       2012 "UNITED STATES OF AMERICA" .8823529  13 119
"1st Bank & Trust"                       2013 "UNITED STATES OF AMERICA" .8823529  14 119
"1st Bank & Trust"                       2014 "UNITED STATES OF AMERICA" .8828125  15 128
"1st Bank Oklahoma"                      2005 "UNITED STATES OF AMERICA" .8175676  11 148
"1st Bank Oklahoma"                      2006 "UNITED STATES OF AMERICA" .7974684  11 158
"1st Bank Oklahoma"                      2007 "UNITED STATES OF AMERICA" .8139535  14 172
"1st Bank Oklahoma"                      2008 "UNITED STATES OF AMERICA" .6990291  17 206
"1st Bank Oklahoma"                      2009 "UNITED STATES OF AMERICA" .6652893  23 242
"1st Bank Oklahoma"                      2010 "UNITED STATES OF AMERICA" .6830189  25 265
"1st Bank Oklahoma"                      2011 "UNITED STATES OF AMERICA" .8359789  19 189
"1st Bank Yuma"                          2005 "UNITED STATES OF AMERICA" .9189189   9 111
"1st Bank Yuma"                          2006 "UNITED STATES OF AMERICA" .8833333  11 120
"1st Bank Yuma"                          2007 "UNITED STATES OF AMERICA" .8534483  12 116
"1st Bank Yuma"                          2008 "UNITED STATES OF AMERICA" .8166667  11 120
"1st Bank Yuma"                          2009 "UNITED STATES OF AMERICA" .8709677  14 124
"1st Bank Yuma"                          2010 "UNITED STATES OF AMERICA" .8880597  15 134
"1st Bank Yuma"                          2011 "UNITED STATES OF AMERICA" .8611111  19 144
"1st Bank Yuma"                          2012 "UNITED STATES OF AMERICA" .8629442  21 197
"1st Bank Yuma"                          2013 "UNITED STATES OF AMERICA" .8844221  21 199
"1st Bank Yuma"                          2014 "UNITED STATES OF AMERICA" .8831776  23 214
"1st Bank of Sea Isle City"              2005 "UNITED STATES OF AMERICA" .8663595  15 217
"1st Bank of Sea Isle City"              2006 "UNITED STATES OF AMERICA" .8298969  16 194
"1st Bank of Sea Isle City"              2007 "UNITED STATES OF AMERICA" .8196721  16 183
"1st Bank of Sea Isle City"              2008 "UNITED STATES OF AMERICA"      .83  17 200
"1st Bank of Sea Isle City"              2009 "UNITED STATES OF AMERICA" .8760331  17 242
"1st Bank of Sea Isle City"              2010 "UNITED STATES OF AMERICA" .9152542  18 236
"1st Bank of Sea Isle City"              2011 "UNITED STATES OF AMERICA" .9045643  20 241
"1st Bank of Sea Isle City"              2012 "UNITED STATES OF AMERICA" .8953975  21 239
"1st Bank of Sea Isle City"              2013 "UNITED STATES OF AMERICA" .8888889  22 234
"1st Bank of Sea Isle City"              2014 "UNITED STATES OF AMERICA" .8841202  23 233
"1st Bank, National Association"         2006 "UNITED STATES OF AMERICA" .8081396  16 172
"1st Bank, National Association"         2007 "UNITED STATES OF AMERICA" .8146341  17 205
"1st Bank, National Association"         2008 "UNITED STATES OF AMERICA" .8221154  19 208
"1st Bank, National Association"         2009 "UNITED STATES OF AMERICA" .8601398  23 286
"1st Bank, National Association"         2010 "UNITED STATES OF AMERICA" .8459017  31 305
"1st Bank, National Association"         2011 "UNITED STATES OF AMERICA" .8745981  29 311
"1st Bank, National Association"         2012 "UNITED STATES OF AMERICA" .8686869  31 297
"1st Bank, National Association"         2013 "UNITED STATES OF AMERICA"     .875  31 304
"1st Bank, National Association"         2014 "UNITED STATES OF AMERICA" .8580247  39 324
"1st Capital Bank"                       2008 "UNITED STATES OF AMERICA" .7862595  28 131
"1st Capital Bank"                       2009 "UNITED STATES OF AMERICA" .8541667  27 192
"1st Capital Bank"                       2010 "UNITED STATES OF AMERICA" .8678414  28 227
"1st Capital Bank"                       2011 "UNITED STATES OF AMERICA" .8888889  32 288
"1st Capital Bank"                       2012 "UNITED STATES OF AMERICA" .8966565  34 329
"1st Capital Bank"                       2013 "UNITED STATES OF AMERICA" .8992248  38 387
"1st Capital Bank"                       2014 "UNITED STATES OF AMERICA" .9096774  41 465
"1st Centennial Bank"                    2005 "UNITED STATES OF AMERICA" .8815789  50 456
"1st Centennial Bank"                    2006 "UNITED STATES OF AMERICA"  .831216  58 551
"1st Centennial Bank"                    2007 "UNITED STATES OF AMERICA" .6952105  62 689
"1st Choice Bank Inc"                    2005 "UNITED STATES OF AMERICA" .9139344  20 244
"1st Choice Bank Inc"                    2006 "UNITED STATES OF AMERICA" .9124579  24 297
"1st Choice Bank Inc"                    2007 "UNITED STATES OF AMERICA" .9042904  27 303
"Some Bank"                              2007 "SOMEWHERE ELSE" . . . 
"Other Bank"                             2007 "SOMEWHERE ELSE" . . . 
end

bysort country : gen years = _N
egen tag_cb = tag(country Bank_Name)
egen distinct = total(tag_cb), by(country)
egen tag_c = tag(country) 
list country distinct years if tag_c, noobs sum(distinct years)

     |                  country   distinct   years |
      |---------------------------------------------|
      |           SOMEWHERE ELSE          2       2 |
      | UNITED STATES OF AMERICA         15     100 |
      |---------------------------------------------|
  Sum |                                  17     102 |
      +---------------------------------------------+

Announcement

Table with unique firms and firm-year observations by country

Comment

Comment

Comment

Comment

Comment

Comment

Comment