Restructuring data from ORBIS

[Anja Jean-Mairet]

Hi,

I am working with firm level data from ORBIS. I have downloaded multiple variables for the years 2023-2000, however the format of the data is not yet in panel format. I am trying to visualize what the data currently looks like. Each variable's annual data is listed in columns per firm. What I need is a separate row for each year which shows me the respective observation for the variables in the respective year.

I know that if I would only have one variable and I would have a separate column that repeats the variable name the code could look something like this:

egen panelid=group(VariableName BvDID)

reshape long YR, i(panelid) j(year)

But do I really have to first create a dataset for each single variables and in the end merge them all or is there an easier option to do it all at once and in the same dataset?


What I currently have:

BvDID	CompanyName	sales_2023	Sales_2022	Sales_20XX	Employees_2023	Employees_20XX
NLxxx	Example1	XX	XX	XX	XX	XX
GBxxx	Example2	XX	XX	XX	XX	XX

What I need it to look like:

BvDID	CompanyName	Year	Sales	Employees	Variable3	Variable4
NLxxx	Example1	2023	XX	XX	XX	XX
NLxxx	Example1	2022	XX	XX	XX	XX
NLxxx	Example1	2021	XX	XX	XX	XX
GBxxx	Example2	2023	XX	XX	XX	XX
GBxxx	Example2	2022	XX	XX	XX	XX
GBxxx	Example2	2021	XX	XX	XX	XX

Thank you for your help!

Best,
Anja

[George Ford]

you can use multiple prefix in reshape.

reshape long Sales_ Employees_ ....,

if the data really looks like that, you should lowercase all the variable names (you have sales_2023 and Sales_2022). Or, make sure they are all the same. If you're importing from csv/xlsx, you can do it then with lowercase option.

[Anja Jean-Mairet]

Thank you George!

After some data cleaning that worked wonderfully!

[George Ford]

excellent