Comparing people within larger units in Stata

[Michael Jensen]

Hello,

I have a dataset with a large number of observations from a panel survey over the course of several months. Within that data source I have these variables given to me directly:
personnum - A unique person number for each person in the household
personnumofmom - the person number of a person's mother
personnumofdad - the person number of a person's father
age - The age of the person
currenteducationlevel - The education level of the person (1 = not high school graduate, 2 = high school exactly, 3 = some higher ed but not a bachelor's degree, 4 = at least a bachelor's degree)

I have created these variables (and many more):
personid - a unique identifier for each person across ALL of the respondents of the survey.
familyid - a unique identifier for each family, only made up of people who live together, within a household which can be comprised of multiple families
householdid - a unique identifier for each household


I need to create an indicator variable of whether a person's dad lives in the same household as they do.

I also need to create a variable which attaches the current education level of a person's oldest child to their record.

I am not sure how to go about this. It seems like there needs to be some complicated sorting involved, and I am not sure where to start, honestly.

[Clyde Schechter]

I need to create an indicator variable of whether a person's dad lives in the same household as they do.

I'm not sure I understand your explanation of your variables. I take it that the variable personnumof dad refers to the personnum of the person's father within that household. But if the person's father doesn't live in the household, there will be no such number. So it boils down to whether personnumofdad is missing.

gen dad_in_household = !missing(personnumofdad)

I also need to create a variable which attaches the current education level of a person's oldest child to their record.

Again, based on my understanding of your variables, I don't think this is possible. In at least some instances the person's oldest child will live in a different household. These children cannot even be identified in the data as the children of interest because no variable in their observations will point to their parents: the variables personnumofmom and personnumofdad will both be missing because the child's parents are not in the same household.

If I am misunderstanding your variables, please post back with a clearer explanation of them. Also, to make it easier to develop and test code for these problems, assuming they even are solvable, please show example data. And be sure to use the -dataex- command to show that example data. If you are running version 18, 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.

[Michael Jensen]

In general your understanding is correct --- but those are the constraints. We want the education level of the person's oldest child who lives in that household. If they live in another household they are irrelevant.

[Michael Jensen]

Also, it is possible that the person number of a person's parent would exist if they were in the survey in another household, just incidentally, I suppose ... your logic should work almost all of the time, but in theory it would be a tighter comparison if we check to see if the person number of the dad have the same household

[Clyde Schechter]

Thank you for the clarification. I'd like to help, but this is too complicated for me to do all in my head. I need example data to work with to develop and test some code. Please post back, using the -dataex- command to show some. Please make the example data include a few complete households that have children, and at least one household that does not. Refer to my explanation in #2 regarding finding/obtaining -dataex- and how to use it.

[Clyde Schechter]

Also, it is possible that the person number of a person's parent would exist if they were in the survey in another household, just incidentally, I suppose ... your logic should work almost all of the time, but in theory it would be a tighter comparison if we check to see if the person number of the dad have the same household

Sorry, but this makes no sense to me, so I think my understanding of your data is still not cleared up. The variable personnumberofdad for my observation, as I understand it, is a person number within my household. If some number appears in that variable, it must refer to a personnumber in my household. That number, whatever it is, can appear in many different households, and, in each instance, it refers to a different person. If my dad does not live in my household, any such number in that personnumberofdad variable would be necessarily a data error: it points to somebody in my household, who, in this circumstance, is necessarily not my dad. What am I missing.

This confusion makes a data example even more critical.

[Michael Jensen]

When it comes to the parent in household variable, I figured it out --- the specification of personnumofmom and personnumofdad were that if they were equal to 999, there was not one in the household, so that made that simple enough.

Unfortunately with dataex, the result is that "input statement exceeds linesize limits"



I do want to note I did create these variables previously:
by famid: egen min_age = min(age)
by famid: egen max_age = max(age)

The way I imagine going about this would be to find the minimum age among the people who are the child of the current person living in the household, and then go find the child with that minimum age's currenteducationlevel, and attach it to the record of the current person ... is that not practical?

[Michael Jensen]

In other words, something like this:
1. by family ID, use personnumofmom and personnumofdad to "find" all of the children of a person who live in the same family
2. Find which one of them is the oldest
3. Create a variable "educlevelofchild"
4. Take the oldest child of the current person's education level and put it here

[Clyde Schechter]

Unfortunately with dataex, the result is that "input statement exceeds linesize limits"

