Problems with Dataset

[Serena Gallo]

Hi everyone,
I have a dataset gathered from Boardex and I want to merge it with the Bank Focus database.
However, in the Boardex dataset I have data expressed at the individual/director level by the company and for the same year, for example, 2007, I have more strings for each individual in the board.
I would like to generate for example a new variable "average of Board Time in Company" to obtain a unique string for 2007 or generate a new variable that contains the proportions of females on the male on the board.
How Can I do it?
This is an example of my Boardex dataset

* Example generated by -dataex-. To install: ssc install dataex clear
input str8 AnnualReportYear str25 Country str110 CompanyName str1510 ISIN double TimeinCompany
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.5
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.2
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.2
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.9
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.4
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"    0
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.9
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.6
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.9
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.6
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.3
"Dec 2007" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.6
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.9
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .5
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.4
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.6
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.6
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.2
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.9
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.2
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.9
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.3
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.5
"Dec 2008" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.6
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.2
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.6
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.9
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.4
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.5
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.6
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.9
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.6
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .5
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.5
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.2
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.3
"Dec 2009" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  4.9
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  9.6
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.5
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  9.6
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.9
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.5
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.3
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  5.9
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.2
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"    0
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  9.5
"Dec 2010" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 10.6
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.9
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.3
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 10.5
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.5
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  6.9
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.5
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 10.6
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  1.2
"Dec 2011" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .5
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 11.6
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .7
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .9
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 11.6
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  8.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306" 11.5
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.9
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"   .5
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  2.2
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  3.5
"Dec 2012" "Austria" "RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)" "AT0000606306"  7.9
end

I would like to have the dataset as follows:
Year companyname AverageTimeonboard
2007 x 10
2008 x 8

[/CODE]

[Ken Chui]

This may or may not be what you need, but generally the command that should help is collapse. To learn more, use help collapse.

collapse (mean) TimeinCompany, by(AnnualReportYear CompanyName)

Results:

     +---------------------------------------------------------------------------------------------------------------------+
     | Annual~r                                                                                    CompanyName   TimeinC~y |
     |---------------------------------------------------------------------------------------------------------------------|
  1. | Dec 2007   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)       4.425 |
  2. | Dec 2008   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)   5.3833333 |
  3. | Dec 2009   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)   5.9307692 |
  4. | Dec 2010   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)   2.8521739 |
  5. | Dec 2011   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)   3.9818182 |
  6. | Dec 2012   RAIFFEISEN BANK INTERNATIONAL AG (Raiffeisen International Bank Holding AG prior to 10/2010)   4.4555556 |
     +---------------------------------------------------------------------------------------------------------------------+

[Serena Gallo]

Thanks very much. It seems to work.
However, I have dataset in this way. How Can I obtain like in your example?

