Re: st: Extracting data from multiple tabs of an excel spreadsheet and appending in a single stata data file

[Nick Cox <njcoxstata@gmail.com>]

Upgrade to Stata 12 is a serious, although perhaps not practical,
suggestion. -import excel- is a major feature that you are missing.

Otherwise I think you are best advised save separate worksheets as
text files, and then -insheet- and -append- in a loop.

I'd advise against cut and paste. Copy and paste makes more sense, but
the method above is surely better, especially as you may need to
incorporate other data management commands in each case and it is best
to have a script as a record of all you did.

On a slightly personal but still general note I get a sense that
-destring-, which I wrote originally because people were using Stata's
then new Data Editor like a spreadsheet and getting into messes by
typing in header information spreadsheet-style, seems far more often
to be used for copying with string variables  imported from
spreadsheets that shouldn't be string.

Nick

On Fri, Nov 30, 2012 at 11:39 AM, Tim Evans <Tim.Evans@wmciu.nhs.uk> wrote:

> I'm using Stata 11.2.
>
> I have an Excel file with multiple tabs, containing data in a consistent format throughout.
>
> The excel file contains life expectancies for males and females and the data variables look like this:
>
> Sex age         mx              qx              lx              dx      ex              sex mx          qx              lx              dx      ex
> 1   0         0.005006  0.004993        100000.0        499.3   78.05           2   0.004152    0.004143        100000.0        414.3   82.12
> to
> 100
>
> Each tab of the excel file relates to a specific time period, for instance, 2008-10, 2007-09 through to 1985-87. Note that the age variable is only present on the right hand side of the data.
>
> What I would like to do is to create one data file for use in Stata, in the format:
>
> Year sex age mx         qx              lx              dx      ex
> 1987    1   0  0.005006         0.004993        100000.0        499.3   78.05
> 1987  2   0  0.004152   0.004143        100000.0        414.3   82.12
>
>
> Where Year is equal to the last value of the tab name (2008-10 would be 2010 and 1985-87 would be 1987).
>
> Any suggestions appreciated - otherwise a horrible cut and paste job is in store!
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/