OK. If you read the help file you will see that you can restrict -dataex- to just the variables needed for the present purpose, and I'm pretty sure that won't be too large for linesize limits. Run this:

dataex personnum* age currenteducationlevel personid *id

and paste the output you get between (but not including) the lines

----------------------- copy starting from the next line -----------------------

and

------------------ copy up to and including the previous line ------------------

[Michael Jensen]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(personnum age currenteducationlevel) str17 personid str14 householdid str16 familyid
102 26 . "01912800093211102" "01912800093211" "0191280009321101"
102 25 . "01912800093211102" "01912800093211" "0191280009321101"
102 26 . "01912800093211102" "01912800093211" "0191280009321102"
102 25 . "01912800093211102" "01912800093211" "0191280009321102"
102 26 . "01912800093211102" "01912800093211" "0191280009321103"
102 26 . "01912800093211102" "01912800093211" "0191280009321103"
102 26 . "01912800093211102" "01912800093211" "0191280009321104"
102 13 . "01912835897811102" "01912835897811" "0191283589781101"
102 13 . "01912835897811102" "01912835897811" "0191283589781102"
102 13 . "01912835897811102" "01912835897811" "0191283589781103"
102 13 . "01912835897811102" "01912835897811" "0191283589781104"
102 13 . "01912835897861102" "01912835897861" "0191283589786101"
102 13 . "01912835897861102" "01912835897861" "0191283589786102"
102 13 . "01912835897861102" "01912835897861" "0191283589786103"
103  8 . "01913300030111103" "01913300030111" "0191330003011101"
103  7 . "01913300030111103" "01913300030111" "0191330003011101"
103  7 . "01913300030111103" "01913300030111" "0191330003011102"
103  8 . "01913300030111103" "01913300030111" "0191330003011102"
103  8 . "01913300030111103" "01913300030111" "0191330003011103"
103  8 . "01913300030111103" "01913300030111" "0191330003011103"
103  8 . "01913300030111103" "01913300030111" "0191330003011104"
104  6 . "01913300077831104" "01913300077831" "0191330007783101"
103  9 . "01913300077831103" "01913300077831" "0191330007783101"
103 10 . "01913300077831103" "01913300077831" "0191330007783101"
104  7 . "01913300077831104" "01913300077831" "0191330007783101"
301 28 . "01913300077831301" "01913300077831" "0191330007783102"
301 28 . "01913300077831301" "01913300077831" "0191330007783102"
104  7 . "01913300077831104" "01913300077831" "0191330007783103"
104  6 . "01913300077831104" "01913300077831" "0191330007783103"
103 10 . "01913300077831103" "01913300077831" "0191330007783103"
103 10 . "01913300077831103" "01913300077831" "0191330007783103"
301 28 . "01913300077831301" "01913300077831" "0191330007783104"
301 28 . "01913300077831301" "01913300077831" "0191330007783104"
104  7 . "01913300077831104" "01913300077831" "0191330007783105"
103 10 . "01913300077831103" "01913300077831" "0191330007783105"
104  6 . "01913300077831104" "01913300077831" "0191330007783105"
103 10 . "01913300077831103" "01913300077831" "0191330007783105"
301 28 . "01913300077831301" "01913300077831" "0191330007783106"
301 28 . "01913300077831301" "01913300077831" "0191330007783106"
104  6 . "01913300077831104" "01913300077831" "0191330007783107"
103 10 . "01913300077831103" "01913300077831" "0191330007783107"
301 28 . "01913300077831301" "01913300077831" "0191330007783108"
103 17 . "01913339809911103" "01913339809911" "0191333980991101"
103 17 . "01913339809911103" "01913339809911" "0191333980991101"
103 18 . "01913339809911103" "01913339809911" "0191333980991102"
103 17 . "01913339809911103" "01913339809911" "0191333980991102"
103 17 . "01913339809911103" "01913339809911" "0191333980991103"
103 18 . "01913339809911103" "01913339809911" "0191333980991103"
103 17 . "01913339809911103" "01913339809911" "0191333980991104"
201  0 . "01913345155311201" "01913345155311" "0191334515531101"
104  3 . "01913345155311104" "01913345155311" "0191334515531101"
103  6 . "01913345155311103" "01913345155311" "0191334515531101"
101 28 . "01913345155311101" "01913345155311" "0191334515531101"
103  5 . "01913345155311103" "01913345155311" "0191334515531101"
101 28 . "01913345155311101" "01913345155311" "0191334515531101"
104  3 . "01913345155311104" "01913345155311" "0191334515531101"
201  0 . "01913345155311201" "01913345155311" "0191334515531101"
102 30 . "01913345155311102" "01913345155311" "0191334515531101"
104  3 . "01913345155311104" "01913345155311" "0191334515531102"
104  3 . "01913345155311104" "01913345155311" "0191334515531102"
101 28 . "01913345155311101" "01913345155311" "0191334515531102"
201  0 . "01913345155311201" "01913345155311" "0191334515531102"
201  1 . "01913345155311201" "01913345155311" "0191334515531102"
103  6 . "01913345155311103" "01913345155311" "0191334515531102"
101 28 . "01913345155311101" "01913345155311" "0191334515531102"
103  6 . "01913345155311103" "01913345155311" "0191334515531102"
104  3 . "01913345155311104" "01913345155311" "0191334515531103"
201  1 . "01913345155311201" "01913345155311" "0191334515531103"
201  0 . "01913345155311201" "01913345155311" "0191334515531103"
101 29 . "01913345155311101" "01913345155311" "0191334515531103"
101 28 . "01913345155311101" "01913345155311" "0191334515531103"
103  6 . "01913345155311103" "01913345155311" "0191334515531103"
103  6 . "01913345155311103" "01913345155311" "0191334515531103"
104  3 . "01913345155311104" "01913345155311" "0191334515531103"
201  0 . "01913345155311201" "01913345155311" "0191334515531104"
104  3 . "01913345155311104" "01913345155311" "0191334515531104"
103  6 . "01913345155311103" "01913345155311" "0191334515531104"
101 28 . "01913345155311101" "01913345155311" "0191334515531104"
501 15 . "01913345165511501" "01913345165511" "0191334516551101"
501 15 . "01913345165511501" "01913345165511" "0191334516551103"
501 15 . "01913345165511501" "01913345165511" "0191334516551105"
103  5 . "01913345196411103" "01913345196411" "0191334519641101"
103  5 . "01913345196411103" "01913345196411" "0191334519641101"
103  5 . "01913345196411103" "01913345196411" "0191334519641102"
103  5 . "01913345196411103" "01913345196411" "0191334519641102"
103  5 . "01913345196411103" "01913345196411" "0191334519641103"
103  5 . "01913345196411103" "01913345196411" "0191334519641103"
103  5 . "01913345196411103" "01913345196411" "0191334519641104"
102 18 . "01913365148011102" "01913365148011" "0191336514801101"
103 14 . "01913365148011103" "01913365148011" "0191336514801101"
103 15 . "01913365148011103" "01913365148011" "0191336514801101"
102 18 . "01913365148011102" "01913365148011" "0191336514801101"
103 14 . "01913365148011103" "01913365148011" "0191336514801102"
102 18 . "01913365148011102" "01913365148011" "0191336514801102"
103 15 . "01913365148011103" "01913365148011" "0191336514801102"
102 18 . "01913365148011102" "01913365148011" "0191336514801102"
102 18 . "01913365148011102" "01913365148011" "0191336514801103"
103 15 . "01913365148011103" "01913365148011" "0191336514801103"
103 14 . "01913365148011103" "01913365148011" "0191336514801103"
102 18 . "01913365148011102" "01913365148011" "0191336514801103"
end

