Question About How to "Stack" Variables

[Erika Kociolek]

Hi there,

I have a dataset similar to the following:

clear
input int parentid int id int childid int ultimateid
1 4 8 0
1 4 6 1
2 9 . 2
end

I want to create a dataset where all columns except for ultimateid are stacked, and ultimateid is maintained - for example:

clear
input int newid int ultimateid
1 1
1 1
2 2
4 1
4 1
9 2
8 1
6 1
. 2
end

I know this will yield duplicates; I can de-dup later.

After digging around on Statalist, I saw some suggestions to use reshape and/or stack, but am not quite sure how to get those to work in this particular case.

Thank you in advance for any advice or suggestions.

Erika

[Clyde Schechter]

On the assumption that your "ultimateid" variable appears in sorted order in your real data, as it does in your example:

rename parentid id1
rename id id2
rename childid id3

reshape long id, i(ultimateid) j(_j)
sort _j ultimateid

drop _j // OPTIONAL

By the way, I am assuming that you made a mistake in showing your desired output, because what were originally 0 values for ultimate id have for no apparent reason all changed to 1's. If that is not an error, then my code is not doing what you want, and I would ask you to explain how the 0 ultimate ids become 1s (and why none of the other ultimate id's change.)

[Erika Kociolek]

Hi Clyde,

Thanks for your response, and thank you for pointing out that error (the zero that should be a one) - I've corrected it below:

clear
input int parentid int id int childid int ultimateid
1 4 8 1
1 4 6 1
2 9 . 2
end

Best,
Erika

[Erika Kociolek]

Hi Clyde,

My desired output was correct, but my initial dataset wasn't. When I try to reshape using the corrected dataset, I can't because ultimateid is not unique. Are there any other options?

Erika

[Clyde Schechter]

clear
input int parentid int id int childid int ultimateid
1 4 8 1
1 4 6 1
2 9 . 2
end

rename parentid id1
rename id id2
rename childid id3

gen long obs_no = _n

reshape long id, i(obs_no) j(_j)

sort _j obs_no

(And you can -drop _j and obs_no- if you like: id and ultimateid are the variables you wanted.)

[Erika Kociolek]

Worked wonderfully - thank you!

Erika