Stack does not work, need to transform column variables into row variables

[Khanh Bui]

Hi, I am very new to Stata. I have this problem where I want to transform a table that looks something like this

Year	Month	area1	area2	area3
2000	1	100	200	300
2000	2	400	500	600
2000	3	700	800	900

into something like this

Year	Month	region	value
2000	1	area1	100
2000	1	area2	200
2000	1	area3	300

and so on...

I've tried stack but that does not seem to work, any advice or suggestions on what I should try is very much appreciated. Thank you!

[Dung Le]

Khanh Bui,

You need to reshape your data from a wide to a long format. If you show an example of your data, someone may be able to help you out.

[Clyde Schechter]

If your data set looks exactly like you show in the upper table of #1, it is a one-liner:

reshape long area, i(year month) j(region)

However, if the variables are not named are1 area2 and area3 but have names like northeast central and southern, then this code will be insufficient. That is why Dung Le asked you to post back with a data example. In the future, always show example data when asking for help with coding. Although there are occasional problems that are simple enough to answer without having example data, that is the exception, and when you don't post example data in the usual case where it is needed, you just delay the moment when you finally have a solution.

Since you are new to Statalist, you need to learn that the code required to solve a problem often depends on details of the data set that get obscured when you create a simplified tabular display of the data. That is one of many reasons why the Forum FAQ, which all joining members are requested to read before making their first post, recommends using the -dataex- command to show example data. -dataex- preserves all of the important details of the data and metadata, and also is directly importable into Stata. So, in the future, when showing data examples, please use the -dataex- command to do so. 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.

[yilmaz kemal]

.

[yilmaz kemal]

Welcome to Statalist, Khanh Bui . As told by Dung Le and Clyde Schechter , if you provide a reproducible dataset, you can get a better help.
Here is my suggestion based on what you provided:

clear
input year month area1 area2 area3
2000 1 100 200 300
2000 2 400 500 600
2000 3 700 800 900
end

reshape long area, i(month) j(region, string)

forvalues i=1/3 {
        replace region="area`i'" if region=="`i'"    
}

rename area value
order  year month region  value

[Nick Cox]

As a small tweak to the excellent help from yilmaz kemal note that you don't need the loop:

clear
input year month area1 area2 area3
2000 1 100 200 300
2000 2 400 500 600
2000 3 700 800 900
end

reshape long area, i(month) j(region, string)

replace region = "area " + region 

rename area value
order  year month region  value

You might prefer to do something like this:

clear
input year month area1 area2 area3
2000 1 100 200 300
2000 2 400 500 600
2000 3 700 800 900
end

reshape long area, i(month) j(region)

label def region 1 "whatever" 2 "applies" 3 "here" 
label val region region 

rename area value
order  year month region  value

[Nick Cox]

Note that you will need region or area to be a numeric variable if you want to xtset (because you want to use any command that requires xtset). A numeric variable can as said have value labels. So an area variable with numeric values such as 1 2 3 and value labels that make sense to you and all your readers gets you the best of both worlds.