[Rajdeep Chaudhuri]

Thanks Clyde for the reply.

Well, if the child has two mothers, how do you decide which mother's education will be used for that variable? After all, it would only be an occasional coincidence if both mothers have the same level of education. Of perhaps you want the average of the mothers' education levels? Or the highest, or the lowest? Or something else?

I want both(or more) of the mother(or father) education. So for example childid 101001601100105 has two fathers 101001601100101 and 101001601100103, then the education for 101001601100101 would be father1education and education for 101001601100103 should be father2education.

So does this mean that a child with multiple mothers or fathers will appear multiple times in the data set, with those records having the same hhid but new values for prim_key?

Yes, they will appear multiple times in the data set. The way that the data is structured, the childid will occur twice, once for the father and once for the mother(not necessarily though, since it is possible that a child does not have a mother or a father), and if it occurs anymore than twice, that means there is a chance that there are two fathers(or mothers).

The example dataset here is only with such instances( childid occurs more than twice). The original dataset also has the childid occurring once or twice.

* Example generated by -dataex-. For more info, type help dataex
clear
input str15(prim_key hhid childid) byte(dm003 dm008)
"101001601100101" "101001601100100" "101001601100105" 1 .
"101001601100103" "101001601100100" "101001601100105" 1 3
"101001601100104" "101001601100100" "101001601100105" 2 2
"104021500110101" "104021500110100" "104021500110103" 1 2
"104021500110102" "104021500110100" "104021500110103" 2 .
"104021500110108" "104021500110100" "104021500110103" 2 .
"106030701630101" "106030701630100" "106030701630103" 1 8
"106030701630102" "106030701630100" "106030701630103" 2 .
"106030701630105" "106030701630100" "106030701630103" 1 4
"107037300070102" "107037300070100" "107037300070105" 2 .
"107037300070103" "107037300070100" "107037300070105" 1 3
"107037300070104" "107037300070100" "107037300070105" 2 4
"109047303220101" "109047303220100" "109047303220108" 1 .
"109047303220101" "109047303220100" "109047303220105" 1 .
"109047303220101" "109047303220100" "109047303220107" 1 .
"109047303220101" "109047303220100" "109047303220106" 1 .
"109047303220102" "109047303220100" "109047303220105" 2 .
"109047303220102" "109047303220100" "109047303220106" 2 .
"109047303220102" "109047303220100" "109047303220108" 2 .
"109047303220102" "109047303220100" "109047303220107" 2 .
"109047303220104" "109047303220100" "109047303220108" 2 .
"109047303220104" "109047303220100" "109047303220107" 2 .
"109047303220104" "109047303220100" "109047303220106" 2 .
"109047303220104" "109047303220100" "109047303220105" 2 .
"109047701390101" "109047701390100" "109047701390105" 1 4
"109047701390101" "109047701390100" "109047701390106" 1 4
"109047701390101" "109047701390100" "109047701390107" 1 4
"109047701390102" "109047701390100" "109047701390105" 2 .
"109047701390103" "109047701390100" "109047701390105" 1 7
"109047701390103" "109047701390100" "109047701390106" 1 7
"109047701390103" "109047701390100" "109047701390107" 1 7
"109047701390104" "109047701390100" "109047701390106" 2 5
"109047701390104" "109047701390100" "109047701390107" 2 5
"109047701390104" "109047701390100" "109047701390105" 2 5
"109050902450101" "109050902450100" "109050902450103" 1 .
"109050902450101" "109050902450100" "109050902450107" 1 .
"109050902450101" "109050902450100" "109050902450108" 1 .
"109050902450101" "109050902450100" "109050902450105" 1 .
"109050902450101" "109050902450100" "109050902450104" 1 .
"109050902450101" "109050902450100" "109050902450106" 1 .
"109050902450102" "109050902450100" "109050902450108" 2 .
"109050902450102" "109050902450100" "109050902450106" 2 .
"109050902450102" "109050902450100" "109050902450105" 2 .
"109050902450102" "109050902450100" "109050902450104" 2 .
"109050902450102" "109050902450100" "109050902450107" 2 .
"109050902450102" "109050902450100" "109050902450103" 2 .
"109050902450109" "109050902450100" "109050902450105" 2 .
"109050902450109" "109050902450100" "109050902450106" 2 .
"109050902450109" "109050902450100" "109050902450103" 2 .
"109050902450109" "109050902450100" "109050902450108" 2 .
"109050902450109" "109050902450100" "109050902450104" 2 .
"109050902450109" "109050902450100" "109050902450107" 2 .
"109053706630101" "109053706630100" "109053706630103" 1 1
"109053706630102" "109053706630100" "109053706630103" 1 4
"109053706630103" "109053706630100" "109053706630103" 2 .
"111075001050101" "111075001050100" "111075001050109" 2 .
"111075001050102" "111075001050100" "111075001050109" 1 7
"111075001050103" "111075001050100" "111075001050109" 2 2
"111076401910101" "111076401910100" "111076401910104" 1 2
"111076401910101" "111076401910100" "111076401910106" 1 2
"111076401910102" "111076401910100" "111076401910104" 2 .
"111076401910102" "111076401910100" "111076401910106" 2 .
"111076401910103" "111076401910100" "111076401910104" 2 .
"111076401910103" "111076401910100" "111076401910106" 2 .
"112079500360101" "112079500360100" "112079500360107" 1 7
"112079500360101" "112079500360100" "112079500360106" 1 7
"112079500360101" "112079500360100" "112079500360105" 1 7
"112079500360101" "112079500360100" "112079500360104" 1 7
"112079500360102" "112079500360100" "112079500360106" 2 2
"112079500360102" "112079500360100" "112079500360104" 2 2
"112079500360102" "112079500360100" "112079500360105" 2 2
"112079500360102" "112079500360100" "112079500360107" 2 2
"112079500360103" "112079500360100" "112079500360104" 2 3
"112079500360103" "112079500360100" "112079500360107" 2 3
"112079500360103" "112079500360100" "112079500360105" 2 3
"112079500360103" "112079500360100" "112079500360106" 2 3
"112079600760101" "112079600760100" "112079600760107" 1 .
"112079600760101" "112079600760100" "112079600760108" 1 .
"112079600760101" "112079600760100" "112079600760104" 1 .
"112079600760101" "112079600760100" "112079600760106" 1 .
"112079600760102" "112079600760100" "112079600760106" 2 .
"112079600760102" "112079600760100" "112079600760107" 2 .
"112079600760102" "112079600760100" "112079600760108" 2 .
"112079600760102" "112079600760100" "112079600760104" 2 .
"112079600760103" "112079600760100" "112079600760104" 1 .
"112079600760103" "112079600760100" "112079600760107" 1 .
"112079600760103" "112079600760100" "112079600760108" 1 .
"112079600760103" "112079600760100" "112079600760106" 1 .
"112079600910101" "112079600910100" "112079600910104" 1 .
"112079600910102" "112079600910100" "112079600910104" 2 .
"112079600910103" "112079600910100" "112079600910104" 2 2
"112081300170101" "112081300170100" "112081300170104" 1 .
"112081300170101" "112081300170100" "112081300170105" 1 .
"112081300170101" "112081300170100" "112081300170106" 1 .
"112081300170102" "112081300170100" "112081300170105" 2 .
"112081300170102" "112081300170100" "112081300170104" 2 .
"112081300170102" "112081300170100" "112081300170106" 2 .
"112081300170103" "112081300170100" "112081300170105" 2 .
"112081300170103" "112081300170100" "112081300170104" 2 .
"112081300170103" "112081300170100" "112081300170106" 2 .
end
label values dm003 rsex_ind
label def rsex_ind 1 "1 Male", modify
label def rsex_ind 2 "2 Female", modify
label values dm008 _vl6024_ind
label def _vl6024_ind 1 "1 Less than Primary (Standard 1-4)", modify
label def _vl6024_ind 2 "2 Primary Completed (Standard 5-7)", modify
label def _vl6024_ind 3 "3 Middle Completed (Standard 8- 9)", modify
label def _vl6024_ind 4 "4 Secondary School/Matriculation completed", modify
label def _vl6024_ind 5 "5 Higher Secondary/Intermediate/Senior Secondary completed", modify
label def _vl6024_ind 7 "7 Graduate degree (B.A., B.Sc., B. Com.) completed", modify
label def _vl6024_ind 8 "8 Post-graduate degree or (M.A., M.Sc., M. Com.) above (M.Phil, Ph.D., Post-Doc) completed", modify

