Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: Merge files of tables with headers and multiple identifiers


From   Minsoo Kim <ibsulkim@gmail.com>
To   statalist <statalist@hsphsun2.harvard.edu>
Subject   st: Merge files of tables with headers and multiple identifiers
Date   Wed, 13 Jul 2011 00:01:41 -0500

Hi,

I am using STATA SE/11.2 and I have been trying to merge ten excel
files(2000~2009). Each excel file has about 600 hundreds of tables
with headers for each table and with two empty rows between each
table.
I would like to illustrate what I need to do with two excel
files(2000, 2001) for now. The original exce files I have can be
simiplified as follows:

<Excel File1>

Company Name     Year of File         Table Name
           Gross Earning      Oper. Exp & Taxes     Net Earnings
     ABERD               2000          Income Statement          1997
                 300                         50
     ABERD               2000          Income Statement          1998
                 311                         52
  31
     ABERD               2000          Income Statement          1999
                 323                         55
  32


Company Name     Year of File         Table Name            Asset
  Road and Equipment       Securities Owned        Total
     ALABA               2000              Balance Sheet          1997
                  60                                20
      500
     ALABA               2000              Balance Sheet          1998
                  62                                23
      502


Company Name     Year of File         Table Name          Liabilities
    Captial Stock                 Bonded Debt               Total
     ALABA               2000              Balance Sheet          1997
                70                                  10
       500
     ALABA               2000              Balance Sheet          1998
                76                                  12
       502


<Excel File2>

Company Name     Year of File         Table Name
           Gross Earning      Oper. Exp & Taxes     Net Earnings
Surplus for Year
     ABERD               2001          Income Statement          1998
                 311                         52
31
     ABERD               2001          Income Statement          1999
                 323                         55
32
     ABERD               2001          Income Statement          2000
                 303                         53
30                           16


Company Name     Year of File         Table Name            Asset
  Road and Equipment       Securities Owned        Total        Cash
     ABERD               2001              Balance Sheet          1998
                  62                                23
      502
     ABERD               2001              Balance Sheet          1999
                  63                                22
      503            15


Company Name     Year of File         Table Name          Liabilities
    Captial Stock         Total
     ABERD               2001              Balance Sheet          1998
                76                    502
     ABERD               2001              Balance Sheet          1999
                72                    503



<Goal Data Set>

Company Name     Year of File         Table Name
           Gross Earning      Oper. Exp & Taxes     Net Earnings
Surplus for Year
     ABERD               2000          Income Statement          1997
                 300                         50
     ABERD               2000          Income Statement          1998
                 311                         52
31
     ABERD               2000          Income Statement          1999
                 323                         55
32
     ABERD               2001          Income Statement          2000
                 303                         53
30                           16


 Company Name     Year of File         Table Name           Asset
  Road and Equipment       Securities Owned        Total      Cash
     ABERD               2000              Balance Sheet          1997
                  60                                20
      500
     ABERD               2000              Balance Sheet          1998
                  62                                23
      502
     ABERD               2001              Balance Sheet          1999
                  63                                22
      503            15


Company Name     Year of File         Table Name          Liabilities
    Captial Stock                 Bonded Debt               Total
     ABERD               2000              Balance Sheet          1997
                70                                  10
       500
     ABERD               2000              Balance Sheet          1998
                76                                  12
       502
     ABERD               2001              Balance Sheet          1999
                72
         503



As above, I need to add data of other nine excel files into the first
data set(the earliest excel file). If it doesn't have any match with
the first excel file, I still need to add it into the first data set
in a seperate table.
As there are headers in each table and multiple identifiers, merge
command by itself cannot generate the Goal Data Set as I desire.
Can anyone help me to make resolve this issue?

Thanks,


--
Minsoo
The University of Chicago

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index