Household data reshaped from long to wide format

[Lilly Tee]

Hi, I currently have data that is structured at an individual level but is provided by the household. I was looking to convert this from a long to a wide format. So instead, it would report observations for each identifier (i.e., household) are presented in a single row. However, I also have many individual-level variables such as age, gender, health, qualification and so on.

I tried the following:
reshape wide gender age birthyear ... , i(pidp) j(hidp)
Where pidp is the individual identifier and hidp is the household identifier. Any help in doing this would be appreciated

[Clyde Schechter]

I doubt anybody can help you without example data, so we can see exactly what the organization, naming, and structure of the existing data set is. Please post back using the -dataex- command to show this. 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.

[Lilly Tee]

Hi Clyde, took your advice and did the following

clear
input long(pidp pid hidp) byte(person_number gender) int(age dob) byte(qualification hhsize) float monthly_income
22445 10127798 278447092 1 2 30 1984 -8 1 2856.67
29925 10192697 620316412 1 2 38 1977 -8 3 2089
76165 10689869 142378412 1 2 32 1982 -8 2 2704.17
223725 11926929 619140012 3 1 40 1975 -8 3 1999.06
280165 12430439 755847212 1 2 36 1979 -8 4 3089.7
333205 12908843 416466012 3 2 25 1990 -8 3 2005.83
387605 13361163 347194412 3 2 27 1988 -8 3 896.18
813285 16441141 550902012 3 1 45 1970 -8 3 1332.54
850005 16714296 211922012 2 1 26 1988 -8 2 1213.33
1587125 17870879 619493612 2 2 50 1965 -8 2 4666.67
1833965 50832336 754494012 1 1 50 1965 -8 2 2604.17
2067205 65313828 74596012 3 1 26 1988 -8 3 501.74
2270525 76446336 824894412 3 2 18 1997 9 4 822.03
2853965 96577029 688642812 1 2 33 1982 -8 2 2941.67
3568645 118692895 757669612 5 1 20 1995 96 7 0
3587685 118781707 485146012 3 1 34 1980 -8 3 1300.04
3663845 119065835 417934812 6 1 31 1983 -8 6 216.67
3667245 119074613 281764812 3 2 27 1988 -8 3 2025
3705325 119277506 145914412 2 2 61 1953 -8 2 0
4091565 135447429 484425212 3 1 37 1978 -8 3 2666.67
4454005 154358304 484092012 2 1 70 1944 -8 2 1131.04
4473725 154588539 416642812 2 1 59 1955 -8 2 5031.85
4849085 176725733 349050812 2 1 32 1982 -8 2 3573.67
4853165 176977635 825588012 4 1 45 1970 17 4 2200
68002049 10017992 73793612 2 2 35 1979 -8 3 2333.33
68002725 10023526 73800412 1 2 61 1953 -8 1 1171.43
68004087 -8 68013612 1 1 65 1949 -8 1 1358.33
68006127 -8 68020412 1 2 45 1969 -8 4 385
68006135 -8 68020412 3 2 23 1991 -8 4 1012.88
68006139 -8 68020412 2 2 20 1994 -8 4 0
68006807 -8 68027212 1 2 78 1936 -8 1 585
68008847 -8 68040812 1 2 57 1957 -8 1 1971
68009527 -8 68047612 1 1 38 1977 -8 2 2450
68010887 -8 68054412 1 2 51 1963 -8 2 2300
68014287 -8 68068012 1 2 45 1969 -8 3 1304.56
68014291 -8 68074812 1 2 22 1993 -8 2 1300
68017687 -8 68081612 1 2 36 1979 -8 3 836.55
68020407 -8 68095212 1 2 78 1936 -8 2 1178.66
68020564 -8 68020412 4 1 45 1970 12 4 223.17
68021765 10200436 73807212 1 2 56 1958 -8 4 1832.07
68021769 10200479 73807212 2 1 54 1960 -8 4 7712.41
68021773 10200509 73807212 3 2 28 1987 -8 4 2166.67
68021781 30139368 73807212 4 1 21 1993 -8 4 1701.47
68025847 -8 68102012 1 2 79 1935 -8 1 1881.16
68028567 -8 68108812 1 2 43 1971 -8 4 775.89
68028571 -8 68108812 2 1 48 1966 -8 4 150.67
68028575 -8 68115612 1 2 24 1991 -8 2 0
68029927 -8 68122412 1 2 43 1972 -8 4 88.8
68029931 -8 68122412 2 1 46 1969 -8 4 2264.79
68029935 -8 68122412 3 2 18 1996 -8 4 83.33
68031967 -8 68129212 1 2 67 1947 -8 1 1701.6
68032647 -8 68136012 1 1 30 1984 -8 3 2730
68035365 10403086 73814012 1 1 63 1951 -8 1 3175
68035367 -8 68142812 1 1 34 1980 -8 2 6075
68036727 -8 68149612 1 1 83 1931 -8 1 1062
68037407 -8 68156412 1 2 46 1968 -8 2 1454
68037411 -8 68156412 2 2 17 1998 -8 2 0
68041487 -8 68170012 1 2 45 1969 -8 4 3480.88
68041491 -8 68170012 2 1 42 1973 -8 4 3600
68042167 -8 68176812 1 1 45 1970 -8 3 3988.13
68042171 -8 68176812 2 2 44 1970 -8 3 3372
68043527 -8 68183612 1 1 61 1953 -8 2 3038.57
68044207 -8 68190412 1 2 39 1975 -8 5 1428.77
68044211 -8 68190412 2 1 42 1973 -8 5 132.3
68044887 -8 68197212 1 2 68 1946 -8 1 1157
68045567 -8 68204012 1 2 53 1961 -8 3 3004.17
68045571 -8 68204012 2 1 55 1959 -8 3 6458.33
68045575 -8 68204012 3 1 18 1996 -8 3 430
68046247 -8 68210812 1 1 72 1942 -8 2 2036.67
68046251 -8 68210812 2 2 70 1945 -8 2 568.66
68046927 -8 68217612 1 2 42 1972 -8 3 3732
68048287 -8 68231212 1 1 68 1946 -8 1 1097.49
68049647 -8 68238012 1 1 57 1957 -8 3 5500
68049651 -8 68238012 2 2 55 1959 -8 3 2000
68051007 -8 68251612 1 1 54 1960 -8 2 5291.67
68051011 -8 68251612 2 2 47 1967 -8 2 3426.67
68056447 -8 68265212 1 1 52 1962 -8 4 1282.09
68056451 -8 68265212 2 2 50 1964 -8 4 1560.54
68056455 -8 68265892 1 2 20 1994 -8 1 444.17
68058485 10628126 73820812 1 1 70 1944 -8 2 1462.27
68058487 -8 68272012 1 1 75 1939 -8 2 1213.95
68058489 10628169 73820812 2 2 70 1944 -8 2 264.33
68058491 -8 68272012 2 2 66 1949 -8 2 1021.25
68059171 -8 68285612 1 2 25 1989 -8 2 2663.56
68060525 10641556 73827612 1 1 89 1926 -8 3 1140.27
68060527 -8 68292412 1 1 41 1973 -8 3 3125
68060531 -8 68292412 2 2 42 1972 -8 3 2290.63
68060533 160066204 73827612 2 2 59 1955 -8 3 542.5
68060537 160066239 73827612 3 1 71 1943 -8 3 3007.34
68061288 -8 68047612 2 2 29 1986 -8 2 1451.67
68063247 -8 68306012 1 2 48 1966 -8 3 533
68063251 -8 68306012 2 1 50 1964 -8 3 503.47
68063927 -8 68312812 1 2 45 1970 -8 3 1100.58
68063931 -8 68312812 2 1 47 1967 -8 3 4088.8
68064605 10653872 73834412 1 1 66 1948 -8 2 1277.83
68064609 10653902 73834412 2 2 63 1951 -8 2 773.33
68065967 -8 68326412 1 1 66 1948 -8 2 895.83
68065971 -8 68326412 2 2 56 1959 -8 2 1900
68066647 -8 68333212 1 2 28 1986 -8 4 687.55
68066651 -8 68333212 2 1 29 1985 -8 4 2624.53
end
label values pid pid
label def pid -8 "inapplicable", modify
label values person_number person_number
label values gender gender
label def gender 1 "male", modify
label def gender 2 "female", modify
label values age age
label values dob dob
label values qualification qualification
label def qualification -8 "inapplicable", modify
label def qualification 9 "AS Level", modify
label def qualification 12 "GCSE / O Level", modify
label def qualification 17 "General Standard Grade (National 4 / Intermediate 1)", modify
label def qualification 96 "None of the above", modify
label values hhsize hhsize
label values monthly_income monthly_income
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 42168 observations
Use the count() option to list more

