Reshape multiple years observations from wide to long

[Luigi Rombi]

Dear all,
my current data structure is as follows:

DATA	CompanyID	2011	2012	2013	…	2019
VarOfInterest	i1	stub1	stub2	stub3	…	stubn
AnnualReportDate	i1	j1	j2	j3	…	jn
VarOfInterest	i2	stub1	stub2	stub3	…	stubn
AnnualReportDate	i2	j1	j2	j3	…	jn

Essentially, I have two Data: VarOfInterest and AnnualReportDate (both varying over the years for each company) for several companies (identified with CompanyID that is unique for each company and constant over the years)

I aim to reshape my dataset into a different structure, such as this one.

CompanyID	AnnualReportDate	VarOfInterest
i1	j1	stub1
i1	j2	stub2
i1	j3	stub3
…	…	…
i1	jn	stubn
i2	j1	stub1
i2	j2	stub2
i2	j3	stub3
…	…	…
i2	jn	stubn

Thanks for your time

[William Lisowski]

Your question really isn't clear without more detail, or at a minimum it is too difficult to guess at a good answer from what you have shared. Please help us help you. Show example data that you have read into Stata, not the made-up data that you have posted. The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output.

Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a helpful response, you need to show some example data.

Be sure to use the dataex command to do this. If you are running version 15.1 or later, or a fully updated version 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 and 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.

Now, the default for dataex is to show all variables and 100 observations. But we don't need that much. Along with the other variables, only the first three of the yearly variables (your columns 2011, 2012, and 2013) are needed. And about 20 observations should be enough. Note that in particular you show columns headed with year numbers; your Stata variables will have to have different names because Stata variables cannot start with a digit. So something like

dataex DATA CompanyID y2011 y2012 y2013 in 1/20

should give example data useful enough to address your question.

[Luigi Rombi]

Hi William,
Thank you for your time and advice. I'll keep that in mind in my next posts.

Anyway, I found a solution to my issue since Baum & Cox perfectly described it in Stata tip 45: Getting those data into shape. Stata Journal 7: 268–271, retrieved in the references of the Stata Manual for the reshape command.

Best,
LR