Transpose wide to long data

[Mia Pham]

Dear Statalist,

I have the following data that show employment history of individuals

id	firm_id	frommonth	fromyear	tomonth	toyear
1	10	2	2000	4	2002
1	20	4	2002	7	2004
1	30	7	2004	10	2007
2	40	6	1998	8	2000
2	50	9	2002	11	2003
2	20	11	2003	8	2006

Now I want to transpose the data in long shape like the follows:

id	year	firm_id
1	2000	10
1	2001	10
1	2002	20
1	2003	20
1	2004	20
1	2005	30
1	2006	30
1	2007	30
2	1998	40
2	1999	40
2	2000	40
2	2001	.
2	2002	50
2	2003	50
2	2004	20
2	2005	20
2	2006	20

Note that the corresponding firm of an individual will depends on how long they stay in that firm in that year. For example, in April 2002, id 1 moved from firm 10 to firm 20, so they stay with firm 20 for more time than that of firm 10 in 2002. Therefore, the corresponding firm in 2002 if id 1 is 20.
There could be some year where the person is not employed at all (like id 2 in 2001). In this case even if they only start working for firm 50 from September 2002, the corresponding firm for them in 2002 is firm 50.

I tried several ways to do the transpose of the data but it doesn't work. I would appreciate if you can help me with this issue.

Thank you!

[Andrew Musau]

Two comments:

1. With 44 posts, you should not be presenting data examples in this way. Always use the dataex command.

Note that the corresponding firm of an individual will depends on how long they stay in that firm in that year. For example, in April 2002, id 1 moved from firm 10 to firm 20, so they stay with firm 20 for more time than that of firm 10 in 2002. Therefore, the corresponding firm in 2002 if id 1 is 20.
There could be some year where the person is not employed at all (like id 2 in 2001). In this case even if they only start working for firm 50 from September 2002, the corresponding firm for them in 2002 is firm 50.

2. For your specified conditions, calculate the differences and ultimately change the "to year" to correspond to the wanted allocation. I will not do this, but the following shows you an approach to get the layout that you want.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id firm_id frommonth) int fromyear byte tomonth int toyear
1 10  2 2000  4 2002
1 20  4 2002  7 2004
1 30  7 2004 10 2007
2 40  6 1998  8 2000
2 50  9 2002 11 2003
2 20 11 2003  8 2006
end

expand toyear-fromyear
bys id firm_id fromyear toyear: gen year= fromyear+_n-1
contract id year firm_id

. l, sepby(id)

     +-----------------------------+
     | id   firm_id   year   _freq |
     |-----------------------------|
  1. |  1        10   2000       1 |
  2. |  1        10   2001       1 |
  3. |  1        20   2002       1 |
  4. |  1        20   2003       1 |
  5. |  1        30   2004       1 |
  6. |  1        30   2005       1 |
  7. |  1        30   2006       1 |
     |-----------------------------|
  8. |  2        40   1998       1 |
  9. |  2        40   1999       1 |
 10. |  2        50   2002       1 |
 11. |  2        20   2003       1 |
 12. |  2        20   2004       1 |
 13. |  2        20   2005       1 |
     +-----------------------------+