How to write in wide format

[priya sawaliya]

Hello,
I would like to convert my data in wide format. my data look like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 stock byte stock_id double beta int date
"3M IN Equity" 1         . 16618
"3M IN Equity" 1         . 16649
"3M IN Equity" 1         . 16680
"3M IN Equity" 1         . 16710
"3M IN Equity" 1         . 16741
"3M IN Equity" 1         . 16771
"3M IN Equity" 1         . 16802
"3M IN Equity" 1         . 16833
"3M IN Equity" 1         . 16861
"3M IN Equity" 1 1.0242735 16892
"3M IN Equity" 1 .93165562 16922
"3M IN Equity" 1 1.0052914 16953
"3M IN Equity" 1 .91398876 16983
"3M IN Equity" 1 .92771798 17014
"3M IN Equity" 1 .92501127 17045
"3M IN Equity" 1 .89771264 17075
"3M IN Equity" 1 .89292392 17106
"3M IN Equity" 1 .84455079 17136
"3M IN Equity" 1 .81149471 17167
"3M IN Equity" 1 .78862009 17198
"3M IN Equity" 1 .79077822 17226
"3M IN Equity" 1 .76711434 17257
"3M IN Equity" 1 .75772362 17287
"3M IN Equity" 1 .51870083 17318
"3M IN Equity" 1 .57872802 17348
"3M IN Equity" 1 .55797771 17379
"3M IN Equity" 1 .50700669 17410
"3M IN Equity" 1 .62044345 17440
"3M IN Equity" 1 .63649182 17471
"3M IN Equity" 1 .68824955 17501
"3M IN Equity" 1 .65520035 17532
"3M IN Equity" 1 .63642547 17563
"3M IN Equity" 1 .58995894 17592
"3M IN Equity" 1 .56368013 17623
"3M IN Equity" 1  .5420721 17653
"3M IN Equity" 1 .51879322 17684
"3M IN Equity" 1 .51861309 17714
"3M IN Equity" 1 .50388487 17745
"3M IN Equity" 1 .50886022 17776
"3M IN Equity" 1 .41270763 17806
"3M IN Equity" 1 .38656573 17837
"3M IN Equity" 1 .35143678 17867
"3M IN Equity" 1 .32456268 17898
"3M IN Equity" 1 .31262002 17929
"3M IN Equity" 1 .31467122 17957
"3M IN Equity" 1 .37305928 17988
"3M IN Equity" 1 .36607098 18018
"3M IN Equity" 1 .37236193 18049
"3M IN Equity" 1 .35365586 18079
"3M IN Equity" 1  .3635787 18110
"3M IN Equity" 1 .35108253 18141
"3M IN Equity" 1 .35221714 18171
"3M IN Equity" 1 .37890297 18202
"3M IN Equity" 1 .42270491 18232
"3M IN Equity" 1 .45244584 18263
"3M IN Equity" 1 .44378156 18294
"3M IN Equity" 1 .43082303 18322
"3M IN Equity" 1  .3448735 18353
"3M IN Equity" 1 .41370085 18383
"3M IN Equity" 1 .37983125 18414
"3M IN Equity" 1 .39776613 18444
"3M IN Equity" 1 .39218399 18475
"3M IN Equity" 1 .41063585 18506
"3M IN Equity" 1 .36740134 18536
"3M IN Equity" 1 .41702158 18567
"3M IN Equity" 1 .45854719 18597
"3M IN Equity" 1 .42393788 18628
"3M IN Equity" 1 .50314955 18659
"3M IN Equity" 1 .46508122 18687
"3M IN Equity" 1 .43439217 18718
"3M IN Equity" 1 .45586783 18748
"3M IN Equity" 1 .49806431 18779
"3M IN Equity" 1 .54572956 18809
"3M IN Equity" 1   .555546 18840
"3M IN Equity" 1 .51171769 18871
"3M IN Equity" 1 .49679597 18901
"3M IN Equity" 1 .49561475 18932
"3M IN Equity" 1 .45676664 18962
"3M IN Equity" 1 .38704123 18993
"3M IN Equity" 1 .29830623 19024
"3M IN Equity" 1 .26004881 19053
"3M IN Equity" 1 .29890214 19084
"3M IN Equity" 1 .29205719 19114
"3M IN Equity" 1 .31123305 19145
"3M IN Equity" 1 .29035064 19175
"3M IN Equity" 1 .25681098 19206
"3M IN Equity" 1 .23257046 19237
"3M IN Equity" 1 .26389838 19267
"3M IN Equity" 1 .23093442 19298
"3M IN Equity" 1 .19880251 19328
"3M IN Equity" 1 .33880127 19359
"3M IN Equity" 1  .4548112 19390
"3M IN Equity" 1 .54708197 19418
"3M IN Equity" 1  .5014456 19449
"3M IN Equity" 1 .52091825 19479
"3M IN Equity" 1 .42890336 19510
"3M IN Equity" 1 .34185527 19540
"3M IN Equity" 1 .29621827 19571
"3M IN Equity" 1 .35535035 19602
"3M IN Equity" 1 .34478139 19632
end
format %td date

