Constructing a new variable for year left full-time education

[Nico Ochmann]

Dear Stata Forum,

I have panel data (id, year), and I would like to construct a new variable call it "yle" for the year a person left full-time education (a person is in full-time education if jbstat==7).
I would like to have an entry for this variable for all (survey) years for a given individual (id).
Note that this variable "yle" is not necessarily always time-invariant for a given "id" (see id==17015).
In such cases, "yle" will vary and report for instance the first year left full-time education, and the most recent year left-full time education (see id==17015).
Once again, I would highly appreciate your help in constructing this variable.
Best,
Nico

* Example generated by -dataex-. For more info, type help dataex
clear
input long id float year byte jbstat
 2727 1992 2
 2727 1993 2
 2727 2000 2
 3407 1992 1
 3407 1993 1
 3407 1994 1
 3407 1998 1
 3407 1999 2
 3407 2001 2
 3407 2002 2
 3407 2005 2
 4091 1992 2
 4091 1993 2
 4091 1994 2
 4091 1995 2
 4767 1992 1
 4767 1993 1
 5451 1992 2
 5451 1993 2
 5451 1995 2
 6135 1993 2
 6807 1992 2
 6807 1993 2
 6882 1997 1
 6882 1998 2
 7487 1992 1
 7487 1993 1
 7487 1994 1
 7487 1996 1
 8167 1992 2
 8167 1993 2
 8167 1995 2
 8167 1996 2
 9527 1992 2
 9527 1993 2
 9527 1998 2
 9527 1999 2
 9527 2000 2
 9527 2002 2
 9527 2003 2
12935 1992 7
12935 1993 2
12935 1998 2
15645 1993 7
15645 1994 2
15645 1995 2
15645 1996 2
15645 1997 2
15645 1998 2
15645 1999 2
15645 2000 2
15645 2001 2
15645 2002 2
15645 2003 2
15645 2004 2
15645 2005 2
15645 2006 2
16339 1992 7
16339 1993 2
16339 1994 2
16339 1995 2
16339 1996 2
16339 1997 2
16339 1998 2
16339 2005 2
16339 2007 2
17015 1997 7
17015 1998 7
17015 1999 7
17015 2000 2
17015 2001 7
17015 2003 2
17015 2004 2
17015 2005 2
20415 1992 7
20482 1997 2
21087 1992 2
21087 1993 2
21087 1994 2
21087 1995 2
21087 1996 2
21087 1997 2
21087 1998 2
21087 2000 2
21767 1992 2
21767 1993 2
21767 1994 1
21767 1995 1
21767 1996 2
21767 1998 2
21767 1999 1
21767 2000 2
21767 2002 2
22445 2000 7
22445 2001 7
22445 2002 7
22445 2003 7
22445 2004 7
22445 2005 2
22445 2006 2
end
label values jbstat jbstat
label def jbstat 1 "self-employed", modify
label def jbstat 2 "employed", modify
label def jbstat 7 "ft studt, school", modify

[Nick Cox]

Thanks for the data example. Here is something as a start. You seem to be implying that it's more complicated than this; if so, I don't understand the nuances.

See also

SJ-11-2 dm0055 . . . . . . . . . . . . . . Speaking Stata: Compared with ...
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q2/11 SJ 11(2):305--314 (no commands)
reviews techniques for relating values to values in other
observations


https://journals.sagepub.com/doi/pdf...867X1101100210

. egen yle = max(cond(jbstat == 7, year, .)), by(id)
(58 missing values generated)

