reshape panel data for fixed effects regression

[Tom Kreuzer]

Dear Statalist,

I'm having difficulties with reshaping my paneldata. I have estimated betas of 500 companies over 15 years from a first regression. I only want to include significant betas in my second stage that is why I have an unbalanced Panel.

The way that I have now arranged them is(in short):

I want to reshape the data to become like this for a fixed effects regression:

State	Year	company
beta	1999	1
beta	1999	2
beta	1999	3
beta	1999	4
beta	1999	5
beta	2000	1
beta	2000	2
beta	2000	3
beta	2000	4
beta	2000	5 etc.

I tried to orientate on the example of Tom regt and the advice of Nick Cox here:
http://www.statalist.org/forums/foru...ing-panel-data

I used the code:

reshape long company, i(state year)

But stata showed up with a error r(498) "_j contains all missing values"

Have I done something wrong?
Is there a better way to organize my panel data structure?

Any help is appreciated!
Tom

[Carlo Lazzaro]

Tom:
you may want to try:

reshape long company_, i( year ) j(company)

[William Lisowski]

Perhaps

reshape long company_, i(state year) j(company)
rename company_ value

or

rename company_* value*
reshape long value, i(state year) j(company)

Not tested, since Stata can't read pictures of sample data. To improve your future posts, please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post, looking especially at sections 9-12 on how to best pose your question. In particular, please read FAQ #12 and use dataex and CODE delimiters when posting to Statalist. The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Jorrit Gosens]

comapre the result of doing either of the follwoing two:

reshape long company_, i(state year) j(companynumber)

reshape long company, i(state year) j(companynumber) string

[Tom Kreuzer]

Thank you very much for all the advice. I tried the various suggestions provided above, but Stata issued an error message:

i=state year does not uniquely identify the observations;
there are multiple observations with the same value of state year.
Type "reshape error" for a listing of the problem observations.
r(9)


Following Williams suggestion, I make use of dataex to provide examples of my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str11 state double(company_1 company_2 company_3 company_4 company_5)
1995 "beta"                          .           .                  .           .                  .
1996 "beta"                          .           .                  .           .                  .
1997 "beta"                          .           .                  .           .                  .
1998 "beta"                          .           .                  .           .                  .
1999 "beta"                          .           .                  .           .                  .
2000 "beta"                          .           .                  .           .                  .
2001 "beta"                          .           .                  .           .                  .
2002 "beta"                          .           .                  .           .                  .
2003 "beta"                          .           .                  .           .                  .
2004 "beta"        -.35796858147333616           .                  .           .                  .
2005 "beta"                          .           .                  .           .                  .
2006 "beta"          -.872861626626529           .                  .           .                  .
2007 "beta"                          .           .                  .           .                  .
2008 "beta"                          .           .                  .           .                  .
2009 "beta"                          .           .                  .           .                  .
2010 "beta"                          .           .                  .           .                  .
2011 "beta"                          .           .                  .           .                  .
2012 "beta"                          .           .                  .           . -.9748186321555409
2013 "beta"                          .           .                  .           .                  .
2014 "beta"          .6433204665971168           . 1.5217393461302873           .                  .
2015 "beta"                          .           .                  .           .                  .
1995 "TotalAssets"          1.3068e+10  8523724000                  .           .           68883000
1996 "TotalAssets"          1.4183e+10  9412580000                  .           .           77613000
1997 "TotalAssets"          1.3364e+10 1.11256e+10                  .           .          119754000
1998 "TotalAssets"          1.3238e+10 12101825000                  .           .          229280000
1999 "TotalAssets"          1.4153e+10 13259919000                  .  4615037000          283345000
2000 "TotalAssets"          1.3896e+10 14471044000                  .  5451297000          309737000
2001 "TotalAssets"          1.4522e+10 15283254000                  .  6061356000          359957000
2002 "TotalAssets"          1.4606e+10 23296423000                  .  5478948000          556887000
2003 "TotalAssets"          1.5329e+10 23592680000                  .  6459240000          704816000
2004 "TotalAssets"           1.760e+10 26039308000                  .  8013484000          968817000
2005 "TotalAssets"          2.0708e+10 28767494000                  .  8957352000         1305919000
2006 "TotalAssets"          2.0541e+10 29141203000                  .  9497492000         1418255000
2007 "TotalAssets"          2.1294e+10 36178172000         1.5669e+10 10747162000         1418255000
2008 "TotalAssets"          2.4694e+10 39713924000         1.6601e+10 12398525000          8.790e+08
2009 "TotalAssets"          2.5793e+10 42419204000         1.5858e+10 12255734000         1.4465e+10
2010 "TotalAssets"           2.725e+10 52581623000        21134705000 12835253000         1.3742e+10
2011 "TotalAssets"          3.0156e+10 60573852000        21134705000 15731510000         1.3447e+10
2012 "TotalAssets"          3.1616e+10 60276893000         1.9521e+10 16665415000         1.3277e+10
2013 "TotalAssets"          3.3876e+10  6.7235e+10         2.7008e+10 16867049000          1.420e+10
2014 "TotalAssets"           3.355e+10  4.2953e+10         2.9198e+10 17930452000         1.4012e+10
2015 "TotalAssets"          3.1209e+10  4.1207e+10         2.7513e+10 18202647000         1.4642e+10
   . ""                              .           .                  .           .                  .
   . ""                              .           .                  .           .                  .