* Example generated by -dataex-. To install: ssc install dataex clear
input str8 AnnualReportYear str110 CompanyName double TimeinCompany
"Dec 2007" "1ST INDEPENDENCE FINANCIAL GROUP INC (De-listed 09/2008)"                                                  7.666666666666667
"Dec 2007" "1ST SOURCE CORP"                                                                                          11.392307692307694
"Dec 2007" "AAREAL BANK AG"                                                                                                      5.01875
"Dec 2007" "ABIGAIL ADAMS NATIONAL BANCORP (De-listed 09/2009)"                                                                     8.42
"Dec 2007" "ABINGTON BANCORP INC (De-listed 10/2011)"                                                                                 .5
"Dec 2007" "ABN AMRO HOLDING NV (De-listed 04/2008)"                                                                   6.243478260869564
"Dec 2007" "ACCESS NATIONAL CORP (De-listed 02/2019)"                                                                  5.700000000000001
"Dec 2007" "AGEAS SA/NV"                                                                                               8.242857142857144
"Dec 2007" "AIB GROUP PLC (Allied Irish Banks PLC prior to 12/2017)"                                                   7.357894736842107
"Dec 2007" "AKBANK TURK ANONIM SIRKETI AS"                                                                            17.110000000000003
"Dec 2007" "ALLEANZA ASSICURAZIONI SPA (De-listed 10/2009)"                                                           5.5076923076923086
"Dec 2007" "ALLIANCE & LEICESTER PLC (De-listed 10/2008)"                                                              5.741666666666666
"Dec 2007" "ALLIANCE BANCORP INC OF PENNSYLVANIA (De-listed 10/2015)"                                                  .9000000000000001
"Dec 2007" "ALLIANCE BANKSHARES CORP (De-listed 12/2012)"                                                                         4.6875
"Dec 2007" "ALLIANCE FINANCIAL CORP (De-listed 03/2013)"                                                              6.1923076923076925
"Dec 2007" "ALPHA BANK A E"                                                                                           11.571428571428573
"Dec 2007" "AMCORE FINANCIAL INC (De-listed 05/2010)"                                                                 10.816666666666666
"Dec 2007" "AMERIANA BANCORP (De-listed 12/2015)"                                                                                   13.4
"Dec 2007" "AMERICAN COMMUNITY BANCSHARES INC (De-listed 04/2009)"                                                     6.584615384615384
"Dec 2007" "AMERICAN NATIONAL BANKSHARES INC"                                                                         15.736363636363636
"Dec 2007" "AMERICAN RIVER BANKSHARES (American River Holdings prior to 05/2004)"                                      10.18888888888889
"Dec 2007" "AMERICANWEST BANCORP (United Security Bancorp prior to 03/2001) (De-listed 03/2010)"                       3.166666666666666
"Dec 2007" "AMERICASBANK CORP (De-listed 07/2008)"                                                                      6.06923076923077
"Dec 2007" "AMERIS BANCORP (ABC Bancorp prior to 12/2005)"                                                            12.233333333333333
"Dec 2007" "AMERISERV FINANCIAL INC (USBancorp Inc prior to 05/2001)"                                                 12.731250000000003
"Dec 2007" "AMES NATIONAL CORP"                                                                                       10.366666666666669
"Dec 2007" "ANNAPOLIS BANCORP INC (Annapolis National Bancorp prior to 02/2005) (De-listed 04/2013)"                                8.66
"Dec 2007" "APPALACHIAN BANCSHARES INC (De-listed 10/2009)"                                                           11.599999999999998
"Dec 2007" "ARBUTHNOT BANKING GROUP PLC (Secure Trust Banking Group prior to 06/2005)"                                5.2272727272727275
"Dec 2007" "ARROW FINANCIAL CORP"                                                                                     14.923076923076923
"Dec 2007" "ASSOCIATED BANC-CORP"                                                                                      9.708333333333334
"Dec 2007" "ASTORIA FINANCIAL CORP (De-listed 10/2017)"                                                                            12.11
"Dec 2007" "ATLANTIC BANCGROUP INC (De-listed 11/2010)"                                                                                9
"Dec 2007" "ATLANTIC COAST FINANCIAL CORP (Atlantic Coast Federal Corp prior to 02/2011) (De-listed 05/2018)"          4.133333333333334
"Dec 2007" "ATLANTIC SOUTHERN FINANCIAL GROUP INC (De-listed 01/2011)"                                                 .7133333333333333
"Dec 2007" "ATLANTIC UNION BANKSHARES CORP (Union Bankshares Corp prior to 05/2019)"                                   8.459999999999999
"Dec 2007" "AUBURN NATIONAL BANCORPORATION INC"                                                                                    11.18
"Dec 2007" "AVANZA BANK HLDG AB (Avanza AB prior to 05/2008)"                                                          5.471428571428571
"Dec 2007" "BANC OF CALIFORNIA INC (First PacTrust Bancorp Inc prior to 07/2013)"                                                    5.8
"Dec 2007" "BANCA CR FIRENZE SPA (Cassa di Risparmio di Firenze prior to 01/2006) (De-listed 04/2008)"                 5.246153846153845
"Dec 2007" "BANCA GENERALI SPA"                                                                                       1.0299999999999998
"Dec 2007" "BANCA ITALEASE SPA (De-listed 04/2010)"                                                                    .3545454545454545
"Dec 2007" "BANCA MPS (Banca Monte Dei Paschi di Siena SpA prior to 08/2001)"                                          7.880000000000003
"Dec 2007" "BANCA POPOLARE DI MILANO SCARL (De-listed 01/2017)"                                                                      6.4
"Dec 2007" "BANCA POPOLARE DI SONDRIO SCARL"                                                                          13.540000000000003
"Dec 2007" "BANCFIRST CORP"                                                                                           10.434999999999999
"Dec 2007" "BANCO BPI SA (BPI SGPS prior to 01/2003) (De-listed 12/2018)"                                             12.104761904761906
"Dec 2007" "BANCO COMERCIAL PORTUGUES SA"                                                                             10.305000000000003
"Dec 2007" "BANCO ESPIRITO SANTO SA (De-listed 02/2016)"                                                              10.013333333333337
"Dec 2007" "BANCO GUIPUZCOANO SA (De-listed 11/2010)"                                                                 13.090909090909092
"Dec 2007" "BANCO INTERNACIONAL DO FUNCHAL SA (BANIF SA) (De-listed 01/2016)"                                                       5.55
"Dec 2007" "BANCO PASTOR SA (De-listed 03/2012)"                                                                      20.445454545454545
"Dec 2007" "BANCO POPOLARE SOCIETÀ COOPERATIVA (De-listed 01/2017)"                                                   .3818181818181819
"Dec 2007" "BANCO POPULAR ESPANOL SA (De-listed 06/2017)"                                                             12.326315789473684
"Dec 2007" "BANCO SABADELL SA"                                                                                         5.091666666666668
"Dec 2007" "BANCO SANTANDER SA (Banco Santander Central Hispano SA prior to 08/2007)"                                 11.542105263157893
"Dec 2007" "BANCORP RHODE ISLAND INC (De-listed 01/2012)"                                                              5.873333333333333
"Dec 2007" "BANCORPSOUTH BANK (BancorpSouth Inc prior to 11/2017)"                                                    14.533333333333333
"Dec 2007" "BANCTRUST FINANCIAL GROUP INC (De-listed 02/2013)"                                                        10.421428571428574
"Dec 2007" "BANESTO - BANCO ESPANOL DE CREDITO SA (De-listed 05/2013)"                                                 7.507142857142857
"Dec 2007" "BANK FUR TIROL UND VORARLBERG AG"                                                                                       8.36
"Dec 2007" "BANK HANDLOWY W WARSZAWIE SA"                                                                               4.88421052631579
"Dec 2007" "BANK MUTUAL CORP (De-listed 02/2018)"                                                                     10.466666666666665
"Dec 2007" "BANK OF AMERICA CORP"                                                                                      5.182352941176472
"Dec 2007" "BANK OF COMMERCE HOLDINGS (Redding Bancorp prior to 05/2004)"                                                          12.95
"Dec 2007" "BANK OF CYPRUS HOLDINGS PLC (Bank of Cyprus Public Company Ltd prior to 01/2017)"                         11.211764705882354
"Dec 2007" "BANK OF FLORIDA CORP (Bancshares of Florida Inc prior to 01/2007) (De-listed 06/2010)"                     6.099999999999999
"Dec 2007" "BANK OF GRANITE CORP (De-listed 10/2011)"                                                                  9.600000000000001
"Dec 2007" "BANK OF HAWAII CORP"                                                                                       7.450000000000002
"Dec 2007" "BANK OF KENTUCKY FINANCIAL CORP (De-listed 06/2015)"                                                      10.770000000000001
"Dec 2007" "BANK OF MARIN BANCORP"                                                                                                    .4
"Dec 2007" "BANK OF MOSCOW (BMO) (De-listed 05/2016)"                                                                 3.2692307692307687
"Dec 2007" "BANK OF NEW YORK MELLON CORP (THE)"                                                                        .4000000000000001
"Dec 2007" "BANK OF PIRAEUS SA"                                                                                                    7.525
"Dec 2007" "BANK OF SOUTH CAROLINA CORP"                                                                                         9.78125
"Dec 2007" "BANK OF THE CAROLINAS CORP (De-listed 03/2012)"                                                                          1.6
"Dec 2007" "BANK OZK (Bank of the Ozarks Inc prior to 07/2018)"                                                       11.338461538461539
"Dec 2007" "BANK PEKAO SA"                                                                                             5.264705882352942
"Dec 2007" "BANKFINANCIAL CORPORATION"                                                                                               2.2
"Dec 2007" "BANKINTER SA"                                                                                                        11.9875
"Dec 2007" "BANNER CORP"                                                                                              11.626666666666667
"Dec 2007" "BANQUE CANTONALE DE GENEVE"                                                                                4.463636363636365
"Dec 2007" "BANQUE CANTONALE VAUDOISE"                                                                                 4.542857142857143
"Dec 2007" "BANQUE NATIONALE DE BELGIQUE (National Bank of Belgium)"                                                               15.95
"Dec 2007" "BAR HARBOR BANKSHARES INC"                                                                                 6.357142857142856
"Dec 2007" "BARCLAYS PLC"                                                                                                          5.875
"Dec 2007" "BAY BANCORP INC (Carrollton Bancorp prior to 11/2013) (De-listed 04/2018)"                                 8.433333333333334
"Dec 2007" "BAY NATIONAL CORP (De-listed 06/2010)"                                                                     6.921428571428571
"Dec 2007" "BAYERISCHE HYPO-UND VEREINSBANK AG (HVB) (De-listed 09/2008)"                                              5.346666666666666
"Dec 2007" "BBVA - BANCO BILBAO VIZCAYA ARGENTARIA SA"                                                                 6.121428571428572
"Dec 2007" "BBX CAPITAL CORP (BankAtlantic Bancorp Inc prior to 08/2012) (De-listed 12/2016)"                                      10.05
"Dec 2007" "BCB BANCORP INC"                                                                                           4.427272727272729
"Dec 2007" "BEACH FIRST NATIONAL BANCSHARES INC (De-listed 04/2010)"                                                   11.22857142857143
"Dec 2007" "BEACON FEDERAL BANCORP INC (De-listed 10/2012)"                                                           .19999999999999998
"Dec 2007" "BEAR STATE FINANCIAL INC (First Federal Bancshares of Arkansas Inc prior to 05/2014) (De-listed 04/2018)"               8.52
"Dec 2007" "BENEFICIAL BANCORP INC (Beneficial Mutual Bancorp Inc prior to 01/2015) (De-listed 03/2019)"               .4000000000000001
"Dec 2007" "BENI STABILI SPA SIIQ (Beni Stabili SPA prior to 01/2011) (De-listed 12/2018)"                             3.628571428571429
"Dec 2007" "BENJAMIN FRANKLIN BANCORP INC (De-listed 04/2009)"                                                         7.378571428571431
"Dec 2007" "BERKSHIRE BANCORP INC (De-listed 11/2013)"                                                                13.483333333333334
"Dec 2007" "BERKSHIRE HILLS BANCORP INC"                                                                               5.815384615384616
end

[Ken Chui]

Sorry I do not understand what the problem is. The data in #3 only contains data from 2007 and it's not possible to get multiple years summary using this sample data. Have you tried running the code in #2, and then use gsort CompanyName AnnualReportYear to see data?

If it didn't work, please follow the FAQ's suggestions and clearly describe what didn't work. I don't have the full data and thus I cannot understand how "it seems to work" but then also not working.

[Serena Gallo]

I want the data in panel format:
Year Company name
2007 Bank x
2008 Bank x
2009 Bank x

This command, instead, orders different banks for the same year
Year Company name
2007 Bank x
2007 Bank y
2007 Bank z