[Michael Jensen]

I do want to note, you are right to limit the scope of the variables to within family, within household. The personnumberofdad / mom will only be valid if they live in the same family, and in the same household.

[Michael Jensen]

Sorry for the repetitive replies. I think you need more information, so I added it:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(personnum personnumberofmom personnumberofdad age currenteducationlevel) str17 personid str14 hhid str16 familyid
102  101 999 25 . "01912800093211102" "01912800093211" "0191280009321101"
102  101 999 26 . "01912800093211102" "01912800093211" "0191280009321101"
102  101 999 26 . "01912800093211102" "01912800093211" "0191280009321102"
102  101 999 25 . "01912800093211102" "01912800093211" "0191280009321102"
102  101 999 26 . "01912800093211102" "01912800093211" "0191280009321103"
102  101 999 26 . "01912800093211102" "01912800093211" "0191280009321103"
102  101 999 26 . "01912800093211102" "01912800093211" "0191280009321104"
102 999 999 13 . "01912835897811102" "01912835897811" "0191283589781101"
102 999 999 13 . "01912835897811102" "01912835897811" "0191283589781102"
102 999 999 13 . "01912835897811102" "01912835897811" "0191283589781103"
102 999 999 13 . "01912835897811102" "01912835897811" "0191283589781104"
102 999 999 13 . "01912835897861102" "01912835897861" "0191283589786101"
102 999 999 13 . "01912835897861102" "01912835897861" "0191283589786102"
102 999 999 13 . "01912835897861102" "01912835897861" "0191283589786103"
103  102  101  8 . "01913300030111103" "01913300030111" "0191330003011101"
103  102  101  7 . "01913300030111103" "01913300030111" "0191330003011101"
103  102  101  8 . "01913300030111103" "01913300030111" "0191330003011102"
103  102  101  7 . "01913300030111103" "01913300030111" "0191330003011102"
103  102  101  8 . "01913300030111103" "01913300030111" "0191330003011103"
103  102  101  8 . "01913300030111103" "01913300030111" "0191330003011103"
103  102  101  8 . "01913300030111103" "01913300030111" "0191330003011104"
104  102  101  7 . "01913300077831104" "01913300077831" "0191330007783101"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783101"
103  102  101  9 . "01913300077831103" "01913300077831" "0191330007783101"
104  102  101  6 . "01913300077831104" "01913300077831" "0191330007783101"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783102"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783102"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783103"
104  102  101  6 . "01913300077831104" "01913300077831" "0191330007783103"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783103"
104  102  101  7 . "01913300077831104" "01913300077831" "0191330007783103"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783104"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783104"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783105"
104  102  101  6 . "01913300077831104" "01913300077831" "0191330007783105"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783105"
104  102  101  7 . "01913300077831104" "01913300077831" "0191330007783105"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783106"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783106"
104  102  101  6 . "01913300077831104" "01913300077831" "0191330007783107"
103  102  101 10 . "01913300077831103" "01913300077831" "0191330007783107"
301 999 999 28 . "01913300077831301" "01913300077831" "0191330007783108"
103  102  101 17 . "01913339809911103" "01913339809911" "0191333980991101"
103  102  101 17 . "01913339809911103" "01913339809911" "0191333980991101"
103  102  101 18 . "01913339809911103" "01913339809911" "0191333980991102"
103  102  101 17 . "01913339809911103" "01913339809911" "0191333980991102"
103  102  101 17 . "01913339809911103" "01913339809911" "0191333980991103"
103  102  101 18 . "01913339809911103" "01913339809911" "0191333980991103"
103  102  101 17 . "01913339809911103" "01913339809911" "0191333980991104"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531101"
102 999 999 30 . "01913345155311102" "01913345155311" "0191334515531101"
201  101  102  0 . "01913345155311201" "01913345155311" "0191334515531101"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531101"
201  101  102  0 . "01913345155311201" "01913345155311" "0191334515531101"
103  101  102  5 . "01913345155311103" "01913345155311" "0191334515531101"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531101"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531101"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531101"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531102"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531102"
201  101  102  0 . "01913345155311201" "01913345155311" "0191334515531102"
201  101  102  1 . "01913345155311201" "01913345155311" "0191334515531102"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531102"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531102"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531102"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531102"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531103"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531103"
101 999 999 29 . "01913345155311101" "01913345155311" "0191334515531103"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531103"
201  101  102  0 . "01913345155311201" "01913345155311" "0191334515531103"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531103"
201  101  102  1 . "01913345155311201" "01913345155311" "0191334515531103"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531103"
101 999 999 28 . "01913345155311101" "01913345155311" "0191334515531104"
103  101  102  6 . "01913345155311103" "01913345155311" "0191334515531104"
104  101  102  3 . "01913345155311104" "01913345155311" "0191334515531104"
201  101  102  0 . "01913345155311201" "01913345155311" "0191334515531104"
501  101  502 15 . "01913345165511501" "01913345165511" "0191334516551101"
501  101  502 15 . "01913345165511501" "01913345165511" "0191334516551103"
501  101  502 15 . "01913345165511501" "01913345165511" "0191334516551105"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641101"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641101"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641102"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641102"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641103"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641103"
103  102  101  5 . "01913345196411103" "01913345196411" "0191334519641104"
103  101  401 14 . "01913365148011103" "01913365148011" "0191336514801101"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801101"
103  101  401 15 . "01913365148011103" "01913365148011" "0191336514801101"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801101"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801102"
103  101  401 14 . "01913365148011103" "01913365148011" "0191336514801102"
103  101  401 15 . "01913365148011103" "01913365148011" "0191336514801102"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801102"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801103"
103  101  401 15 . "01913365148011103" "01913365148011" "0191336514801103"
102  101  401 18 . "01913365148011102" "01913365148011" "0191336514801103"
103  101  401 14 . "01913365148011103" "01913365148011" "0191336514801103"
end

