Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | Eleimon Gonis <Eleimon2.Gonis@uwe.ac.uk> |
To | "statalist@hsphsun2.harvard.edu" <statalist@hsphsun2.harvard.edu> |
Subject | st: Data layout issue |
Date | Fri, 27 Jul 2012 15:30:42 +0100 |
Dear Stata users, I would like your help with an issue I've been facing. I have collected some accounting data on companies (e.g. total assets, capital expenditure, etc) from Datastream and the way the database provided the data is the following: I have an Excel file with multiple sheets, one for each type of accounting data, which has the time period in one column (Year column in below's example) and then each company's data in another column (e.g. ATUK and ABCR below). The good thing is that each sheet has a fixed time period (2000-2012) and also the same companies appear in the same column in each sheet (so no problems in terms of positioning as it were). Sheet for capex variable (first three columns) Year ATUK ABCR 2000 2420 2001 2252 2002 1 1291 2003 7 3080 2004 15 1203 2005 121 1591 2006 792 2123 2007 72 684 2008 8 595 2009 16 441 2010 6 284 2011 44 118 2012 121 My question has to do with how it is that I can start putting this mess (sic) in a long format, where ATUK would go first, then ABCR underneath it, etc, whilst correctly populating the year column too, as I want to run some panel data models later on. In other words, I'd like the above to look like this: Year ID capex 2000 1 . 2001 1 . 2002 1 1 2003 1 7 2004 1 15 2005 1 121 2006 1 792 2007 1 72 2008 1 8 2009 1 16 2010 1 6 2011 1 44 2012 1 . 2000 2 2420 2001 2 2252 2002 2 1291 2003 2 3080 2004 2 1203 2005 2 1591 2006 2 2123 2007 2 684 2008 2 595 2009 2 441 2010 2 284 2011 2 118 2012 2 121 I'm using 11.2 on a Win XP environment at work. Thank you, Eleimon * * 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/