Contract year reshape

[Cong Gu]

Dear Statalists,

Hope all is well.

I am dealing with the contract data with specific contract start and end years as shown below:

Contract ID	Contract Start Year	Contract End Year
5	05aug1987	05nov1987
11	01sep1987	01sep1994
13	01aug1987	01aug1988
14	01aug1987	03feb1988
16	12sep1987	01jan1989
17	01sep1987	01mar1988
18	01sep1987	01sep1990
19	12sep1987	01oct1997
21	01oct1987	01oct1992
22	01oct1987	01oct1992
24	02sep1987	01oct1988
25	02sep1987	01oct1988
26	01sep1987	01apr1997
28	12sep1987	01nov1990
29	12sep1987	01nov1992
30	01sep1987	01sep1992
31	12aug1987	01mar1995
32	12aug1987	01mar1995
33	12aug1987	01mar1990
34	12aug1987	01mar1989
35	12aug1987	01mar1990
36	12aug1987	01mar1995

Could you please kindly let me know how to reshape this data into long format in Stata?

For example, for Contract ID "18", it should be presented as follows:

Contract ID	Year
18	1987
18	1988
18	1989
18	1990

Many thanks in advance.

Best,
Cong

[Clyde Schechter]

This is more than just a reshape. You also want to extract the year from the dates and fill in the in-between years.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte contract_id str10 contract_start_year str9 contract_end_year
 5 "05aug1987 " "05nov1987"
11 "01sep1987 " "01sep1994"
13 "01aug1987 " "01aug1988"
14 "01aug1987 " "03feb1988"
16 "12sep1987 " "01jan1989"
17 "01sep1987 " "01mar1988"
18 "01sep1987 " "01sep1990"
19 "12sep1987 " "01oct1997"
21 "01oct1987 " "01oct1992"
22 "01oct1987 " "01oct1992"
24 "02sep1987 " "01oct1988"
25 "02sep1987 " "01oct1988"
26 "01sep1987 " "01apr1997"
28 "12sep1987 " "01nov1990"
29 "12sep1987 " "01nov1992"
30 "01sep1987 " "01sep1992"
31 "12aug1987 " "01mar1995"
32 "12aug1987 " "01mar1995"
33 "12aug1987 " "01mar1990"
34 "12aug1987 " "01mar1989"
35 "12aug1987 " "01mar1990"
36 "12aug1987 " "01mar1995"
end

reshape long contract_@_year, i(contract_id) j(start_end) string
gen year = yofd(daily(contract__year, "DMY"))
assert !missing(year)
drop start_end contract__year
by contract_id (year), sort: drop if year == year[_n-1]
tsset contract_id year
tsfill

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.

[William Lisowski]

This is very similar to the problem addressed in post #3 at

https://www.statalist.org/forums/for...34#post1674534

in particular by using the expand command. The advice about presenting example data using the dataex command is relevant, also.

[Cong Gu]

This is more than just a reshape. You also want to extract the year from the dates and fill in the in-between years.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte contract_id str10 contract_start_year str9 contract_end_year
5 "05aug1987 " "05nov1987"
11 "01sep1987 " "01sep1994"
13 "01aug1987 " "01aug1988"
14 "01aug1987 " "03feb1988"
16 "12sep1987 " "01jan1989"
17 "01sep1987 " "01mar1988"
18 "01sep1987 " "01sep1990"
19 "12sep1987 " "01oct1997"
21 "01oct1987 " "01oct1992"
22 "01oct1987 " "01oct1992"
24 "02sep1987 " "01oct1988"
25 "02sep1987 " "01oct1988"
26 "01sep1987 " "01apr1997"
28 "12sep1987 " "01nov1990"
29 "12sep1987 " "01nov1992"
30 "01sep1987 " "01sep1992"
31 "12aug1987 " "01mar1995"
32 "12aug1987 " "01mar1995"
33 "12aug1987 " "01mar1990"
34 "12aug1987 " "01mar1989"
35 "12aug1987 " "01mar1990"
36 "12aug1987 " "01mar1995"
end

reshape long contract_@_year, i(contract_id) j(start_end) string
gen year = yofd(daily(contract__year, "DMY"))
assert !missing(year)
drop start_end contract__year
by contract_id (year), sort: drop if year == year[_n-1]
tsset contract_id year
tsfill

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.

Dear Clyde,

Many thanks for this kind help. Much appreciated about that.

Best wishes,
Cong