reshaping data

[Tina Samuel]

Hello there,
I am encountering a problem reshaping my data and I would greatly appreciate your help.

I have the following dataset:
ObjectId c_id Country s_id Variable yr2013 yr2014 yr2015 yr2016 yr2017 yr2018 yr2019 yr2020 yr2021 yr2022
1 1 Afghanistan, Islamic Rep. of 1 Climate-Driven Hazard & Exposure 6.3 6.3 6.3 6.3 6.3 6.3 6.3 6.3 6.5 6.5
189 48 Dominican Rep. 2 Climate-Driven Inform Risk Indicator 5.1 5.1 5.1 5.1 5.1 5.1 5.1 5.1 5.1 5.1
409 103 Mali 3 Index for Risk Management; Lack of Coping Capacity 5.4 5.4 5.4 5.4 5.4 5.4 5.4 5.4 5.5 5.5
718 182 Uruguay 4 Index for Risk Management; Vulnerability 2.1 2.2 2.1 2.2 2.3 2.3 1.9 2.4 2.4
Where s_id is the code for each variable

I would like to reshape this data to look like this:
ObjectId c_id Country year 1 2 3 4
1 1 Afghanistan, Islamic Rep. of 2013 6.3
1 1 Afghanistan, Islamic Rep. of 2014 6.3
1 1 Afghanistan, Islamic Rep. of 2015 6.3
1 1 Afghanistan, Islamic Rep. of 2016 6.3
1 1 Afghanistan, Islamic Rep. of 2017 6.3
1 1 Afghanistan, Islamic Rep. of 2018 6.3
1 1 Afghanistan, Islamic Rep. of 2019 6.3
1 1 Afghanistan, Islamic Rep. of 2020 6.3
1 1 Afghanistan, Islamic Rep. of 2021 6.5
1 1 Afghanistan, Islamic Rep. of 2022 6.5

& so on, do you know how I can do so ?

Thank you in advance!

[Clyde Schechter]

What you are asking for is literally impossible in Stata. You cannot have a variable named 1, or 2, etc. Variable names have restrictions: They cannot exceed 32 characters in length, all characters must be letters or digits or underscore (_), and the first character cannot be a digit.

The closest I think you can get to it is this:

gen varnum = substr(s_id, 1, strpos(s_id, " ")-1)
destring varnum, replace
drop s_id

reshape long yr, i(ObjectId c_id Country) j(year)
rename yr v
reshape wide v, i(ObjectID c_id Country year) j(varnum)

Note: This code is untested because the data tableau presented as an example was not suitable for importation in Stata without extensive surgery. In the future when asking for help with code, always use the -dataex- command to show example data. If you are running version 18, 17, 16 or a fully updated version 15.1 or 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- to 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.