reshape dataset layout

[Qingyang Xu]

Hi guys, I am new to stata and have trouble in reshape the layout of my dataset.

So at this time, the data I collect from datastream is in this layout:

And I want to reshape it into this kind of layout:

Is there any way for me to do so? I spent the whole day yesterday looking at sort and reshape function but still no idea where to start my work. I am grateful for any kind of help and hint.

[Clyde Schechter]

Well, the tableau you show is suggestive, but it's not a possible Stata data set in that things like firm1-var1 are not possible varnames: only digits, letters, and underscore (_) characters are allowed. Since -reshape- code relies intensely on the details of the variable names, it is not really possible to confidently propose code that will work for you. It would have been far more helpful had you shown a real data example from your actual Stata data set using the -dataex- command.

That said, I will show you the general approach using a demonstration data set that looks more or less like your tableau:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mdate firm1_var1 firm1_var2 firm1_var3 firm1_var4 firm1_var5 firm1_var6 firm1_var7 firm2_var1 firm2_var2 firm2_var3 firm2_var4 firm2_var5 firm2_var6 firm2_var7 firm3_var1 firm3_var2 firm3_var3 firm3_var4 firm3_var5 firm3_var6 firm3_var7)
599   .9472316   .7800556     .587661   .4575412  .7526946 .11324778     .8159919  .3224011  .5222717  .21660325 .50994456  .30335885  .7215877   .21465635  .4405374   .4373816   .8502334   .2106113  .29453346  .4883266  .14044325
600  .05222337 .015391795   .58383894   .4056266   .627162  .4035936 .00028821192  .8752485  .8128985   .6231912  .5060895   .7084118  .6140801    .6058227  .8109628  .56283796   .7269681   .6391397   .6051359  .6101886   .9587523
601   .9743183    .574749    .6502236  .10792727     .6639  .7056643     .3804336  .9153903 .53831494   .7862822 .38687575   .9936326 .13111094    .7316875 .07246409  .29152772   .6870763   .4236866  .29465795  .7730114   .9713359
602   .9457484   .6551434     .421985   .7559011 .18422593  .5731504    .12547621  .5912329  .4184419 .004203058  .7252148  .29127693  .7384753   .14340399  .7352452   .9189357   .3651932   .7563699    .934348  .6664088   .9789942
603  .18564783  .53569984    .7049119   .9501313 .28977874 .09911993     .8984238  .3718975 .02413011  .23491123 .04936334   .9030942  .8670255     .435995  .4810134   .4308617    .306365   .3757029   .3977599  .8286883  .15681207
604   .9487334    .399902  .071344234   .1730719 .54863846   .992569     .6826897  .7271155  .8008212   .8358622  .4846969   .7233624    .68184    .6163042  .4488823   .8839191    .591621  .19234246   .7007847  .6733498   .6432714
605   .8825376    .356696    .9762383  .15291964 .58717006  .3708564   .003448891   .742907 .28695825   .2875256 .05717023   .7255588 .27689284    .7886472 .59292585   .8466811  .03972807    .483353   .8151794  .6131955  .39555255
606   .9440776   .7333733     .470753   .9149532 .24046357  .6126292     .8822002  .8368753  .5523863   .4829012 .01275296   .6869112 .11522907    .5077908  .5985539  .11466528   .6128506   .6434054     .79322  .7551122   .6708255
607  .08942585   .5795121    .9566074   .6809552  .2239969  .8929192     .5584789  .9620744  .3937416   .7304509  .9186962  .04706742  .3282427    .6408654 .21865103  .10681064   .3638305   .1675173  .51681155 .20058893   .8392906
608   .7505445   .8314545     .058898   .3250927  .5621675 .56029755     .7529196  .7559323 .53916496  .53706163  .6139446   .9708208  .1559232    .6631094 .27223426   .8354875    .858534   .3687886    .826618  .1901031  .23890825
609   .9484983   .2258395   .40200615   .5950673  .5482864  .4706297     .6922931  .7972363  .9865241 .068542786  .0936002   .9968397  .6180297    .5595049  .4894108   .6850701   .5123788  .47409105  .18963243 .29178423   .4753942
610  .11216265   .7590426    .2779583   .8800637  .8825152 .02750407     .8599574 .26165095   .881642   .3974658 .58045495  .14693867 .07834926    .7797714  .6184696 .008610565  .09414338   .9472899    .978209  .3734652    .683974
611   .4809064   .9605365    .2021227   .9440667  .3109555  .4280561    .13414234 .07799873  .5611674   .9175555  .6263985   .9460785 .14040683    .8112432   .997188   .9462112   .6391721   .8063906   .8860307 .14791898    .743752
612   .9763448   .8826448    .3003853   .3617445 .25469995  .6700398     .4723527  .7793288  .5525427   .6052354  .3676016   .1181663 .25311214    .3741969  .7967921   .0815424   .3360295 .033930518   .6371259  .9184047   .4282821
613  .12549753  .32789275    .9302015     .29709  .6928065  .4825758     .4374269  .0979075 .10575833   .7637404  .4460828   .5996632  .7446989    .7410577   .823602   .4389702   .9686729    .560661   .9585252  .4125423 .021758124
614   .7655026   .3513183    .3215277  .17099117  .1083876 .25556746     .8328871  .9606185 .47012335   .7864969  .6288581  .21558407 .29047707    .8240173 .11192109  .18490495    .833865   .7526915   .7067978  .6093209   .9432667
615 .035859343   .3247132  .006623633   .8399968  .3790731  .4384699    .10340178  .8710195  .3550118   .1470323 .05571201   .9748631 .24378547    .5675536  .6989579   .2786369  .02193043  .19082133    .759917   .498381    .866712
616  .07023593  .05225784    .6270677   .3256108  .3164629 .31582925     .4531727  .6299337 .13379718   .9437305  .3306202   .9635917 .26614276   .17912534    .30644  .04501681   .5457183   .7494813   .8703864  .5829556   .8456575
617  .21017867   .7021965 .0089331865   .6692844  .8641324  .0935955    .19332026   .366159   .941715  .02082244  .1729921   .6886892 .13427253    .3172322 .09149858   .2025986   .4407154  .26410568   .2961064 .04575173   .8287497
618   .6616006   .8886811    .3961149   .2747885 .14159387 .02711774     .5459082  .1809516  .4236883  .18070504  .6544085 .017226761   .228667   .56646615   .561728  .09391225   .4370439  .18742865   .8745297  .4459683   .6819412
619  .20113812    .912768    .5508492  .42323855   .458869  .0993189     .9379712 .39116785  .7296131   .3787152  .9757255  .11731508  .7248391    .8171877  .8423647   .6634434   .3614414  .11629582   .6173607  .7026991   .9669327
620   .9874877   .7012697    .4874784  .14427297  .9604624 .11855792     .8324136   .728358  .9601552   .4170643  .9159671   .3044476  .8724514 .0003778321  .7447154   .0855845   .1469714   .6083057   .6701748  .8224061   .7628713
621   .2985383 .017440306    .7211111   .7595631  .4984015  .7919037    .03983431  .8949009  .1770748   .8849521 .14597209  .23746087 .08731584     .968271  .2766567    .636588  .29530206   .3478262  .47317085  .6471993   .7689802
622   .8969765   .7283185    .6848539   .5321585  .8726584    .06659     .3430923  .4442242  .3405329   .2106834 .24133775   .1499538  .3661061    .5275499   .954187   .7694102  .56108105   .8607229   .1549096  .4130094  .28514433
623  .08119465   .1590071    .3321862  .20126833 .04213694  .8575507     .4476398  .4358361  .9865602   .4554886  .8844633   .8042786 .08382535   .26596707  .8432269   .8973282 .003954357   .5284772  .13419913   .417355   .9456708
624    .660214  .24487182    .6343603   .9941382  .8171654  .8894401     .7155131  .3496222  .9224355   .9162734  .8159728  .32951865  .6773128    .0751499  .5201123    .723623   .9064275   .8871928   .9985808 .20447685 .026154125
625   .6283849   .3063067    .8705296   .4353405  .3534296  .5142699    .06749756  .6338428  .7734263   .8860857 .05276982   .4231223 .05277415    .5755458  .8234542   .2697828 .020137155   .1689878 .008545899  .4926267   .8326545
626 .019561933  .10421273    .4043443 .021285385  .7544023  .3910225     .1154926 .14445524  .7058836  .50991046 .04294183   .7527171 .09111737   .06338029  .9043961   .7959391  .56796324   .7240822   .3183507  .4036203  .28832573
627  .20679154  .20672686   .12353604   .7446451   .766519   .372983     .2044689 .07554335  .4117713    .673403  .5233816   .8849649  .7306813    .6293616  .0485412   .6629182   .4832753  .19211394   .9257677  .9517537   .6076019
628   .8097933   .8505134    .4398758   .0918318  .4196481  .6078497     .5137122  .4999191  .9204913  .06827217   .076191  .52734905  .5813505    .6035736  .3939106  .22398396  .15298656  .09332719   .6309783  .5268703  .10934786
end
format %tm mdate


