Re: st: Data layout issue

[David Ashcraft <ashcraftd@rocketmail.com>]

There is a very simple tutorial Datastream data conversion for stata ready file. You can access it from:

http://dss.princeton.edu/usingdata/stata/dataman/reshapeds.htm ;


David


----- Original Message -----
From: Eleimon Gonis <Eleimon2.Gonis@uwe.ac.uk>
To: "statalist@hsphsun2.harvard.edu" <statalist@hsphsun2.harvard.edu>
Cc: 
Sent: Friday, July 27, 2012 5:30:42 PM
Subject: st: Data layout issue

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/


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