Shifting data from various columns to create one variable

[Ash Zam]

Suppose I have the following:

Year	ID	Period	10th Percentile	20th Percentile	30th Percentile
2019	1	1	100	200	300
2020	1	2	400	500	600

I want to create a variable that takes on the values of the row and make it into a column without reshaping the whole data. So, something that looks like this:

Year	Percentile	Variable of Interest
2019	10	100
2019	20	200
2019	30	300
2020	10	400
2020	20	500
2020	30	600

So while I want this, I don't want to reshape the whole dataset and would prefer to just create new variables to do this (if possible), as we have millions of observations and there is a lot of going back and forth between what I want to do with these variables and what I have to do with the existing format and it would take a very long time to have to continuously switch back and forth. I hope this makes sense- thanks!

[Clyde Schechter]

So while I want this, I don't want to reshape the whole dataset and would prefer to just create new variables to do this (if possible),

I don't think this is possible. Each observation in the original data must be converted to 3 observations in the new data, so there is no way to maintain the alignment of the year variable with the values of other variables corresponding to it without reshaping.

there is a lot of going back and forth between what I want to do with these variables and what I have to do with the existing format

So perhaps the resolution to this problem is to have two frames, one for the original data, and one for the -reshape-d percentiles of the variable of interest. You can link them on ID and year for situations where you need them all together, but can work with them separately for things where you don't. And switching between frames is very fast.

If you like this approach but are unsure how to implement it, please show example data, using the -dataex- command, when you post back. 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.