Need help organizing data, rows to columns

[Yifan Lu]

Hi all,

I need some help organizing my data please. This is kinda of what I currently have:

year	var1	var2	var3
2020	1	1	1
2020	2	2	2
2022	3	3	3
2022	4	4	4

I want to organize them into something like this, since I only have two years, I want to create two columns for each var that captures the years variables, so that later on I can create other variables to calculate things such as %change, etc.

var1_2020	var1_2022	var2_2020	var2_2022	var3_2020	var3_2022
1	3	1	3	1	3
2	4	2	4	2	4

Much appreciated!!!

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input int year byte(var1 var2 var3)
2020 1 1 1
2020 2 2 2
2022 3 3 3
2022 4 4 4
end

gen `c(obs_t)' obs_no = _n
by year (obs_no), sort: gen series = _n
drop obs_no
rename (var1 var2 var3) =_
reshape wide var1_ var2_ var3_, i(series) j(year)

That said, you probably should not do this at all. There are very few things in Stata that are better done with this wide layout than with the original long version, and "create other variables to calculate things such as %change" is not among them. When you have long layout data like this, it is best to leave it organized in this way unless you have some very specific and clear purpose that really requires rearranging it to wide.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 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.