Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Federico Nutarelli
    started a topic Reshape wide

    Reshape wide

    Hi all,

    I would like to reshape wide my unbalanced database which looks as follow:


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double(idfirm Year) float(inflow_ratio outflow_ratio avsales) double(tot_sales nprod)
     1 2009          0           0  83603.08  308746.0587639413   4
     1 2015          0   .05263158  52369.71  768412.7578732732  18
     1 2010        .25           0  91092.75  407237.1453954251   5
     1 2012   .2857143           0  104542.8  664061.6305459269   9
     1 2014   .3571429           0  48557.22  715163.0758130773  19
     1 2011         .4           0 118792.94  598549.0481277383   7
     1 2013   .5555556           0 73541.766    608140.67505112  14
     1 2008          .           .  94701.45 211591.55354704778   4
     2 2011          0           0   5262992  10580189.86100467   6
     2 2009          0           0 1841746.4 12892224.398947667   7
     2 2010          0   .14285715   5212612 10897763.444727022   6
     2 2007          0           0   6330808 11406777.403632266   6
     2 2015          0           0   1356981  5427924.711631365   4
     2 2014          0    .3333333   1785749  7133930.997182059   4
     2 2005          0           0   2109991 11513177.507807251   6
     2 2012          0           0 2037856.5 10967562.283051893   6
     2 2013          0           0 1823603.5  9124157.813647183   6
     2 2006          0           0 2050668.5 12304011.532418355   6
     2 2008  .16666667           0 2734569.5 12355367.684264863   7
     2 2004          .           . 2039558.8  12237352.38962967   6
     3 2011          0     .372093 2412.5247  35362.49685707843  27
     3 2006          0   .03508772  67388.88  1843225.499704768  55
     3 2014          0    .2105263 1496.9843  21090.64112449751  15
     3 2015          0   .26666668  4020.239  23837.82400447101  11
     3 2008          0   .21276596  7850.393  160865.5648153848  37
     3 2012          0    .4074074  1075.685 16035.562916347304  16
     3 2009          0    .1081081  6033.619   82650.9899064962  33
     3 2007          0   .14545454  11123.49  476002.8259390887  47
     3 2013      .1875           0   3456.24 22272.424034809985  19
     3 2005  .23913044           0  81622.65   4406664.46751826  57
     3 2010   .3030303           0 4410.1416 126687.58501765266  43
     3 2004          .           .  53702.81 2464566.2638161103  46
     4 2015          0 .0091743115  176931.8  29946995.67406721 324
     4 2011 .014336918           0   70322.5 12433251.178176137 283
     4 2010 .025735294           0  62012.55 12531817.194234656 279
     4 2012  .03180212           0  64410.49 11206517.411851741 292
     4 2005  .03947368           0  78010.12  8251045.135936161 158
     4 2013  .05821918           0     79104 14594676.742348071 309
     4 2014  .05825243           0 119126.19 26481277.906628065 327
     4 2009  .07509881           0 73256.164  13834060.86932386 272
     4 2006   .0949367           0  94482.98  9847010.316997783 173
     4 2008   .1712963           0  78780.68 13768126.595234673 253
     4 2007   .2485549           0 126469.87 13503137.133153718 216
     4 2004          .           .  56691.86  8398246.263306491 152
     5 2007          .           . 157.76617   39.4415442669969   1
     6 2009          0           0  5520.829 1380.2071011962253   1
     6 2007          0           0  9421.785  9421.785319502253   1
     6 2006          0           0  496491.9 496491.87314146105   1
     6 2008          0           0  1577.368  394.3420099042942   1
     6 2005          0           0   5704382  5704381.338495776   1
     6 2004          .           .  28066686  28066686.19154623   1
     7 2014          0           0  693862.8 173465.68589452517   1
     7 2015          0           0 31625.557  31625.55690904084   1
     7 2012          0           0   1318229 329557.26253078564   1
     7 2013          0           0 1220001.8  305000.4474947418   1
     7 2010          0           0 1804061.4  451015.3511721381   1
     7 2009          0           0  381988.5   381988.536163157   1
     7 2008          0           0  42076.49  42076.49109553711   1
     7 2011          0           0  270585.6 270585.60116440145   1
     7 2007          .           .  35459.79  35459.78814655074   1
     8 2014          0           0  33949468 2359259573.3842134  99
     8 2006          0    .1627907  18472182  1598121054.119387 108
     8 2005          0   .03007519  18729456 1689540069.7170644 129
     8 2015          0   .05050505  29031148  2370530389.480409  94
     8 2010          0    .0786517  35879268  2181284539.324025  82
     8 2009          0    .1010101  34457748 2201020370.9059067  89
     8 2007          0    .0462963  32214094  1656992400.409758 103
     8 2008          0   .03883495  36879372   2142910992.52344  99
     8 2011 .024390243           0  32816464 2029940306.3960855  84
     8 2013  .05319149           0  46580236  2727305730.021949  99
     8 2012  .11904762           0  27333772 2225894374.1270456  94
     8 2004          .           .  11277370  1499855855.865098 133
     9 2011          0           0 471647968  12248782762.78322  34
     9 2010          0           0 775569536 11844314656.874342  34
     9 2009          0           0 619878528 11052308521.725805  34
     9 2013          0    .0909091 670351680 11978041077.652853  30
     9 2006          0   .03030303 711789248 11125455014.939121  32
     9 2005          0           0 398063520 10607521709.816193  33
     9 2007          0           0 485468992 10999908825.891459  32
     9 2012          0  .029411765 423355936 12198970579.756456  33
     9 2015  .03030303           0 532608064 16695333854.076557  34
     9 2008      .0625           0 471612384 11020835162.138296  34
     9 2014         .1           0 574183808 13100594403.908373  33
     9 2004          .           . 300541632   9917874207.75519  33
    10 2004          .           . 10196.007 20392.013641854945   2
    11 2014          0           0 124664.38  31166.09542619379   1
    11 2010          0           0 302000.13  514778.3255435499   2
    11 2009          0           0 1215518.4  2431036.790986024   2
    11 2012          0          .5 1857.2793 1857.2792628005145   1
    11 2013          0           0 229202.33  57300.58370722538   1
    11 2011          0           0 133911.64  66955.82008718849   2
    11 2008          .           .  830263.1 1020956.3147491836   2
    12 2015          0           0 120786.42 120786.41920184325   1
    12 2014          0           0  92413.03  92413.03425338621   1
    12 2013          0           0  46875.85 46875.852646417305   1
    12 2012          .           .  5597.375 1399.3437130821144   1
    13 2008          0           0   7242266 3626933.5455257315   2
    13 2007          0           0  11139598  5607216.736637451   2
    13 2005          0           0   5632205  5632204.502071029   1
    13 2009          0           0 138602.83 277205.67300430126   2
    end
    I intend to do that for two reasons:

    1) multiple imputation of missing values for some of the variables (inflow_ratio and outflow_ratio)
    2) apply cam (coerced exact matching)

    Any idea on how I can proceed?

    P.s. of course I have already tried the following:

    Code:
     xtset idf Year
     mi set mlong
     mi reshape wide, i(idf) j(Year)
    but emerged an error stating that my variables were not constant within idfirm (which I do not understand what it does mean...maybe is unbalancedness):

    Code:
    variable data_lancio_molecola not constant within idfirm
    variable inflow not constant within idfirm
    variable outflow not constant within idfirm
    variable nprod not constant within idfirm
    variable avsales not constant within idfirm
    variable avsales_existing not constant within idfirm
    variable avsales_new not constant within idfirm
    variable tot_sales not constant within idfirm
    variable recalled_dummy_byfirm not constant within idfirm
    variable internationalproduct not constant within idfirm
    variable molecule not constant within idfirm
    variable newmolmarket not constant within idfirm
    variable newmolfirm not constant within idfirm
        Your data are currently long.  You are performing a reshape wide.  You typed something like
    
            . reshape wide a b, i(idfirm) j(Year)
    
        There are variables other than a, b, idfirm, Year in your data.  They must be constant within
        idfirm because that is the only way they can fit into wide data without loss of information.
    
        The variable or variables listed above are not constant within idfirm.  Perhaps the values are in
        error.  Type reshape error for a list of the problem observations.
    
        Either that, or the values vary because they should vary, in which case you must either add the
        variables to the list of xij variables to be reshaped, or drop them.
    Many thanks,

    Federico
    Last edited by Federico Nutarelli; 10 Jan 2019, 08:19.

  • Federico Nutarelli
    replied
    I tried to do so...but actually emerged another issue:

    Code:
    at least one imputation variable required: (method ...) ivar(s)
     -- above applies to specification (regress )
    
    mi impute chained: invalid specification;
        see above error messages
    ...

    Leave a comment:


  • Justin Blasongame
    replied
    I could help with the reshape issue, but I'm not familiar with mi. I'm not sure if this will work or if it's what you're after but for what it's worth you can try
    Code:
    reshape wide inflow_ratio outflow_ratio avsales tot_sales  nprod, i(idfirm) j(Year)
    mi set wide
    mi register imputed inflow_ratio* outflow_ratio* 
    mi xtset, clear
    mi impute chained (regress) inflow_ratio* (regress) outflow_ratio* (regress) agefirm* (regress), add(10) rseed (091107)

    Leave a comment:


  • Federico Nutarelli
    replied
    Many thanks for your prompt reply,

    I corrected the code but it still appears the error in #2 after having reshaped. My code after the reshape is as follows:

    Code:
    mi reshape wide inflow_ratio outflow_ratio nprod avsales avsales_existing avsales_new agefirm tot_sales recalled_dummy_byfirm newmolfirm newmolmarket, i(idfirm) j(Year)
    
    *mi convert wide, clear
    mi xtset, clear
    mi register imputed inflow_ratio* outflow_ratio* agefirm*
    mi impute chained (regress) inflow_ratio* (regress) outflow_ratio* (regress) agefirm* (regress), add(10) rseed (091107)
    Thanks again!

    Leave a comment:


  • Justin Blasongame
    replied
    It looks like your problem is here
    Code:
    mi reshape wide, i(idf) j(Year)
    You're not telling Stata what to reshape. I think this is what you want
    Code:
    xtset idfirm Year
    mi set mlong
    mi reshape wide inflow_ratio outflow_ratio avsales tot_sales  nprod, i(idfirm) j(Year)

    Leave a comment:


  • Federico Nutarelli
    replied
    Just a quick news: I managed to solve the last problem of not constant values, but actually another problem emerged in the multiple imputation step:

    Code:
        Your mi data are xtset and some of the variables previously declared by xtset are not in the
        dataset.  mi verifies that none of the xtset variables are also registered as imputed or passive.
        Type mi xtset, clear to clear old no-longer-valid settings.

    Leave a comment:

Working...
X