Thank you so much for your help. I really do appreciate it

[Clyde Schechter]

OK, glad we have that. I should point out that the example data you chose to show has only missing values for currenteducationlevel, so it is not really a suitable example for your current problem. I have dealt with that by modifying your data, introducing randomly selected values for most of the observations, and leaving a few missing.

I also notice that although there is an exact 1:1 correspondence between personid and combinations of personnum hhid, neither of these uniquely identifies observations in the data set. It is also unclear whether they identify distinct people who simply recur in the panel survey multiple times at different ages. The same value of personid is consistently associated across observations with the same values of personnumberofmom and personnumberofdad. But with (sometimes) different values of age (understandable in longitudinal data), but also with different values of familyid (even though they always, in the example, have the same value for hhid), which doesn't make sense to me. Also, sometimes there are multiple observations having the same value of both personid and age, but sometimes differing in their values of familyid.

So, I still don't understand the data organization here and can't proceed without additional clarification on all of this. To solve your problem is it absolutely critical that I be able to identify the same person over time here. At present my hunch is that personid, or, equivalently, the combination of personnum and hhid, does this--but then I do not understand why familyid can change even though hhid does not. Moreover, family id even changes when the same personid appears in different observations at the same age. And, I can't understand why the same personid ever even does appear in multiple observations at the same age--in order to accommodate as many as 7 different observations of the same person at the same age, the survey would have to be administered at least every two months. Is that the case?

