Add only selected data to panel data set - merge or append?

[Ronald Biefinger]

Hello

I have a panel data set in long form - several groups (companies in different countries) and each year is its own observation). Now I downloaded additional data (in Stata format) which I would like to add selectively. The additional dataset (wide format) contains a key metric which is different for every country and every year. I would like to add this key metric to my existing dataset, however, I am missing the right commands, getting stuck the whole time.

So for example I have 10 companies from 5 different countries and 10 observations (years) each. Now I would like to add the key metric information contained in the additional dataset to each company - taking into account the correct country and year. How would you go about this? Thanks!!

[berlino]

Had the same problem a couple of weeks ago. In the end I did all by hand (did not know this forum existed) ... still be interested to see what the solution would be

[Clyde Schechter]

The difficulty arises because you have one data set in long format and the other in wide. You need to -reshape- the wide data set to long first, and then -merge- will bring you home. You don't provide an example of your data. But let's suppose that your first data set, data1.dta, has variables country, company, year, and some other variables (your metrics) in long format. Suppose your second data set, data2.dta, has variables country, company, key_metric_2000, key_metric_2001, key_metric_2002, etc. And I assume that the year variable in data1.dta has 2000, 2001, 2002, etc. among its values. Finally, the code below assumes that each combination of country, company, and year occurs at most once in data1.dta, and each combination of country and company at most once in data2.

use data2, clear
reshape long key_metric_, i(country company) j(year)
rename key_metric_ key_metric
tempfile holding
save `holding'

use data1, clear
merge 1:1 country company year using `holding'

[Ronald Biefinger]

Thank you very much for your reply. Below is the additional data that I want to add. The code you posted was not working, becasue I did not know how to modify it correctly... maybe you have a suggestion?

[Nick Cox]

Clyde has however pointed you to reshape and merge, so start with help reshape.

[Ronald Biefinger]

That was my original data. Either it says "no xij variables found" or "variable company not found" because I do not know what my company varibale should be. I will check the help reshalpe ...

[Andrew Musau]

Hi Ronald, you should check your variable labels to amend Clyde's code: From the screenshot that you display, the correct code is:

reshape long yr, i(country) j(year)

What you should get after reshaping are 3 columns: country - country_1 to country_n, year - 2001 to 2011, and yr - the observations.

Merging should be straight forward.

[Ronald Biefinger]

Great, thanks, it's working!!