-2

I'm working with this type of data set in Stata:

Year Country Investment Value
2000 US Bonds Total 8%
2000 US Bonds Private 50%
2000 US Equity Total 10%
2000 US Bonds Public 50%
2000 US Equity listed 30%
2000 US Equity Unlisted 70%
2000 FR Bonds Total 5%
2000 FR Bonds Private 40%
2000 FR Bonds Public 60%
2001 US Bonds Private 70%

My issue is that "Bonds Private" and "Bonds Public" are subcategories of "Bonds Total". For my analysis, I would need to have those data in the same line as their respective category.

Thus, I'm trying to achieve this:

Year Country Bonds Total Bonds Private Bonds Public Equity Total Equity Listed Equity Unlisted Real Estate etc..
2000 US 8% 50% 50% 10% 30% 70% 5% ...
2000 FR 5% 60% 40% 12% 10% 90% 8% ...
2000 DE 6% 40% 60% 15% 10% 90% 10% ...
... ... ... ... ... ... ... ... ... ...
2019 CA 5% 60% 40% 10% 30% 70% 10% ...

I already tried to achieve this by using the function reshape but I could not achieve the same result. Here is what I have tried :

by Year Country, sort: gen newid = _n    
reshape wide investment, i(year) j(newid)

I'm receiving the following error message:

values of variable newid not unique within year
Loni
  • 1
  • 1
  • 1
    Please study the Stata tag wiki and present a data example as code using `dataex`. Alternatively look directly at `help dataex` We can't comment on code you don't show (although I suspect you don't need any loops here any way). – Nick Cox Sep 13 '21 at 08:50
  • Check out the 'reshape' command in Stata. You are trying to transform 'long' data to 'wide' data. – cremorna Sep 13 '21 at 12:59
  • Hi all, thank you both for your comments! Indeed, it looks like the answer is "reshape" however I was not able to achieve the result I wanted (see updated question). – Loni Sep 14 '21 at 08:51
  • The error message is informative. Your observations are identified by country and year. Your code implies that you have `year` and `Year` so watch outl – Nick Cox Sep 15 '21 at 09:39

1 Answers1

0

Without seeing a reproducible example I'll try to guess:

reshape wide value, i(year country) j(investment) string

Use the string option in order to allow for string observations ("Bonds total" etc..) to be used as variable names.

Rename the variables accordingly afterwards.

krasnapolsky
  • 347
  • 1
  • 16