[Michael Jensen]

Broadly speaking, what you are stating is all accurate again ---

Family ID can change because a household can include multiple families, but not vice versa, first.

The ID numbers are comprised of different underlying variables that the publication indicates should allow for the identification of different people, or families, or households, in different combinations, as you describe observing, but for simplicity I removed them because I thought you would see that.

"but also with different values of familyid (even though they always, in the example, have the same value for hhid)" <-- this I don't understand either, other than ... perhaps in theory a person could move to a different household in a later month, and since family id is a subset of household id, this would lead to their family id changing in the next period. These are not static necessarily.

It was administered monthly

I may not be able to get too much additional information right now to be worth you continuing to work on this, but you were very helpful, thank you.

[Clyde Schechter]

perhaps in theory a person could move to a different household in a later month, and since family id is a subset of household id, this would lead to their family id changing in the next period

In theory, yes. But that isn't what's going on in this data, because even though family_id changes, hhid does not.

Let me leave you with some code that may at least point you in the right direction. It presumes that personnum and hhid jointly identify unique persons. It also requires the kind of internal consistency of variables within unique persons that we've been batting around here. Anyway, once you get the data organization ironed out, this code might work, or come close to doing what you want. You might take it from there, or if you want more help, post back with fixed up data.

//    GET RID OF MAGIC NUMBER MISSING VALUE CODES
mvdecode personnumberof*, mv(999)

preserve
keep personnum* hhid age currenteducationlevel
reshape long personnumberof, i(personnum hhid age) j(parent) string
drop if missing(personnumberof)
drop parent personnum
duplicates drop
rename personnumberof personnum
rename (currenteducationlevel age) child_=
tempfile children
save `children'

restore
joinby personnum hhid using `children', unmatched(master)
by personnum hhid (child_age), sort: gen oldest_child_educ = child_currenteducationlevel[_N]

Note: The first command, that replaces those 999's with actual Stata missing values, is necessary for this code to work, because it uses Stata's -missing()- function, which will not recognize 999 as a missing value. But even if it weren't necessary, using magic number missing value codes is bad practice because nothing in Stata works well with them. Stata has its own internal representation for missing values, and this is what you should always use in Stata--it eliminates having to tag all the commands with an -if- clause to exclude observations with 999, and that, makes for both cleaner and faster code. It also reduces coding errors, because in any reasonably long program, you are likely to forget the -if something != 999- tag somewhere along the line, leading to incorrect results.