end

I guess companies with all missing values with the state "beta" could also be a problem (see company_4).
I tried to drop all companies with less than 2 obseravations within the state "beta" using the code:

foreach company of varlist company_*{
sum `company'
if r(N)<2 drop `company' if state=beta
}

But I think I made a mistake by only addressing the number of observations within the "beta" category.

Thank you very much for further advice.

Tom

[Carlo Lazzaro]

Tom.
you can create a slave variable that allows you to -reshape- your dataset from -wide- to -long-:
[CODEg slave=_n]
reshape long company_, i(slave) j(company)
drop slave[/CODE]

The further step would consist in cleaning up your dataset: however, I suspect that deleting observations without a sound rationale can hamper more than help the subsequent analyses.

[William Lisowski]

Stata not only gave you an error message, it told you exactly what the problem is - observations with duplicated values of the combination of state and year - and it instructed you to type

reshape error

to find those observations. Using Stata 14.2 to read in the sample data we then see

. reshape long company_, i(state year) j(company)
(note: j = 1 2 3 4 5)
variable id does not uniquely identify the observations
    Your data are currently wide.  You are performing a reshape long.  You specified i(state
    year) and j(company).  In the current wide form, variable state year should uniquely
    identify the observations.  Remember this picture:

         long                                wide
        +---------------+                   +------------------+
        | i   j   a   b |                   | i   a1 a2  b1 b2 |
        |---------------| <--- reshape ---> |------------------|
        | 1   1   1   2 |                   | 1   1   3   2  4 |
        | 1   2   3   4 |                   | 2   5   7   6  8 |
        | 2   1   5   6 |                   +------------------+
        | 2   2   7   8 |
        +---------------+
    Type reshape error for a list of the problem observations.
r(9);

. reshape error
(note: j = 1 2 3 4 5)

i (state year) indicates the top-level grouping such as subject id.

The data are currently in the wide form; there should be a single
observation per i.

2 of 44 observations have duplicate i values:

     +--------------+
     | state   year |
     |--------------|
  1. |            . |
  2. |            . |
     +--------------+

(data now sorted by state year)

.

which confirms what can be seen by looking at the data posted in #5 above: the final two observations have both state and year missing. Fix that, for example with

drop if year==.

and the reshape will run.

. reshape long company_, i(state year) j(company)
(note: j = 1 2 3 4 5)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       42   ->     210
Number of variables                   7   ->       4
j variable (5 values)                     ->   company
xij variables:
      company_1 company_2 ... company_5   ->   company_
-----------------------------------------------------------------------------

. 
end of do-file

. list in 1/10

     +------------------------------------------+
     |       state   year   company    company_ |
     |------------------------------------------|
  1. | TotalAssets   1995         1   1.307e+10 |
  2. | TotalAssets   1995         2   8.524e+09 |
  3. | TotalAssets   1995         3           . |
  4. | TotalAssets   1995         4           . |
  5. | TotalAssets   1995         5    68883000 |
     |------------------------------------------|
  6. | TotalAssets   1996         1   1.418e+10 |
  7. | TotalAssets   1996         2   9.413e+09 |
  8. | TotalAssets   1996         3           . |
  9. | TotalAssets   1996         4           . |
 10. | TotalAssets   1996         5    77613000 |
     +------------------------------------------+

Finally, with regard to

I guess companies with all missing values with the state "beta" could also be a problem (see company_4).
I tried to drop all companies with less than 2 obseravations within the state "beta" using the code:

that clearly causes no problem with the reshape.