Dear All,

How can I select and create a new data set with all household heads and those household with no household head to select the spouse and if there is no spouse then to select the oldest members.

Please find below an example of my data set. For example in HHNO 1 I have head so in the new data set I want to keep only head. In HHNO 2 There is no head but there is spouse so I want to keep Spouse and in HHNO 4 there is no head and spouse so I want to keep the oldest hh member in HHNO 4.

Can you all help me.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id1 int hhno byte relationship int age
1  1  1 40
1  1  2 38
1  1  3  9
1  1  4  7
1  2  4 38
1  2  2 34
1  2  6 74
1  2  7 34
1  2  4 10
1  2  4  5
1  3  1 61
1  4  4 65
1  4  6 90
1  4 21 78
1  4  4 63
1  4  3 37
1  4  4 27
1  4  4 21
1  4 13  8
1  4  4 30
1  4  3 28
1  5  4 43
1  5  2 42
1  5  4 24
1  5  4  8
1  5  8 48
1  6  1 43
1  6  2 36
1  6  6 66
1  6  4 11
1  6  3  1
1  6  7 32
1  6  3 20
1  7  1 52
1  7  2 62
1  7  3 28
1  7  3 10
1  7 16 25
1  7 14  5
1  8  1 36
1  8  6 65
1  8  4 12
1  8  7 42
1  8  7 33
1  9  1 26
1 10  1 71
1 10 21 83
1 10 13 20
1 10 13 23
1 11  1 48
1 11  2 55
1 11  3 26
1 11  4 22
1 11  4 21
1 11 14  1
1 11 13  0
1 11 16 26
1 11 14  1
1 12  1 48
1 12  5 78
1 12  7 40
1 12  3  8
1 12  2 47
1 13  1 35
1 13  6 55
1 13  4  0
1 14  1 25
1 14  2 27
1 14 10 13
1 14  3  5
1 14  8 31
1 15  1 39
1 15  2 35
1 15  3  5
1 15  4  2
1 15  4  2
1 16  1 38
1 16  2 32
1 16  3 12
1 16  3  6
1 17  1 31
1 17  2 27
1 17 18 64
1 17  3  1
1 18  1 39
1 18  2 38
1 19  1 57
1 19  3 29
1 19  3 25
1 19  3 21
1  2  1 56
1  2  2 59
1  3  0 44
1  3  0 43
1  3  0 48
1  3  0 35
1  3  0 37
1  3  0 41
1  3  0 32
1  3  0 28
end
label values id1 ID1
label def ID1 1 "Bumthang", modify
label values relationship HL3
label def HL3 1 "Head", modify
label def HL3 2 "Spouse", modify
label def HL3 3 "Son", modify
label def HL3 4 "Daughter", modify
label def HL3 5 "Father", modify
label def HL3 6 "Mother", modify
label def HL3 7 "Brother", modify
label def HL3 8 "Sister", modify
label def HL3 10 "Nephew", modify
label def HL3 13 "Grandson", modify
label def HL3 14 "Grand daughter", modify
label def HL3 16 "Daughter-in-law", modify
label def HL3 18 "Mother-in-law", modify
label def HL3 21 "Uncle", modify
label values age age
------------------ copy up to and including the previous line ------------------