[Clyde Schechter]

Thank you. Your attempt at using -reshape- was almost correct. You just got -j()- wrong:

reshape wide pidp pid gender age dob qualification monthly_income, ///
    i(hidp) j(person_number)

does it.

That said, why do you want to do this? While there are a few things that are best, or only, done in wide layout, most data management and analysis in Stata are best done in, or require, long data layout. Unless you know for a fact that you will be doing things for which the wide layout is best suited, you should stick with the long layout you already have. For example, if you will need to do things like identify households that have, say, one or more female children, or have nobody born after 1975, or where the lowest monthly income in the household is at least some threshold value, these are complicated to do in wide layout and are very simple in long. So do think carefully before going wide.

[Lilly Tee]

Thanks for the help, Clyde.

I am doing this because I'm trying to link household data with children-level data. I'm looking at how family income affects children's health. I was also wondering when the reshape command, I could also include variables such as region, number of children in the household, etc.

[Clyde Schechter]

I am doing this because I'm trying to link household data with children-level data. I'm looking at how family income affects children's health.

I can pretty much guarantee you that you will need to use the long layout to do these things. Wide layout is going to make them difficult or impossible. I could say this with complete certainty if you showed an example from the children-level data set.

I was also wondering when the reshape command, I could also include variables such as region, number of children in the household, etc.

