shifting part of the row to the next

[Luc Abbey]

Dear all,

I have a question concerning shaping the dataset. My dataset is structured as follows. Ideally i want to shift the part from variable 'restate2' of each row to next line.

1.what i have now

restate	sic	year	value	restate2	sic2	year2	value2
1	11	2012	34	0	11	2012	66
1	12	2013	453	0	12	2013	87
1	13	2014	546	0	13	2014	57
1	14	2015	68	0	14	2015	353
1	15	2016	896	0	15	2016	24
1	16	2017	585	0	16	2017	38
1	17	2018	545	0	17	2018	93

2.what I would like to have

restate	sic	year	value
1	11	2012	34
0	11	2012	66
1	12	2013	453
0	12	2013	87

...

Do you know how to do that?


Thank you in advance!

[Clyde Schechter]

This looks like a standard -reshape- operation if, as appears true in the example you show, sic and sic2 are always the same in each observation, and so are year and year2. In that case the only obstacle to directly applying reshape is that value and restate need to be renamed to have a 1 at the end of their names.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(restate sic) int(year value) byte(restate2 sic2) int(year2 value2)
1 11 2012  34 0 11 2012  66
1 12 2013 453 0 12 2013  87
1 13 2014 546 0 13 2014  57
1 14 2015  68 0 14 2015 353
1 15 2016 896 0 15 2016  24
1 16 2017 585 0 16 2017  38
1 17 2018 545 0 17 2018  93
end

assert sic == sic2 & year == year2
drop sic2 year2
rename (value  restate) =1
reshape long value restate, i(sic year)
drop _j

Note: In the future, please use the -dataex- command to show example data, not HTML tables. Your HTML table was easy enough to import to Stata, but many of them are quite intractable. And even when they go in easily, they sometimes omit crucial data about storage types and other details that can be important. If you are using Stata version 15.1, the current version, you already have the -dataex- command and need only read -help dataex-. If you are using Stata version 15.0, update to 15.1 by running -update all-. If you are using an older version of Stata, run -ssc install dataex- to get the command and then read -help dataex- to learn how to use it.

[Luc Abbey]

Dear Clyde,

Thank you for your quick reply! I forgot to say that the tricky thing is that sometimes sic and year are not always the same in the same row. So basically, I still want to shift the second part of the observation to the next row even though sic and year are not the same.

Are there some ways to get around?

Thank you!!

[Clyde Schechter]

Yes. It's nearly the same thing. restate and sic have to be renamed with 1's after them. And, in order to have something to put into the -i()- option, you need to create an observation identifier:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(restate sic) int(year value) byte(restate2 sic2) int(year2 value2)
1 11 2012  34 0 11 2012  66
1 12 2013 453 0 12 2013  87
1 13 2014 546 0 13 2014  57
1 14 2015  68 0 14 2015 353
1 15 2016 896 0 15 2016  24
1 16 2017 585 0 16 2017  38
1 17 2018 545 0 17 2018  93
end

gen long obs_no = _n
rename (restate sic year value) =1
reshape long restate sic year value, i(obs_no)
drop _j

[Romalpa Akzo]

expand would provide more direct solution.

gen long _order= _n
expand 2
bys _order: replace restate= restate2 if _n==2
drop restate2 _order

[Luc Abbey]

Hi Thank you both for your solution!! Clyde Schechter It worked well!! Thank you for your nice solution! Romalpa Akzo I don't know why, but the expand command didn't give me the format that I wanted...I basically I wanted to shift all the second part of the same line (from the variable restate2) to the next line. However it seems to me that -expand- gives me the repeated value of the previous line