Please help.
Thank you

[William Lisowski]

When you reshape your data to a wide layout, so that you have a different beta variable for each date, what do you want the new names to be? Your dates are 01jul2005, 01aug2005, ... .

[priya sawaliya]

Dear sir,
you may give any name. I want my beta for each stock for each date.

[William Lisowski]

generate dt = strofreal(mofd(date),"%tm!_CCYY!_NN")
drop date
reshape wide beta, i(stock stock_id) j(dt) string
ds

. ds
stock         beta_2006_10  beta_2008_03  beta_2009_08  beta_2011_01  beta_2012_06
stock_id      beta_2006_11  beta_2008_04  beta_2009_09  beta_2011_02  beta_2012_07
beta_2005_07  beta_2006_12  beta_2008_05  beta_2009_10  beta_2011_03  beta_2012_08
beta_2005_08  beta_2007_01  beta_2008_06  beta_2009_11  beta_2011_04  beta_2012_09
beta_2005_09  beta_2007_02  beta_2008_07  beta_2009_12  beta_2011_05  beta_2012_10
beta_2005_10  beta_2007_03  beta_2008_08  beta_2010_01  beta_2011_06  beta_2012_11
beta_2005_11  beta_2007_04  beta_2008_09  beta_2010_02  beta_2011_07  beta_2012_12
beta_2005_12  beta_2007_05  beta_2008_10  beta_2010_03  beta_2011_08  beta_2013_01
beta_2006_01  beta_2007_06  beta_2008_11  beta_2010_04  beta_2011_09  beta_2013_02
beta_2006_02  beta_2007_07  beta_2008_12  beta_2010_05  beta_2011_10  beta_2013_03
beta_2006_03  beta_2007_08  beta_2009_01  beta_2010_06  beta_2011_11  beta_2013_04
beta_2006_04  beta_2007_09  beta_2009_02  beta_2010_07  beta_2011_12  beta_2013_05
beta_2006_05  beta_2007_10  beta_2009_03  beta_2010_08  beta_2012_01  beta_2013_06
beta_2006_06  beta_2007_11  beta_2009_04  beta_2010_09  beta_2012_02  beta_2013_07
beta_2006_07  beta_2007_12  beta_2009_05  beta_2010_10  beta_2012_03  beta_2013_08
beta_2006_08  beta_2008_01  beta_2009_06  beta_2010_11  beta_2012_04  beta_2013_09
beta_2006_09  beta_2008_02  beta_2009_07  beta_2010_12  beta_2012_05  beta_2013_10

[priya sawaliya]

Sir, It does not work. when I run reshape command the following message is showing
"values of variable dt not unique within stock stock_id.
Your data are currently long. You are performing a reshape wide. You specified i(stock stock_id) and j(dt). There are observations within i(stock stock_id) with the same value of j(dt). In the long data, variables i() and j() together must uniquely
identify the observations".

[William Lisowski]

In your example data your single stock has a single observation date in each month.

It appears that in your full dataset at least one stock has more than one observation in at least one month.

Either your full dataset has some duplicated observations, or your full dataset has some stocks with multiple observation dates in a single month.

You need to either correct the problem of duplicates using the commands described in

help duplicates

or you need to more fully describe your data.

If your data are not monthly - if for example some stocks have weekly data - then converting the data to a wide format will likely be unhelpful, because each stock will have missing values on all the days for which no data is reported.

You haven't told us why you want the data in wide format. The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data. You should try to achieve what you need with the data organized as it currently is, and seek the help of Statalist in doing so. The sort of problems you will encounter trying to use your reshaped data will almost certainly be solved by reshaping the data. It is much easier, for example, to compare the second observation to the first, the third to the second, and so on, than it is to compare the second variable to the first, the third to the second, etc.