Yes. You can include these variables if they exist in your data set. But these sound like variables that do not vary by person but are attributes of the household as a whole. If I am right about that, including them in the -reshape wide- command would just result in making multiple copies of the exact same values, which is, at best pointless, and at worst will make your data set too large and unwieldy to work with. Such variables are best left alone when you -reshape wide-. The -reshape- command will retain those variables, but will not needlessly blow them up into a separate variable for each person.

[Lilly Tee]

I can pretty much guarantee you that you will need to use the long layout to do these things. Wide layout is going to make them difficult or impossible. I could say this with complete certainty if you showed an example from the children-level data set.

I understand, I am happy to share the children-level data set, which is the following:
clear
input long(pidp hidp) byte(person_number gender age) int child_dob byte(child_gender happy) long(parent_1pid parent_2pid)
68029939 68122412 4 1 11 2003 1 2 68029927 68029931
68041495 68170012 3 1 14 2000 1 1 68041487 68041491
68041499 68170012 4 2 12 2003 2 2 68041487 68041491
68044215 68190412 3 1 10 2005 1 1 68044207 68044211
68046935 68217612 2 2 15 2000 2 2 68046927 -8
68046939 68217612 3 1 11 2004 1 5 68046927 -8
68056459 68265212 4 2 15 1999 2 1 68056447 68056451
68056463 68265212 5 2 11 2003 2 1 68056447 68056451
68063255 68306012 3 1 15 1999 1 1 68063247 68063251
68063935 68312812 3 1 11 2003 1 2 68063927 68063931
68086375 68394412 2 2 11 2003 2 3 68086371 68414968
68116295 68503212 3 1 13 2001 1 4 68116287 68116291
68121059 68530412 4 2 15 1999 2 3 68121047 68121051
68142139 68618812 4 1 15 1999 1 7 68142127 68142131
68142143 68618812 5 1 11 2003 1 3 68142127 68142131
68144855 68625612 3 2 10 2005 2 2 68144847 68144851
68159135 68700412 3 2 10 2004 2 1 68159127 68159131
68180895 68782012 3 2 15 1999 2 2 68180887 68180891
68191775 68843212 2 1 12 2002 1 3 68191771 -8
68197219 68877212 4 2 14 2000 2 1 68197207 68197211
68197223 68877212 5 1 13 2001 1 1 68197207 68197211
68224415 69006412 4 2 11 2004 2 3 68224407 -8
68226455 69013212 3 2 12 2002 2 3 68226447 68226451
68228503 69026812 5 1 11 2004 1 2 68228487 68228491
68230539 69040412 4 1 11 2003 1 3 68230527 -8
68231227 69047212 4 2 15 1999 2 1 68231207 68231211
68244819 69101612 2 1 11 2003 1 3 68244807 -8
68247539 69108412 4 1 10 2004 1 3 68247527 68247531
68265219 69156012 4 2 10 2004 2 5 68265207 68265211
68295139 69292012 4 2 10 2004 2 3 68295127 68295131
68344771 69564012 2 2 10 2004 2 2 68344767 -8
68351575 69591212 3 2 11 2004 2 3 68351567 68351571
68367899 69652412 4 1 13 2002 1 5 68367887 68367891
68378115 69686412 8 2 15 2000 2 2 68378095 68378099
68412095 69788412 3 2 15 1999 2 2 68412087 68412091
68427055 69829212 2 1 10 2004 1 4 68427047 -8
68431135 69842812 3 1 10 2004 1 3 68431127 -8
68531095 70176012 2 1 12 2002 1 4 68531087 -8
68548095 70237212 3 1 14 2000 1 1 68548087 68548091
68548099 70237212 4 2 14 2000 2 3 68548087 68548091
68561015 70271212 3 1 13 2001 1 2 68561007 68561011
68580735 70373212 3 2 11 2003 2 2 68580727 68580731
68615419 70529612 4 1 15 1999 1 2 68615407 68615411
68624255 70577212 3 1 13 2001 1 3 68624247 68624251
68624259 70577212 4 1 10 2004 1 3 68624247 68624251
68680015 70828812 3 1 13 2002 1 2 68680007 68680011
68704491 70910412 2 1 15 1999 1 3 68704487 -8
68707215 70930812 3 1 14 2000 1 4 68707207 68707211
68707219 70930812 4 2 11 2003 2 4 68707207 68707211
68740539 71032812 4 2 14 2001 2 6 68740527 68740531
68794259 71277612 4 1 14 2000 1 2 68794247 68794251
68813979 71338812 4 1 14 2000 1 1 68813967 68813971
68813983 71338812 5 1 12 2002 1 2 68813967 68813971
68813987 71338812 6 2 11 2003 2 1 68813967 68813971
68841175 71400012 2 2 13 2001 2 2 68841167 -8
68867695 71434012 3 1 14 2000 1 1 68867687 -8
68895575 71481612 3 2 10 2004 2 2 68895571 -8
68901695 71488412 3 2 11 2003 2 2 68901687 68901691
68903735 71495212 3 2 15 1999 2 4 68903727 68903731
68903739 71495212 4 2 10 2004 2 4 68903727 68903731
68963575 71712812 3 1 10 2004 1 1 68963567 68963571
68964259 71719612 4 1 14 2000 1 1 68964247 68964251
68964263 71719612 5 1 14 2000 1 1 68964247 68964251
68993503 71821612 5 1 14 2000 1 3 68993487 68993491
68993507 71821612 6 2 13 2001 2 5 68993487 68993491
69029535 71910012 3 1 12 2002 1 2 69029527 -8
69051291 71984812 2 1 14 2001 1 2 69051287 -8
69053335 71991612 3 2 11 2003 2 2 69053327 69053331
69070343 72080012 3 1 11 2003 1 5 69070331 69070351
69074419 72134412 4 2 14 2000 2 2 69074407 69074411
69075095 72141212 3 2 11 2003 2 1 69075087 69075091
69078495 72154812 3 1 15 1999 1 1 69078487 69078491
69078499 72154812 4 2 12 2003 2 2 69078487 69078491
69083255 72195612 3 2 12 2002 2 2 69083247 69083251
69105015 72256812 3 2 14 2000 2 2 69105007 69105011
69105019 72256812 4 1 11 2003 1 5 69105007 69105011
69111135 72270412 3 2 13 2001 2 2 69111127 69111131
69132223 72358812 3 1 15 1999 1 3 69132207 69132211
69134935 72372412 3 1 15 1999 1 3 69134927 69134931
69134939 72372412 4 2 13 2002 2 2 69134927 69134931
69135619 72379212 3 2 15 1999 2 2 69135607 69135611
69135623 72379212 4 2 13 2001 2 2 69135607 69135611
69146495 72447212 3 1 15 2000 1 3 69146487 69146491
69183897 74378412 4 1 12 2002 1 1 69183885 69183889
69262775 72862012 3 1 13 2002 1 2 69262767 69262771
69298135 72970812 3 2 10 2004 2 2 69298127 69298131
69302215 72977612 3 1 13 2001 1 3 69302207 69302211
69305621 74398812 4 1 12 2002 1 2 69305605 69305609
69307655 73018412 2 1 12 2002 1 -9 69307647 73576888
69307659 73018412 3 2 10 2004 2 3 69307647 73576888
69325335 73072812 3 2 12 2002 2 1 69325327 69325331
69325339 73072812 4 1 11 2003 1 2 69325327 69325331
69389255 73127212 2 2 10 2004 2 2 69389247 73698648
69431415 73202012 3 2 12 2002 2 2 69431407 69431411
69431419 73202012 4 2 11 2003 2 2 69431407 69431411
69440951 73208812 6 1 14 2000 1 2 69440927 -8
69451135 73222412 3 1 10 2005 1 4 69451127 69451131
69544293 74521212 3 1 15 1999 1 2 69544285 69544289
69544297 74521212 4 2 14 2001 2 2 69544285 69544289
69603453 74555212 3 1 13 2001 1 4 69603445 69603449
end
label values person_number person_number
label values gender gender
label def gender 1 "male", modify
label def gender 2 "female", modify
label values age age
label values child_dob child_dob
label values child_gender child_gender
label def child_gender 1 "Male", modify
label def child_gender 2 "Female", modify
label values happy happy
label def happy -9 "missing", modify
label def happy 1 "1", modify
label def happy 2 "2", modify
label def happy 3 "3", modify
label def happy 4 "4", modify
label def happy 5 "5", modify
label def happy 6 "6", modify
label def happy 7 "7", modify
label values parent_1pid parent_1pid
label values parent_2pid parent_2pid
label def parent_2pid -8 "inapplicable", modify
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 3629 observations
Use the count() option to list more

