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: Data layout issue


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/


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