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

[Tim Evans <Tim.Evans@wmciu.nhs.uk>]

Nick,

Thanks for your suggestion. Stata 12 is but a distance dream with regards to an upgrade!! Cut and paste was a typo - copy and paste was meant.

I do have StatTransfer, which indicates it can concatenate worksheets, so I'll give that a go, otherwise I'll go down the other option you suggested.

Best wishes

Tim


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Nick Cox
Sent: 30 November 2012 11:52
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: Extracting data from multiple tabs of an excel spreadsheet and appending in a single stata data file

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/

_DISCLAIMER:
This email and any attachments hereto contains proprietary information, some or all of which may be confidential or legally privileged. It is for the exclusive use of the intended recipient(s) only. If an addressing or transmission error has misdirected this e-mail and you are not the intended recipient(s), please notify the author by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail or any attachments, as this may be unlawful.


*
*   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/