as also wondering when the reshape command, I could also include variables such as region, number of children in the household, etc.

I see what you're saying. So could I still have these variables within the dataset but wouldn't I include it in the reshape command?

[Clyde Schechter]

Yes, you definitely should leave the data long to combine these data sets.

use household_data_set, clear
merge 1:1 hidp person_number using children_data_set

You will then be in the right set-up to look at things like relationships between household income and child happiness. (I don't see any health variables here, but perhaps you just didn't include any in your example.)

So could I still have these variables within the dataset but wouldn't I include it in the reshape command?

Yes, just don't mention them. -reshape- will keep them in the data set exactly as you have them. If you mention them in the -reshape- command then Stata will create one new variable for each household person, all of them having exactly the same values, which is pointless and just clutters up your data set. Of course, I think this is all moot, because it is becoming much clearer now that you should not -reshape wide- in the first place.

[Lilly Tee]

Thank you for the code. I will try it out.

Yes, just don't mention them. -reshape- will keep them in the data set exactly as you have them. If you mention them in the -reshape- command then Stata will create one new variable for each household person, all of them having exactly the same values, which is pointless and just clutters up your data set. Of course, I think this is all moot, because it is becoming much clearer now that you should not -reshape wide- in the first place.

I tried the reshape command you suggested in the previous post and this is with more variables within the dataset but not including it in the reshape command. An error message appears stating variable child_under16 is not constant within hidp. How would I then resolve for this

