Changing Columns into Rows

[Ricky Gettys]

I need to move data in columns into rows. (moving the b1 and b2 columns below into a1 and a2 but as a new case, similar to what is done with reshape.)

Beginning table:

id	a1	a2	b1	b2
1	1	2	3	4
2	5	6	7	8
3	9	10	11	12
4	13	14	15	16
5	17	18	19	20

Desired table:

id	Case	a1	a2
1	1	1	2
1	2	3	4
2	1	5	6
2	2	7	8
3	1	9	10
3	2	11	12
4	1	13	14
4	2	15	16
5	1	17	18
5	2	19	20

I have tried to use reshape and xpose, but can't get it to do what I want. Any advice? I feel I am close but don't even know the proper terminology to type.

[Rich Goldstein]

looks like you want -stack-; see

help stack

[Ricky Gettys]

Thank you, that seems like a good first step.

However, when I use stack, it eliminates the id variable.

stack a1-b2, into(a1 a2) wide

results:

_stack	a1	a2
1	1	2
1	5	6
1	9	10
1	13	14
1	17	18
2	3	4
2	7	8
2	11	12
2	15	16
2	19	20

I searched the help, but there is no info on keeping other variables not in the command. (wide just adds b1 and b2 columns but not ID columns)
My actual dataset is a lot more complex and has location data for each response that I am stacking and I can't lose it (represented by the ID column), how can I retain the important descriptive data that I am losing with stack?
Any advice?

desired table:

id	_stack	a1	a2
1	1	1	2
2	1	5	6
3	1	9	10
4	1	13	14
5	1	17	18
1	2	3	4
2	2	7	8
3	2	11	12
4	2	15	16
5	2	17	18

[Rich Goldstein]

since you did not use -dataex-, I can't try using your data; please read the FAQ and post some data using -dataex-

[ram singh]

Hi there are some general steps below (Please follow Rich Goldstein's advise for specificity)
1. create a duplicate dataset using temporary dataset / or actual dataset
2. Have one dataset have a1 a2 with id and drop b1 b2: so this dataset has variables: id a1 a2
3. Similarily the other dataset should have variables id b1 b2 (drop a1 a2)
4. Rename the variables as a1 a2 in the second dataset to ensure both dataset have same variable names: that is id a1 a2
5. Then append one to another using -append- command
6. Then sort the variables a1 a2
7. Then you can create a variable using bysort id: gen case = _n
Hopefully that will get you there

[Romalpa Akzo]

stack id a1 a2 id b1 b2, into(id a1 a2) clear

[Ricky Gettys]

Thanks Romalpa, this works great.

I tested this out with additional numeric variables and variables that I want to stack under the A columns and it works well. I added the "demo" to stand for other demographic variables I am working with. (just in case someone else needs this later, I added the dataex, code, and results below)

*sample data
clear
input float(id a1 a2 a3 b1 b2 b3 c1 c2 c3) str17 demo1 float(demo2 demo3)
1  1  2  3  4  5  6  7  8  9 "black"  1 1
2 10 11 12 13 14 15 16 17 18 "white"   2 2
3 19 20 21 22 23 24 25 26 27 "green"  3 3
4 28 29 30 31 32 33 34 35 36 "blue" 4 4
5 37 38 39 40 41 42 43 44 45 "black" 5 5
end

This code nests the demographics and cases as I want.

stack id a1 a2 a3 demo1 demo2 demo3 id b1 b2 b3 demo1 demo2 demo3 id c1 c2 c3 demo1 demo2 demo3, into(id a1 a2 a3 demo1 demo2 demo3) clear
sort id _stack
rename _stack case
order id case

Final table:

	id	case	a1	a2	a3	demo1	demo2	demo3
1	1	1	1	2	3	black	1	1
2	1	2	4	5	6	black	1	1
3	1	3	7	8	9	black	1	1
4	2	1	10	11	12	white	2	2
5	2	2	13	14	15	white	2	2
6	2	3	16	17	18	white	2	2
7	3	1	19	20	21	green	3	3
8	3	2	22	23	24	green	3	3
9	3	3	25	26	27	green	3	3
10	4	1	28	29	30	blue	4	4
11	4	2	31	32	33	blue	4	4
12	4	3	34	35	36	blue	4	4
13	5	1	37	38	39	black	5	5
14	5	2	40	41	42	black	5	5
15	5	3	43	44	45	black	5	5

[Carole J. Wilson]

-stack- does work with string variables:

tostring _all, replace
desc

stack id a1 a2 a3 demo1 demo2 demo3 id b1 b2 b3 demo1 demo2 demo3 id c1 c2 c3 demo1 demo2 demo3, into(id a1 a2 a3 demo1 demo2 demo3) clear
sort id _stack

[Ricky Gettys]

Carole,
You are right, I had a problem with dataex that I figured out. I edited my previous post to correct my unnecessary question. Sorry for the confusion.