. 
. list if yle < ., sepby(id) 

     +----------------------------------------+
     |    id   year             jbstat    yle |
     |----------------------------------------|
 41. | 12935   1992   ft studt, school   1992 |
 42. | 12935   1993           employed   1992 |
 43. | 12935   1998           employed   1992 |
     |----------------------------------------|
 44. | 15645   1993   ft studt, school   1993 |
 45. | 15645   1994           employed   1993 |
 46. | 15645   1995           employed   1993 |
 47. | 15645   1996           employed   1993 |
 48. | 15645   1997           employed   1993 |
 49. | 15645   1998           employed   1993 |
 50. | 15645   1999           employed   1993 |
 51. | 15645   2000           employed   1993 |
 52. | 15645   2001           employed   1993 |
 53. | 15645   2002           employed   1993 |
 54. | 15645   2003           employed   1993 |
 55. | 15645   2004           employed   1993 |
 56. | 15645   2005           employed   1993 |
 57. | 15645   2006           employed   1993 |
     |----------------------------------------|
 58. | 16339   1992   ft studt, school   1992 |
 59. | 16339   1993           employed   1992 |
 60. | 16339   1994           employed   1992 |
 61. | 16339   1995           employed   1992 |
 62. | 16339   1996           employed   1992 |
 63. | 16339   1997           employed   1992 |
 64. | 16339   1998           employed   1992 |
 65. | 16339   2005           employed   1992 |
 66. | 16339   2007           employed   1992 |
     |----------------------------------------|
 67. | 17015   1997   ft studt, school   2001 |
 68. | 17015   1998   ft studt, school   2001 |
 69. | 17015   1999   ft studt, school   2001 |
 70. | 17015   2000           employed   2001 |
 71. | 17015   2001   ft studt, school   2001 |
 72. | 17015   2003           employed   2001 |
 73. | 17015   2004           employed   2001 |
 74. | 17015   2005           employed   2001 |
     |----------------------------------------|
 75. | 20415   1992   ft studt, school   1992 |
     |----------------------------------------|
 94. | 22445   2000   ft studt, school   2004 |
 95. | 22445   2001   ft studt, school   2004 |
 96. | 22445   2002   ft studt, school   2004 |
 97. | 22445   2003   ft studt, school   2004 |
 98. | 22445   2004   ft studt, school   2004 |
 99. | 22445   2005           employed   2004 |
100. | 22445   2006           employed   2004 |
     +----------------------------------------+

[Hemanshu Kumar]

In your data example, if jbstat is ever 7 for an id, it does so in the earliest observation for that id. If that is true of your entire panel, this code will suffice to give you non-missing values of yle for all observations of such ids :

sort id year
gen yle = year if jbstat == 7
by id: replace yle = yle[_n-1] if missing(yle)

If this is not the case, you might also want to add the following bit of code:

egen _yle = min(yle) , by(id)
replace yle = _yle if missing(yle)
drop _yle

With the above code, for your most interesting id 17015, you get:

. list if id == 17015, noobs sep(0)

  +----------------------------------------+
  |    id   year             jbstat    yle |
  |----------------------------------------|
  | 17015   1997   ft studt, school   1997 |
  | 17015   1998   ft studt, school   1998 |
  | 17015   1999   ft studt, school   1999 |
  | 17015   2000           employed   1999 |
  | 17015   2001   ft studt, school   2001 |
  | 17015   2003           employed   2001 |
  | 17015   2004           employed   2001 |
  | 17015   2005           employed   2001 |
  +----------------------------------------+

[Nick Cox]

I hope this gets closer. Looking for the last occurrence is sometimes eased by reversing time.

bysort id : gen yle2 = year if jbstat == 7 & jbstat[_n+1] !=7 

gsort id -year 

by id : replace yle2 = yle2[_n-1] if missing(yle2)

sort id year 

by id: replace yle2 = yle2[_n-1] if missing(yle2)

list if yle < ., sepby(id)

     +-----------------------------------------------+
     |    id   year             jbstat    yle   yle2 |
     |-----------------------------------------------|
 41. | 12935   1992   ft studt, school   1992   1992 |
 42. | 12935   1993           employed   1992   1992 |
 43. | 12935   1998           employed   1992   1992 |
     |-----------------------------------------------|
 44. | 15645   1993   ft studt, school   1993   1993 |
 45. | 15645   1994           employed   1993   1993 |
 46. | 15645   1995           employed   1993   1993 |
 47. | 15645   1996           employed   1993   1993 |
 48. | 15645   1997           employed   1993   1993 |
 49. | 15645   1998           employed   1993   1993 |
 50. | 15645   1999           employed   1993   1993 |
 51. | 15645   2000           employed   1993   1993 |
 52. | 15645   2001           employed   1993   1993 |
 53. | 15645   2002           employed   1993   1993 |
 54. | 15645   2003           employed   1993   1993 |
 55. | 15645   2004           employed   1993   1993 |
 56. | 15645   2005           employed   1993   1993 |
 57. | 15645   2006           employed   1993   1993 |
     |-----------------------------------------------|
 58. | 16339   1992   ft studt, school   1992   1992 |
 59. | 16339   1993           employed   1992   1992 |
 60. | 16339   1994           employed   1992   1992 |
 61. | 16339   1995           employed   1992   1992 |
 62. | 16339   1996           employed   1992   1992 |
 63. | 16339   1997           employed   1992   1992 |
 64. | 16339   1998           employed   1992   1992 |
 65. | 16339   2005           employed   1992   1992 |
 66. | 16339   2007           employed   1992   1992 |
     |-----------------------------------------------|
 67. | 17015   1997   ft studt, school   2001   1999 |
 68. | 17015   1998   ft studt, school   2001   1999 |
 69. | 17015   1999   ft studt, school   2001   1999 |
 70. | 17015   2000           employed   2001   2001 |
 71. | 17015   2001   ft studt, school   2001   2001 |
 72. | 17015   2003           employed   2001   2001 |
 73. | 17015   2004           employed   2001   2001 |
 74. | 17015   2005           employed   2001   2001 |
     |-----------------------------------------------|
 75. | 20415   1992   ft studt, school   1992   1992 |
     |-----------------------------------------------|
 94. | 22445   2000   ft studt, school   2004   2004 |
 95. | 22445   2001   ft studt, school   2004   2004 |
 96. | 22445   2002   ft studt, school   2004   2004 |
 97. | 22445   2003   ft studt, school   2004   2004 |
 98. | 22445   2004   ft studt, school   2004   2004 |
 99. | 22445   2005           employed   2004   2004 |