[Clyde Schechter]

An error message appears stating variable child_under16 is not constant within hidp.

If this variable means that it is an indicator (yes/no) for whether or not the household has any child under 16, then you have a data error, since this should be the same for all observations of the hidp. You will need to get the data error fixed in order to proceed. If, however, the variable indicates that this particular person is a child under 16, then it is a personal, not household level variable. All personal level variables have to be included in the variable list of -reshape wide-. Household-level variables should not be included in that list.

But again, I think you are making a mistake in pursuing the -reshape-. You will be back here soon enough asking for more help with your analyses, and the answer to your query will begin by going back to long layout.

[Lilly Tee]

Okay thank you again, its starting to make sense. Regarding the merge code, once I merge the household data and child data together there should be matched data however once I do that. The number of matches is 0 which doesn't seem correct

[Clyde Schechter]

Regarding the merge code, once I merge the household data and child data together there should be matched data however once I do that. The number of matches is 0 which doesn't seem correct.

That definitely is not right. In fact, using the example household and children data sets that you have posted in this thread, when I merge them 1:1 I get 100% matches. So you are doing something wrong, but I don't know what that might be. If you are using the household data after you -reshape- it to wide, then you wouldn't get any matches--but you also would get an error message, because after going wide there would be no person_number variable any more.

Please post back showing the exact commands you are using to merge these data sets and also show the complete and exact output that Stata gives you. To be sure you get it complete and exact, it is best to copy/paste it from the Results window or your log file directly into your post.

[Lilly Tee]

code:
use household_data_set, clear
merge 1:1 hidp person_number using children_data_set
(label person_number already defined)
(label gender already defined)
(label age already defined)

Result Number of obs
-----------------------------------------
Not matched 45,797
from master 42,168 (_merge==1)
from using 3,629 (_merge==2)

Matched 0 (_merge==3)
-----------------------------------------

[Clyde Schechter]

I'm sorry, but I have no explanation for why you are getting this result.

Am I correct in assuming that the examples from these data sets that you showed in #3 and #7 were taken directly from the household and children data sets respectively, using -dataex- and that you made no modifications to those data. Can I assume that the exact data you show in #3 and #7 actually appear in your household and children data sets, respectively? If so, if nothing else, the data in those two examples should match--and they do when I run the same commands. I have to imagine that between the time you posted in #3 and #7 you have done something additional to one or both of these data sets that has interfered with the match-up between them.

[Lilly Tee]