[Clyde Schechter]

OK, this is a somewhat complicated transformation of the data. But it can be accomplished as:

rename dm003 sex
rename dm008 education

// CREATE DATA SET OF PARENTS
frame put prim_key hhid sex education, into(parents)
    frame parents {
    duplicates drop
    gen parent = "father" if sex == 1
    replace parent = "mother" if sex == 2
    drop sex
    by hhid parent (prim_key education), sort: gen seq = _n
    egen specifier = concat(parent seq), punct(_)
    drop parent seq prim_key
    reshape wide education, i(hhid) j(specifier) string
    rename education*_* *[1]_education*[2]
    isid hhid, sort
}



//    NOW KEEP ONLY THE CHILD-LEVEL VARIABLES FROM THE ORIGINAL DATA
//    AND REDUCE TO ONE RECORD PER CHILD
keep hhid childid cv*
duplicates drop
isid hhid childid, sort  

//    JOIN THE TWO DATA SETS
frlink m:1 hhid, frame(parents)
frget *education*, from(parents)

Added: I wanted to verify that in making the code work for multiple parents, it still worked correctly for the original data. So the data set I developed this one was created by appending the data in #16 to the data provided for the previous solution. The latter contained cv* variables, which are all child-level, but the former does not. Since I make reference to the cv* variables in the current code, it will not run if given only the example data in #16. So, when using this code, be sure you start with a data set that includes the cv* variables (or, remove cv* from the -keep hhid childid cv*- command).