local stubs
forvalues i = 1/7 {
    local stubs `stubs' firm@_var`i'
}

reshape long `stubs', i(mdate) j(firm)
rename firm_var* var*
sort firm mdate

If you are not able to adapt this code for use in your real data set, do post back showing an actual data set example. Use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Qingyang Xu]

Well, the tableau you show is suggestive, but it's not a possible Stata data set in that things like firm1-var1 are not possible varnames: only digits, letters, and underscore (_) characters are allowed. Since -reshape- code relies intensely on the details of the variable names, it is not really possible to confidently propose code that will work for you. It would have been far more helpful had you shown a real data example from your actual Stata data set using the -dataex- command.

That said, I will show you the general approach using a demonstration data set that looks more or less like your tableau:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mdate firm1_var1 firm1_var2 firm1_var3 firm1_var4 firm1_var5 firm1_var6 firm1_var7 firm2_var1 firm2_var2 firm2_var3 firm2_var4 firm2_var5 firm2_var6 firm2_var7 firm3_var1 firm3_var2 firm3_var3 firm3_var4 firm3_var5 firm3_var6 firm3_var7)
599 .9472316 .7800556 .587661 .4575412 .7526946 .11324778 .8159919 .3224011 .5222717 .21660325 .50994456 .30335885 .7215877 .21465635 .4405374 .4373816 .8502334 .2106113 .29453346 .4883266 .14044325
600 .05222337 .015391795 .58383894 .4056266 .627162 .4035936 .00028821192 .8752485 .8128985 .6231912 .5060895 .7084118 .6140801 .6058227 .8109628 .56283796 .7269681 .6391397 .6051359 .6101886 .9587523
601 .9743183 .574749 .6502236 .10792727 .6639 .7056643 .3804336 .9153903 .53831494 .7862822 .38687575 .9936326 .13111094 .7316875 .07246409 .29152772 .6870763 .4236866 .29465795 .7730114 .9713359
602 .9457484 .6551434 .421985 .7559011 .18422593 .5731504 .12547621 .5912329 .4184419 .004203058 .7252148 .29127693 .7384753 .14340399 .7352452 .9189357 .3651932 .7563699 .934348 .6664088 .9789942
603 .18564783 .53569984 .7049119 .9501313 .28977874 .09911993 .8984238 .3718975 .02413011 .23491123 .04936334 .9030942 .8670255 .435995 .4810134 .4308617 .306365 .3757029 .3977599 .8286883 .15681207
604 .9487334 .399902 .071344234 .1730719 .54863846 .992569 .6826897 .7271155 .8008212 .8358622 .4846969 .7233624 .68184 .6163042 .4488823 .8839191 .591621 .19234246 .7007847 .6733498 .6432714
605 .8825376 .356696 .9762383 .15291964 .58717006 .3708564 .003448891 .742907 .28695825 .2875256 .05717023 .7255588 .27689284 .7886472 .59292585 .8466811 .03972807 .483353 .8151794 .6131955 .39555255
606 .9440776 .7333733 .470753 .9149532 .24046357 .6126292 .8822002 .8368753 .5523863 .4829012 .01275296 .6869112 .11522907 .5077908 .5985539 .11466528 .6128506 .6434054 .79322 .7551122 .6708255
607 .08942585 .5795121 .9566074 .6809552 .2239969 .8929192 .5584789 .9620744 .3937416 .7304509 .9186962 .04706742 .3282427 .6408654 .21865103 .10681064 .3638305 .1675173 .51681155 .20058893 .8392906
608 .7505445 .8314545 .058898 .3250927 .5621675 .56029755 .7529196 .7559323 .53916496 .53706163 .6139446 .9708208 .1559232 .6631094 .27223426 .8354875 .858534 .3687886 .826618 .1901031 .23890825
609 .9484983 .2258395 .40200615 .5950673 .5482864 .4706297 .6922931 .7972363 .9865241 .068542786 .0936002 .9968397 .6180297 .5595049 .4894108 .6850701 .5123788 .47409105 .18963243 .29178423 .4753942
610 .11216265 .7590426 .2779583 .8800637 .8825152 .02750407 .8599574 .26165095 .881642 .3974658 .58045495 .14693867 .07834926 .7797714 .6184696 .008610565 .09414338 .9472899 .978209 .3734652 .683974
611 .4809064 .9605365 .2021227 .9440667 .3109555 .4280561 .13414234 .07799873 .5611674 .9175555 .6263985 .9460785 .14040683 .8112432 .997188 .9462112 .6391721 .8063906 .8860307 .14791898 .743752
612 .9763448 .8826448 .3003853 .3617445 .25469995 .6700398 .4723527 .7793288 .5525427 .6052354 .3676016 .1181663 .25311214 .3741969 .7967921 .0815424 .3360295 .033930518 .6371259 .9184047 .4282821
613 .12549753 .32789275 .9302015 .29709 .6928065 .4825758 .4374269 .0979075 .10575833 .7637404 .4460828 .5996632 .7446989 .7410577 .823602 .4389702 .9686729 .560661 .9585252 .4125423 .021758124
614 .7655026 .3513183 .3215277 .17099117 .1083876 .25556746 .8328871 .9606185 .47012335 .7864969 .6288581 .21558407 .29047707 .8240173 .11192109 .18490495 .833865 .7526915 .7067978 .6093209 .9432667
615 .035859343 .3247132 .006623633 .8399968 .3790731 .4384699 .10340178 .8710195 .3550118 .1470323 .05571201 .9748631 .24378547 .5675536 .6989579 .2786369 .02193043 .19082133 .759917 .498381 .866712
616 .07023593 .05225784 .6270677 .3256108 .3164629 .31582925 .4531727 .6299337 .13379718 .9437305 .3306202 .9635917 .26614276 .17912534 .30644 .04501681 .5457183 .7494813 .8703864 .5829556 .8456575
617 .21017867 .7021965 .0089331865 .6692844 .8641324 .0935955 .19332026 .366159 .941715 .02082244 .1729921 .6886892 .13427253 .3172322 .09149858 .2025986 .4407154 .26410568 .2961064 .04575173 .8287497
618 .6616006 .8886811 .3961149 .2747885 .14159387 .02711774 .5459082 .1809516 .4236883 .18070504 .6544085 .017226761 .228667 .56646615 .561728 .09391225 .4370439 .18742865 .8745297 .4459683 .6819412
619 .20113812 .912768 .5508492 .42323855 .458869 .0993189 .9379712 .39116785 .7296131 .3787152 .9757255 .11731508 .7248391 .8171877 .8423647 .6634434 .3614414 .11629582 .6173607 .7026991 .9669327
620 .9874877 .7012697 .4874784 .14427297 .9604624 .11855792 .8324136 .728358 .9601552 .4170643 .9159671 .3044476 .8724514 .0003778321 .7447154 .0855845 .1469714 .6083057 .6701748 .8224061 .7628713
621 .2985383 .017440306 .7211111 .7595631 .4984015 .7919037 .03983431 .8949009 .1770748 .8849521 .14597209 .23746087 .08731584 .968271 .2766567 .636588 .29530206 .3478262 .47317085 .6471993 .7689802
622 .8969765 .7283185 .6848539 .5321585 .8726584 .06659 .3430923 .4442242 .3405329 .2106834 .24133775 .1499538 .3661061 .5275499 .954187 .7694102 .56108105 .8607229 .1549096 .4130094 .28514433
623 .08119465 .1590071 .3321862 .20126833 .04213694 .8575507 .4476398 .4358361 .9865602 .4554886 .8844633 .8042786 .08382535 .26596707 .8432269 .8973282 .003954357 .5284772 .13419913 .417355 .9456708
624 .660214 .24487182 .6343603 .9941382 .8171654 .8894401 .7155131 .3496222 .9224355 .9162734 .8159728 .32951865 .6773128 .0751499 .5201123 .723623 .9064275 .8871928 .9985808 .20447685 .026154125
625 .6283849 .3063067 .8705296 .4353405 .3534296 .5142699 .06749756 .6338428 .7734263 .8860857 .05276982 .4231223 .05277415 .5755458 .8234542 .2697828 .020137155 .1689878 .008545899 .4926267 .8326545
626 .019561933 .10421273 .4043443 .021285385 .7544023 .3910225 .1154926 .14445524 .7058836 .50991046 .04294183 .7527171 .09111737 .06338029 .9043961 .7959391 .56796324 .7240822 .3183507 .4036203 .28832573
627 .20679154 .20672686 .12353604 .7446451 .766519 .372983 .2044689 .07554335 .4117713 .673403 .5233816 .8849649 .7306813 .6293616 .0485412 .6629182 .4832753 .19211394 .9257677 .9517537 .6076019
628 .8097933 .8505134 .4398758 .0918318 .4196481 .6078497 .5137122 .4999191 .9204913 .06827217 .076191 .52734905 .5813505 .6035736 .3939106 .22398396 .15298656 .09332719 .6309783 .5268703 .10934786
end
format %tm mdate


local stubs
forvalues i = 1/7 {
local stubs `stubs' firm@_var`i'
}

reshape long `stubs', i(mdate) j(firm)
rename firm_var* var*
sort firm mdate

If you are not able to adapt this code for use in your real data set, do post back showing an actual data set example. Use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

Thank you Clyde, It didnt work at first time but seems feasible, consdiering the data is too confusing so I recollected from somewhere else. But still appreciate for your help, I will try it later.