Transposing only part of columns into rows

[Alex Fleiss]

Hi everybody,

I currently have the problem that the source where I get my data, formats the data in a weird way.

The following is a simplified version of what I get:

ID	Country	Incorporation	Revenue_2018	Revenue_2017	Revenue_2016	Assets_2018	Assets_2017	Assets_2016
A	ES	2012	980	900	800	2000	1000	500
B	DE	2011	500	450	300	1000	900	800

But what I need is this:

ID	Year	Country	Incorporation	Revenue	Assets
A	2016	ES	2012	800	500
A	2017	ES	2012	900	100
A	2018	ES	2012	980	2000
B	2016	DE	2011	300	800
B	2017	DE	2011	450	900
B	2018	DE	2011	500	1000

I'm thinking of using a loop to do this for each company (6 million) and each year (10 years) and each year dependent variable (60 variables) seperately.

I just started with Stata and don't know how to build such a loop, or if there's an easier way to do this.

I hope you understand what I mean and can help me out.

Best regards
Alex

[Wouter Wakker]

No loop necessary, reshape will do what you want. Based on your data example it would be something like this.

reshape long Revenue_ Assets_, i(ID) j(year)

[Alex Fleiss]

A small command for Stata, a giant leap towards finishing my analysis.

Thank you so much. This totally gets the job done.