Reshape dataset including variables names as an additional variable

[Marco Sordi]

Hello Statalist community,

I don't have yet much experience using Stata and hope you can help me with a problem I am encountering.

I have a dataset with a time variable (years) and industry variables in which the observations represent the respective industry's beta for each year.
I need to reshape the dataset so that I can have all the betas under the same variable (Ind_Beta) and create an additional variable that identifies the industry, repeating of course the years.

Please find below a simplified example of the current dataset and the desired structure, followed by the dataset example generated by stata dataex.

Any help or hint in the right direction would be greatly appreciated.

Thanks a lot in advance!


Current Dataset Structure:

year	Agric	Food	Drug	Books
2000	1.12	1.45	0.97	…
2001	1.05	1.6	0.88	…
2002	1.18	1.34	0.92	…

Desired Structure:

year	Industry	Ind_Beta
2000	Agric	1.12
2000	Food	1.45
2000	Drug	0.97
2000	…	…
2001	Agric	1.05
2001	Food	1.6
2001	Drug	0.88
2001	…	…
2002	Agric	1.18
2002	Food	1.34
2002	Drug	0.92
2002	…	…

Dataset example generated by stata dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year float(agric food beer smoke toys fun books)
2000  .3452645 .13540165 .004693561 .10387945  .5594783  .7765604   .672514
2001  .4666844 .19018325  .22386266 .13462715  .7232649 1.2980112  .6057138
2002  .4180116  .4688401   .4606416  .3165837  .8264946 1.0237317  .7255396
2003  .6058208  .5994597  .54949224 .58115196  .8094561  1.174974  .8105838
2004   .645611   .618094   .5450272  .6523659 1.0529572 1.0226463  .6682123
2005  .7191871  .6432664   .6079998  .8309416 1.2295903 1.1020511  .7842661
2006  .7900746  .5786355   .4802569  .4810273  .9801628  .9165996  .7079894
2007 1.3577225  .7155325   .5886528   .634958  .7687722 1.0139668  .8080173
2008 1.1235923  .6186133   .5526155  .6556639  .8520126  1.213102  1.132914
2009  .6970165 .52631867   .4582001  .3303039 1.0007415 1.5126996  1.307686
2010  .7568682  .5700232   .5300202  .6517677 1.0587844 1.4952796 1.1704426
2011 1.0809941  .6031252   .5268776  .5393996   .980441 1.2700168 1.1069671
2012 1.0136213   .572837  .52827054  .5611398 1.0247171 1.2806007  .9967803
2013  .9104237  .8755165   .7196253  .7702268  .9460964  1.228237  1.176336
2014  .7878072  .7916082   .5841157  .6283697 1.0399202  1.299612 1.1335369
2015  .9148453  .7809891   .7545701  .7404549 1.0749298  1.199249 1.0286957
end

[William Lisowski]

Perhaps this is what you want.

. rename (agric-books) (Ind_Beta=)

. reshape long Ind_Beta, i(year) j(Industry) string
(j = agric beer books food fun smoke toys)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations               16   ->   112        
Number of variables                   8   ->   3          
j variable (7 values)                     ->   Industry
xij variables:
Ind_Betaagric Ind_Betabeer ... Ind_Betatoys->  Ind_Beta
-----------------------------------------------------------------------------

. list if year==2000, clean

       year   Industry   Ind_Beta  
  1.   2000      agric   .3452645  
  2.   2000       beer   .0046936  
  3.   2000      books    .672514  
  4.   2000       food   .1354017  
  5.   2000        fun   .7765604  
  6.   2000      smoke   .1038795  
  7.   2000       toys   .5594783  

.

Added in edit: see the output of

help rename group

to understand the rename command.

[Marco Sordi]

Hi William,

Thank you very much for your help! This is exactly what I need!