yes using
clear
input long(pidp pid hidp) byte(person_number gender) int(age dob) byte(qualification hhsize) float monthly_income
22445 10127798 278447092 1 2 30 1984 -8 1 2856.67
29925 10192697 620316412 1 2 38 1977 -8 3 2089
76165 10689869 142378412 1 2 32 1982 -8 2 2704.17
223725 11926929 619140012 3 1 40 1975 -8 3 1999.06
280165 12430439 755847212 1 2 36 1979 -8 4 3089.7
333205 12908843 416466012 3 2 25 1990 -8 3 2005.83
387605 13361163 347194412 3 2 27 1988 -8 3 896.18
813285 16441141 550902012 3 1 45 1970 -8 3 1332.54
850005 16714296 211922012 2 1 26 1988 -8 2 1213.33
1587125 17870879 619493612 2 2 50 1965 -8 2 4666.67
1833965 50832336 754494012 1 1 50 1965 -8 2 2604.17
2067205 65313828 74596012 3 1 26 1988 -8 3 501.74
2270525 76446336 824894412 3 2 18 1997 9 4 822.03
2853965 96577029 688642812 1 2 33 1982 -8 2 2941.67
3568645 118692895 757669612 5 1 20 1995 96 7 0
3587685 118781707 485146012 3 1 34 1980 -8 3 1300.04
3663845 119065835 417934812 6 1 31 1983 -8 6 216.67
3667245 119074613 281764812 3 2 27 1988 -8 3 2025
3705325 119277506 145914412 2 2 61 1953 -8 2 0
4091565 135447429 484425212 3 1 37 1978 -8 3 2666.67
4454005 154358304 484092012 2 1 70 1944 -8 2 1131.04
4473725 154588539 416642812 2 1 59 1955 -8 2 5031.85
4849085 176725733 349050812 2 1 32 1982 -8 2 3573.67
4853165 176977635 825588012 4 1 45 1970 17 4 2200
68002049 10017992 73793612 2 2 35 1979 -8 3 2333.33
68002725 10023526 73800412 1 2 61 1953 -8 1 1171.43
68004087 -8 68013612 1 1 65 1949 -8 1 1358.33
68006127 -8 68020412 1 2 45 1969 -8 4 385
68006135 -8 68020412 3 2 23 1991 -8 4 1012.88
68006139 -8 68020412 2 2 20 1994 -8 4 0
68006807 -8 68027212 1 2 78 1936 -8 1 585
68008847 -8 68040812 1 2 57 1957 -8 1 1971
68009527 -8 68047612 1 1 38 1977 -8 2 2450
68010887 -8 68054412 1 2 51 1963 -8 2 2300
68014287 -8 68068012 1 2 45 1969 -8 3 1304.56
68014291 -8 68074812 1 2 22 1993 -8 2 1300
68017687 -8 68081612 1 2 36 1979 -8 3 836.55
68020407 -8 68095212 1 2 78 1936 -8 2 1178.66
68020564 -8 68020412 4 1 45 1970 12 4 223.17
68021765 10200436 73807212 1 2 56 1958 -8 4 1832.07
68021769 10200479 73807212 2 1 54 1960 -8 4 7712.41
68021773 10200509 73807212 3 2 28 1987 -8 4 2166.67
68021781 30139368 73807212 4 1 21 1993 -8 4 1701.47
68025847 -8 68102012 1 2 79 1935 -8 1 1881.16
68028567 -8 68108812 1 2 43 1971 -8 4 775.89
68028571 -8 68108812 2 1 48 1966 -8 4 150.67
68028575 -8 68115612 1 2 24 1991 -8 2 0
68029927 -8 68122412 1 2 43 1972 -8 4 88.8
68029931 -8 68122412 2 1 46 1969 -8 4 2264.79
68029935 -8 68122412 3 2 18 1996 -8 4 83.33
68031967 -8 68129212 1 2 67 1947 -8 1 1701.6
68032647 -8 68136012 1 1 30 1984 -8 3 2730
68035365 10403086 73814012 1 1 63 1951 -8 1 3175
68035367 -8 68142812 1 1 34 1980 -8 2 6075
68036727 -8 68149612 1 1 83 1931 -8 1 1062
68037407 -8 68156412 1 2 46 1968 -8 2 1454
68037411 -8 68156412 2 2 17 1998 -8 2 0
68041487 -8 68170012 1 2 45 1969 -8 4 3480.88
68041491 -8 68170012 2 1 42 1973 -8 4 3600
68042167 -8 68176812 1 1 45 1970 -8 3 3988.13
68042171 -8 68176812 2 2 44 1970 -8 3 3372
68043527 -8 68183612 1 1 61 1953 -8 2 3038.57
68044207 -8 68190412 1 2 39 1975 -8 5 1428.77
68044211 -8 68190412 2 1 42 1973 -8 5 132.3
68044887 -8 68197212 1 2 68 1946 -8 1 1157
68045567 -8 68204012 1 2 53 1961 -8 3 3004.17
68045571 -8 68204012 2 1 55 1959 -8 3 6458.33
68045575 -8 68204012 3 1 18 1996 -8 3 430
68046247 -8 68210812 1 1 72 1942 -8 2 2036.67
68046251 -8 68210812 2 2 70 1945 -8 2 568.66
68046927 -8 68217612 1 2 42 1972 -8 3 3732
68048287 -8 68231212 1 1 68 1946 -8 1 1097.49
68049647 -8 68238012 1 1 57 1957 -8 3 5500
68049651 -8 68238012 2 2 55 1959 -8 3 2000
68051007 -8 68251612 1 1 54 1960 -8 2 5291.67
68051011 -8 68251612 2 2 47 1967 -8 2 3426.67
68056447 -8 68265212 1 1 52 1962 -8 4 1282.09
68056451 -8 68265212 2 2 50 1964 -8 4 1560.54
68056455 -8 68265892 1 2 20 1994 -8 1 444.17
68058485 10628126 73820812 1 1 70 1944 -8 2 1462.27
68058487 -8 68272012 1 1 75 1939 -8 2 1213.95
68058489 10628169 73820812 2 2 70 1944 -8 2 264.33
68058491 -8 68272012 2 2 66 1949 -8 2 1021.25
68059171 -8 68285612 1 2 25 1989 -8 2 2663.56
68060525 10641556 73827612 1 1 89 1926 -8 3 1140.27
68060527 -8 68292412 1 1 41 1973 -8 3 3125
68060531 -8 68292412 2 2 42 1972 -8 3 2290.63
68060533 160066204 73827612 2 2 59 1955 -8 3 542.5
68060537 160066239 73827612 3 1 71 1943 -8 3 3007.34
68061288 -8 68047612 2 2 29 1986 -8 2 1451.67
68063247 -8 68306012 1 2 48 1966 -8 3 533
68063251 -8 68306012 2 1 50 1964 -8 3 503.47
68063927 -8 68312812 1 2 45 1970 -8 3 1100.58
68063931 -8 68312812 2 1 47 1967 -8 3 4088.8
68064605 10653872 73834412 1 1 66 1948 -8 2 1277.83
68064609 10653902 73834412 2 2 63 1951 -8 2 773.33
68065967 -8 68326412 1 1 66 1948 -8 2 895.83
68065971 -8 68326412 2 2 56 1959 -8 2 1900
68066647 -8 68333212 1 2 28 1986 -8 4 687.55
68066651 -8 68333212 2 1 29 1985 -8 4 2624.53
end
label values pid pid
label def pid -8 "inapplicable", modify
label values person_number person_number
label values gender gender
label def gender 1 "male", modify
label def gender 2 "female", modify
label values age age
label values dob dob
label values qualification g_qfhigh
label def qualification -8 "inapplicable", modify
label def qualification 9 "AS Level", modify
label def qualification 12 "GCSE / O Level", modify
label def qualification 17 "General Standard Grade (National 4 / Intermediate 1)", modify
label def qualification 96 "None of the above", modify
label values hhsize hhsize
label values monthly_income monthly_income
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 42168 observations
Use the count() option to list more