100. | 22445   2006           employed   2004   2004 |
     +-----------------------------------------------+

[Nico Ochmann]

Hi Hemanshu and Nick,

Thanks both of you for replying and working on this request.

I think Nick's second attempt is very very close, I will give it some thought too with the help of Nick's code.

There's only one minor problem in line 70 for yle2: it should be 1999, I think.

Any suggestions to fix this would be greatly appreciated, I will now work on it as well.

Thanks both,
Best,
Nico

[Nick Cox]

That's a rule beyond my previous, but how about this?

egen yle = max(cond(jbstat == 7, year, .)), by(id)

list if yle < ., sepby(id) 

bysort id (year) : gen yle2 = year if jbstat == 7 & jbstat[_n+1] !=7 

list if yle < . , sepby(id)

by id : replace yle2 = yle2[_n-1] if missing(yle2)

list if yle < . , sepby(id)

egen copyback = min(yle2), by(id)

replace yle2 = copyback if missing(yle2)

list if yle < ., sepby(id)  

     +----------------------------------------------------------+
     |    id   year             jbstat    yle   yle2   copyback |
     |----------------------------------------------------------|
 41. | 12935   1992   ft studt, school   1992   1992       1992 |
 42. | 12935   1993           employed   1992   1992       1992 |
 43. | 12935   1998           employed   1992   1992       1992 |
     |----------------------------------------------------------|
 44. | 15645   1993   ft studt, school   1993   1993       1993 |
 45. | 15645   1994           employed   1993   1993       1993 |
 46. | 15645   1995           employed   1993   1993       1993 |
 47. | 15645   1996           employed   1993   1993       1993 |
 48. | 15645   1997           employed   1993   1993       1993 |
 49. | 15645   1998           employed   1993   1993       1993 |
 50. | 15645   1999           employed   1993   1993       1993 |
 51. | 15645   2000           employed   1993   1993       1993 |
 52. | 15645   2001           employed   1993   1993       1993 |
 53. | 15645   2002           employed   1993   1993       1993 |
 54. | 15645   2003           employed   1993   1993       1993 |
 55. | 15645   2004           employed   1993   1993       1993 |
 56. | 15645   2005           employed   1993   1993       1993 |
 57. | 15645   2006           employed   1993   1993       1993 |
     |----------------------------------------------------------|
 58. | 16339   1992   ft studt, school   1992   1992       1992 |
 59. | 16339   1993           employed   1992   1992       1992 |
 60. | 16339   1994           employed   1992   1992       1992 |
 61. | 16339   1995           employed   1992   1992       1992 |
 62. | 16339   1996           employed   1992   1992       1992 |
 63. | 16339   1997           employed   1992   1992       1992 |
 64. | 16339   1998           employed   1992   1992       1992 |
 65. | 16339   2005           employed   1992   1992       1992 |
 66. | 16339   2007           employed   1992   1992       1992 |
     |----------------------------------------------------------|
 67. | 17015   1997   ft studt, school   2001   1999       1999 |
 68. | 17015   1998   ft studt, school   2001   1999       1999 |
 69. | 17015   1999   ft studt, school   2001   1999       1999 |
 70. | 17015   2000           employed   2001   1999       1999 |
 71. | 17015   2001   ft studt, school   2001   2001       1999 |
 72. | 17015   2003           employed   2001   2001       1999 |
 73. | 17015   2004           employed   2001   2001       1999 |
 74. | 17015   2005           employed   2001   2001       1999 |
     |----------------------------------------------------------|
 75. | 20415   1992   ft studt, school   1992   1992       1992 |
     |----------------------------------------------------------|
 94. | 22445   2000   ft studt, school   2004   2004       2004 |
 95. | 22445   2001   ft studt, school   2004   2004       2004 |
 96. | 22445   2002   ft studt, school   2004   2004       2004 |
 97. | 22445   2003   ft studt, school   2004   2004       2004 |
 98. | 22445   2004   ft studt, school   2004   2004       2004 |
 99. | 22445   2005           employed   2004   2004       2004 |
