Reshaping data with 12 time series

[Bob Rotman]

Hi,

When I tried the following command:
reshape long Jan2003 Feb2003 Mar2003 Apr2003 May2003 Jun2003 Jul2003 Aug2003 Sep2003 Oct2003 Nov2003 Dec2003 Jan2004 Feb2004 Mar2004 Apr2004 May2004 Jun2004 Jul2004 Aug2004 Sep2004 Oct2004 Nov2004 Dec2004 Jan2005 Feb2005 Mar2005 Apr2005 May2005 Jun2005 Jul2005 Aug2005 Sep2005 Oct2005 Nov2005 Dec2005 Jan2006 Feb2006 Mar2006 Apr2006 May2006 Jun2006 Jul2006 Aug2006 Sep2006 Oct2006 Nov2006 Dec2006 Jan2007 Feb2007 Mar2007 Apr2007 May2007 Jun2007 Jul2007 Aug2007 Sep2007 Oct2007 Nov2007 Dec2007 Jan2008 Feb2008 Mar2008 Apr2008 May2008 Jun2008 Jul2008 Aug2008 Sep2008 Oct2008 Nov2008 Dec2008 Jan2009 Feb2009 Mar2009 Apr2009 May2009 Jun2009 Jul2009 Aug2009 Sep2009 Oct2009 Nov2009 Dec2009 Jan2010 Feb2010 Mar2010 Apr2010 May2010 Jun2010 Jul2010 Aug2010 Sep2010 Oct2010 Nov2010 Dec2010 Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 Jan2012 Feb2012 Mar2012 Apr2012 May2012 Jun2012 Jul2012 Aug2012 Sep2012 Oct2012 Nov2012 Dec2012 Jan2013 Feb2013 Mar2013 Apr2013 May2013 Jun2013 Jul2013 Aug2013 Sep2013 Oct2013 Nov2013 Dec2013 Jan2014 Feb2014 Mar2014 Apr2014 May2014 Jun2014 Jul2014 Aug2014 Sep2014 Oct2014 Nov2014 Dec2014 Jan2015 Feb2015 Mar2015 Apr2015 May2015 Jun2015 Jul2015 Aug2015 Sep2015 Oct2015 Nov2015 Dec2015 Jan2016 Feb2016 Mar2016 Apr2016 May2016 Jun2016 Jul2016 Aug2016 Sep2016 Oct2016 Nov2016 Dec2016 Jan2017 Feb2017 Mar2017 Apr2017 May2017 Jun2017 Jul2017 Aug2017 Sep2017 Oct2017 Nov2017 Dec2017,
i(CompanyID VariableID) j(Date)

Stata says it doesn't recognize a# and b#. Only if I ask Stata to:
reshape long Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec, i(CompanyID VariableID) j(Year)

Giving me this output:

Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 10550 -> 158250
Number of variables 198 -> 31
j variable (15 values) -> Year
xij variables:
Jan2003 Jan2004 ... Jan2017 -> Jan
Feb2003 Feb2004 ... Feb2017 -> Feb
Mar2003 Mar2004 ... Mar2017 -> Mar
Apr2003 Apr2004 ... Apr2017 -> Apr
May2003 May2004 ... May2017 -> May
Jun2003 Jun2004 ... Jun2017 -> Jun
Jul2003 Jul2004 ... Jul2017 -> Jul
Aug2003 Aug2004 ... Aug2017 -> Aug
Sep2003 Sep2004 ... Sep2017 -> Sep
Oct2003 Oct2004 ... Oct2017 -> Oct
Nov2003 Nov2004 ... Nov2017 -> Nov
Dec2003 Dec2004 ... Dec2017 -> Dec
-----------------------------------------------------------------------------


This, however, is not what i want since I need panel data for stata to work.
Could you please help me?

Kind regards,

Bob

[Bob Rotman]

I forgot to mention that my 12 variables are already in rows for all the 886 companies in my sample (but you could probably see that since I have 10550 observations).

[Clyde Schechter]

You don't state in clear terms exactly what you want, but I'm guessing you mean this:

rename (Jan2003-Dec2017) v=
reshape long v, i(CompanyID VaribleID) j(my) string
gen Date = monthly(my, "MY")
format Date %tm

The code assumes that the variables Jan2003-Dec2017 are located as a contiguous group of variables in the data, with no extraneous variables in their midst. If that's not the case, consider re-ordering the variables, or contrive some other simple way to refer to them as a single group in the -rename-command. (Or, worst case scenario, list them all out in the -rename- command if there is no way to refer to them more parsimoniously.)

Added: The code also assumes you have no other variables whose names begin with v. If that's not true, pick some prefix that is short, legal as part of a variable name, and does not occur at the beginning of any of your variables, and replace v by that prefix throughout the code.

The variable v that results at the end is a single variable containing the values of whatever it was that the original Jan2003-Dec2017 variables contained. You will probably want to choose a more mnemonic name for it once you are done.

In the future, when asking for help with code, it is best to also show example data. The above code is not tested, because you did not do that. Consequently, it may contain errors.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it 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-.

Added: If this code does not give you what you want, when you post back, do a hand-worked example of what you want the results to look like and show that so we can all get a better understanding of where you are going with this.

[Bob Rotman]

Thank you very much and next time I will show you example data.