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: Extracting data from multiple tabs of an excel spreadsheet and appending in a single stata data file


From   Tim Evans <Tim.Evans@wmciu.nhs.uk>
To   statalist <statalist@hsphsun2.harvard.edu>
Subject   st: Extracting data from multiple tabs of an excel spreadsheet and appending in a single stata data file
Date   Fri, 30 Nov 2012 11:39:41 +0000

Hi all,

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!


Best wishes

Tim




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


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