100. | 22445   2006           employed   2004   2004       2004 |
     +----------------------------------------------------------+

[Nico Ochmann]

Nick, you are great!
You got it, thanks a lot, I would have not come up with this solution, that's for sure.
Much appreciated.
Have a great day,
Best,
Nico

[Hemanshu Kumar]

Hmm, I'm confused now.

I see how what I did in #3 does not exactly do what you wanted.

In #5 you said Nick's solution in #4 was off for observation #70, and in #1 you had also indicated that for id 17015, the value of yle would vary by year. However, you seem to be happy with the solution in #6, even though the value of yle there does not actually vary by year, for id 17015.

If I change one line in my code in #3, I think you get what you actually want:

sort id year
by id: gen yle = year if jbstat == 7 & jbstat[_n+1] != 7
by id: replace yle = yle[_n-1] if missing(yle)

egen _yle = min(yle) , by(id)
replace yle = _yle if missing(yle)
drop _yle

which gives,

. list if id == 17015, noobs sep(0)

  +----------------------------------------+
  |    id   year             jbstat    yle |
  |----------------------------------------|
  | 17015   1997   ft studt, school   1999 |
  | 17015   1998   ft studt, school   1999 |
  | 17015   1999   ft studt, school   1999 |
  | 17015   2000           employed   1999 |
  | 17015   2001   ft studt, school   2001 |
  | 17015   2003           employed   2001 |
  | 17015   2004           employed   2001 |
  | 17015   2005           employed   2001 |
  +----------------------------------------+

Isn't this what you want?

(for all the other id's, Nick's solution in #6 and mine produce the same result)

[Nick Cox]

Hemanshu Kumar Your yle and my yle2 agree for id 17015 too. In #6 my copyback is your _yle. with exactly the same device used to fill in missing values early in each panel.

[Nico Ochmann]

Thanks Hemanshu for the clarification, I really appreciated your reply as well, I just jumped on Nick's answers first because he quickly replied. I just tried your code, and it is great too.
Thanks both for your help.
Best,
Nico

[Hemanshu Kumar]

Nick Cox yikes, I saw your post on my phone screen and missed the last two columns. Sorry about that!

[Nico Ochmann]

Hi Hemanshu and Nick,

I have been working on my project, and once again thank you very much for your help.
You both coded the variable "yle", which I will use for the year a person graduated from full-time education.
I think as a robustness check, it might be a good idea to construct the variable year of labour market entry ("ylm").
If you use the sample dataset in #1, would you be able to help me construct such a variable?
It is a variable that should share the same feature as "yle", so it should have an entry for every year and given id assuming the person attended full-time education.
However, it would be the year after leaving full-time education and going into any labour market status but education (e.g., employment, self-employment etc.).

I would appreciate your thoughts on this.

Best,
Nico

[Nico Ochmann]

I think I got it, I just changed the red bit of Hemanshu's code in #8, so I should be fine.
Thanks again both,
Have a great day,
Best,
Nico

[Nico Ochmann]

Hi Hemanshu and Nick,

I hope this message finds you well.
As I wrote in #13, I made some modifications to the red bit of Hemanshu's code in #8.
However, after working on it for some time now and checking the data spreadsheet in Stata, my modifications are not correct in all instances.
For that reason, I am writing you again for your help which I would need as outlined in #12.
You may want to use the data example in #1 once again.

I would really appreciate your help.
Have a great day,
Best,
Nico

[Hemanshu Kumar]

Since it's not fully clear to me what you need, perhaps you could show the code you tried, as well as the relevant output that doesn't work the way you hoped?