Reshape from wide to long

[Linh Nguyen]

Hi everyone,


I get data from Datastream and there are some firms having no data, i.e. price_14 "$$ER: E100, INVALID CODE OR EXPRESSION ENTERED" in the following example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 tdate double(price_10 price_11 price_12 price_13) str45 price_14 double(price_15 price_16)
"1998"        . 30.0625 45.3071 29.6406 "$$ER: E100,INVALID CODE OR EXPRESSION ENTERED"  5.3828 36.5625
"1999"        . 25.0938 54.3623 41.1562 ""                                               6.6406 40.2813
"2000"        . 22.9375 38.9188 30.0625 ""                                              15.3437 43.4688
"2001"        .  31.475 39.2624   31.45 ""                                              17.6925    39.3
"2002"        .  34.785   42.97   15.57 ""                                               20.875   34.94
"2003"        .  40.215   49.94   32.05 ""                                                29.09      41
"2004"        .   46.99   55.08   23.39 ""                                               44.015   51.26
"2005"        .   46.15   57.88   24.96 ""                                                62.14   56.17
"2006"    9.849   53.39   64.27   20.25 ""                                                53.73   76.63
"2007"    21.52   41.26   73.42   26.66 ""                                                 58.2   93.69
"2008"   14.293   14.08   61.82   14.66 ""                                                 26.6   79.83
"2009"   25.598   15.06   60.63    20.4 ""                                                30.48   68.19
"2010"   22.411   13.34   64.33   21.03 ""                                                36.11   73.12
"2011"   37.282    5.56   66.71   24.25 ""                                                50.68   84.76
"2012"   49.128   11.61   67.89   20.62 ""                                                54.24   86.55
"2013"   83.546   15.57   81.41  25.955 ""                                                 75.3   101.2
"2014"    86.16   17.89   91.09   36.29 ""                                               101.09   92.45
"2015"    97.36   16.83   79.41   34.45 ""                                               117.64   77.95
"2016"   103.25    22.1   84.08   36.27 ""                                               160.04   90.26
"2017"   151.36   29.52   91.88   46.16 ""                                               220.46   83.64
"2018"   188.65   24.64   91.92   46.93 ""                                               249.12   68.19
"2019" 298.5901   35.22   124.9   59.85 ""                                               293.98   69.78
end

Then I run the code:

rename Code tdate
reshape long price_ , i(tdate) j(cmpn)

There is a report:

variable price_14 type mismatch with other price_ variables

Anyone here can help me to reshape my data which still keep price_14?

I would really appreciate all the help I can get

[Junran Cao]

This lets you keep price_14, but I am curious as to why the number of decimals 'exploded'. If anyone can advise?

encode price_14, gen(price_14a)
drop price_14
order price_14a, after(price_13)
rename price_14a price_14
reshape long price_, i(tdate) j(cmpn)
label values price_ price_14a

[Linh Nguyen]

Thanks Junran Cao