and

clear
input long(pidp hidp) byte(person_number gender age) int child_dob byte(child_gender happy) long(parent_1pid parent_2pid)
68029939 68122412 4 1 11 2003 1 2 68029927 68029931
68041495 68170012 3 1 14 2000 1 1 68041487 68041491
68041499 68170012 4 2 12 2003 2 2 68041487 68041491
68044215 68190412 3 1 10 2005 1 1 68044207 68044211
68046935 68217612 2 2 15 2000 2 2 68046927 -8
68046939 68217612 3 1 11 2004 1 5 68046927 -8
68056459 68265212 4 2 15 1999 2 1 68056447 68056451
68056463 68265212 5 2 11 2003 2 1 68056447 68056451
68063255 68306012 3 1 15 1999 1 1 68063247 68063251
68063935 68312812 3 1 11 2003 1 2 68063927 68063931
68086375 68394412 2 2 11 2003 2 3 68086371 68414968
68116295 68503212 3 1 13 2001 1 4 68116287 68116291
68121059 68530412 4 2 15 1999 2 3 68121047 68121051
68142139 68618812 4 1 15 1999 1 7 68142127 68142131
68142143 68618812 5 1 11 2003 1 3 68142127 68142131
68144855 68625612 3 2 10 2005 2 2 68144847 68144851
68159135 68700412 3 2 10 2004 2 1 68159127 68159131
68180895 68782012 3 2 15 1999 2 2 68180887 68180891
68191775 68843212 2 1 12 2002 1 3 68191771 -8
68197219 68877212 4 2 14 2000 2 1 68197207 68197211
68197223 68877212 5 1 13 2001 1 1 68197207 68197211
68224415 69006412 4 2 11 2004 2 3 68224407 -8
68226455 69013212 3 2 12 2002 2 3 68226447 68226451
68228503 69026812 5 1 11 2004 1 2 68228487 68228491
68230539 69040412 4 1 11 2003 1 3 68230527 -8
68231227 69047212 4 2 15 1999 2 1 68231207 68231211
68244819 69101612 2 1 11 2003 1 3 68244807 -8
68247539 69108412 4 1 10 2004 1 3 68247527 68247531
68265219 69156012 4 2 10 2004 2 5 68265207 68265211
68295139 69292012 4 2 10 2004 2 3 68295127 68295131
68344771 69564012 2 2 10 2004 2 2 68344767 -8
68351575 69591212 3 2 11 2004 2 3 68351567 68351571
68367899 69652412 4 1 13 2002 1 5 68367887 68367891
68378115 69686412 8 2 15 2000 2 2 68378095 68378099
68412095 69788412 3 2 15 1999 2 2 68412087 68412091
68427055 69829212 2 1 10 2004 1 4 68427047 -8
68431135 69842812 3 1 10 2004 1 3 68431127 -8
68531095 70176012 2 1 12 2002 1 4 68531087 -8
68548095 70237212 3 1 14 2000 1 1 68548087 68548091
68548099 70237212 4 2 14 2000 2 3 68548087 68548091
68561015 70271212 3 1 13 2001 1 2 68561007 68561011
68580735 70373212 3 2 11 2003 2 2 68580727 68580731
68615419 70529612 4 1 15 1999 1 2 68615407 68615411
68624255 70577212 3 1 13 2001 1 3 68624247 68624251
68624259 70577212 4 1 10 2004 1 3 68624247 68624251
68680015 70828812 3 1 13 2002 1 2 68680007 68680011
68704491 70910412 2 1 15 1999 1 3 68704487 -8
68707215 70930812 3 1 14 2000 1 4 68707207 68707211
68707219 70930812 4 2 11 2003 2 4 68707207 68707211
68740539 71032812 4 2 14 2001 2 6 68740527 68740531
68794259 71277612 4 1 14 2000 1 2 68794247 68794251
68813979 71338812 4 1 14 2000 1 1 68813967 68813971
68813983 71338812 5 1 12 2002 1 2 68813967 68813971
68813987 71338812 6 2 11 2003 2 1 68813967 68813971
68841175 71400012 2 2 13 2001 2 2 68841167 -8
68867695 71434012 3 1 14 2000 1 1 68867687 -8
68895575 71481612 3 2 10 2004 2 2 68895571 -8
68901695 71488412 3 2 11 2003 2 2 68901687 68901691
68903735 71495212 3 2 15 1999 2 4 68903727 68903731
68903739 71495212 4 2 10 2004 2 4 68903727 68903731
68963575 71712812 3 1 10 2004 1 1 68963567 68963571
68964259 71719612 4 1 14 2000 1 1 68964247 68964251
68964263 71719612 5 1 14 2000 1 1 68964247 68964251
68993503 71821612 5 1 14 2000 1 3 68993487 68993491
68993507 71821612 6 2 13 2001 2 5 68993487 68993491
69029535 71910012 3 1 12 2002 1 2 69029527 -8
69051291 71984812 2 1 14 2001 1 2 69051287 -8
69053335 71991612 3 2 11 2003 2 2 69053327 69053331
69070343 72080012 3 1 11 2003 1 5 69070331 69070351
69074419 72134412 4 2 14 2000 2 2 69074407 69074411
69075095 72141212 3 2 11 2003 2 1 69075087 69075091
69078495 72154812 3 1 15 1999 1 1 69078487 69078491
69078499 72154812 4 2 12 2003 2 2 69078487 69078491
69083255 72195612 3 2 12 2002 2 2 69083247 69083251
69105015 72256812 3 2 14 2000 2 2 69105007 69105011
69105019 72256812 4 1 11 2003 1 5 69105007 69105011
69111135 72270412 3 2 13 2001 2 2 69111127 69111131
69132223 72358812 3 1 15 1999 1 3 69132207 69132211
69134935 72372412 3 1 15 1999 1 3 69134927 69134931
69134939 72372412 4 2 13 2002 2 2 69134927 69134931
69135619 72379212 3 2 15 1999 2 2 69135607 69135611
69135623 72379212 4 2 13 2001 2 2 69135607 69135611
69146495 72447212 3 1 15 2000 1 3 69146487 69146491
69183897 74378412 4 1 12 2002 1 1 69183885 69183889
69262775 72862012 3 1 13 2002 1 2 69262767 69262771
69298135 72970812 3 2 10 2004 2 2 69298127 69298131
69302215 72977612 3 1 13 2001 1 3 69302207 69302211
69305621 74398812 4 1 12 2002 1 2 69305605 69305609
69307655 73018412 2 1 12 2002 1 -9 69307647 73576888
69307659 73018412 3 2 10 2004 2 3 69307647 73576888
69325335 73072812 3 2 12 2002 2 1 69325327 69325331
69325339 73072812 4 1 11 2003 1 2 69325327 69325331
69389255 73127212 2 2 10 2004 2 2 69389247 73698648
69431415 73202012 3 2 12 2002 2 2 69431407 69431411
69431419 73202012 4 2 11 2003 2 2 69431407 69431411
69440951 73208812 6 1 14 2000 1 2 69440927 -8
69451135 73222412 3 1 10 2005 1 4 69451127 69451131
69544293 74521212 3 1 15 1999 1 2 69544285 69544289
69544297 74521212 4 2 14 2001 2 2 69544285 69544289
69603453 74555212 3 1 13 2001 1 4 69603445 69603449
end
label values person_number person_number
label values gender gender
label def gender 1 "male", modify
label def gender 2 "female", modify
label values age age
label values child_dob child_dob
label values child_gender child_gender
label def child_gender 1 "Male", modify
label def child_gender 2 "Female", modify
label values happy happy
label def happy -9 "missing", modify
label def happy 1 "1", modify
label def happy 2 "2", modify
label def happy 3 "3", modify
label def happy 4 "4", modify
label def happy 5 "5", modify
label def happy 6 "6", modify
label def happy 7 "7", modify
label values parent_1pid parent_1pid
label values parent_2pid parent_2pid
label def parent_2pid -8 "inapplicable", modify
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 3629 observations
Use the count() option to list more

I got the following now


Result Number of obs
-----------------------------------------
Not matched 0
Matched 3,629 (_merge==3)
-----------------------------------------