clear all
sysdir
adopath
cd "D:\Data for paper"   

/*==================================================
              1: child database
==================================================*/
use "D:\Data for paper\2018\cfps2018person_202012_个人库.dta",clear

rename pid_a_f pid_f
rename pid_a_m pid_m

bys pid: egen num = count(pid) 
tab num 

tab pid_m if pid_m<1000,nol
bys pid_m: egen num2 = count(pid_m) 
tab num2 

//gender
tab gender 

//1. whether the adolescent usually turns to parents when distressed
tab qk5  
tab age qk5 
tabstat qk5,stat(mean n) by(qk5)  
gen e_5=1 if qk5==2
replace e_5=0 if (qk5!=2 & qk5>0 & qk5!=.)  
tab e_5
tab gender,sum(e_5)

egen e_5m=mean(e_5), by(pid_m)
tab e_5m

//2.age
tab age
replace age=. if age<0
replace age=cyear-ibirthy_update if age==.
keep if age>9 & age<16 

//3.Emotional state
tab qn406 
tabstat qn406,stat(mean n) by(qn406)
gen emotion_1=qn406 
replace emotion_1=. if emotion_1<0
tab emotion_1
label var emotion_1 "Frequency of feeling depressed over the past week; 1 = rarely, 4 = most of the time (5–7 days)"
tab gender, sum(emotion_1)

egen emotion_1m=mean(emotion_1), by(pid_m)
tab emotion_1m

keep pid fid18 fid18 fid14 fid12 fid10 pid_f pid_m gender  qk5 e_5 qn406 emotion_1 age  e_5m emotion_1m 
rename pid pid_c 
save 2018children.dta ,replace

/*==================================================
             2: adult database
==================================================*/
use "D:\Data for paper\2018\cfps2018person_202012_个人库.dta", clear

tab gender
tabstat gender,stat(mean n) by(gender)

keep pid fid18 fid18 fid14 fid12 fid10 provcd18 pid_a_f pid_a_m urban18 gender age cfps2018edu qg14 income qg5 qg6 qn406 qm2011 qp201 qu201 qu202 qq9010 qc1 qgb6 employ jobclass qea202 qea0 qg406

//2.age
tab age
tabstat age,stat(mean n) by(age)
replace age=. if age<0
summ ibirthy ibirthy_update qa001m
replace age=cyear-ibirthy_update if age==. & inrange(ibirthy_update,1918,2009)
sum age
tab gender,sum(age)

//3.Age squared / 100
gen age2=age^2/100

//4.Education attainment
summ w01 cfps2018edu
tab cfps2018edu
tabstat cfps2018edu,stat(mean n) by(cfps2018edu)
gen edu=cfps2018edu
tab w01
tabstat w01,stat(mean n) by(w01)
gen nedu=w01
replace nedu=. if nedu<0
replace nedu=nedu-1
replace nedu=1 if nedu==9 | nedu==-1
tab nedu
replace edu=nedu if edu==.
tab edu
label var  edu "Illiterate/Semi-literate/No need for education = 1; Primary school = 2; Junior high school = 3; High school/Technical secondary school/Vocational high school = 4; Junior college = 5; Bachelor's degree = 6; Master's degree = 7; Doctoral degree = 8" 
tab gender,sum(edu)

//5.Occupational status
tab qg14 
tabstat qg14,stat(mean n) by(qg14)
gen admin=qg14
replace admin=. if admin<0 
label var admin "= 1 if holding an administrative/managerial position; = 0 otherwise"
tab gender, sum(admin)

//6.Annual wage income
tab income
tabstat income,stat(mean n) by(income) 
gen yinc=income
replace yinc=. if yinc<0
gen lnyinc=ln(yinc+1)
tab gender,sum(lnyinc)

//7.Employment stability
tab jobclass   
tab jobclass qg5
tab qg5
tabstat qg5,stat(mean n) by(qg5)
gen contract=qg5
replace contract=. if contract<0
label var  contract "= 1 if a labor contract has been signed; = 0 otherwise" 
tab gender,sum(contract)

//8.Work hours
tab qg6
tabstat qg6,stat(mean n) by(qg6) 
gen whour=qg6
replace whour=. if whour<0
label var  whour "Weekly working hours (hours)" 
tab gender,sum(whour)

//9.Emotional disposition
tab qn406
tabstat qn406,stat(mean n) by(qn406) 
gen emo=qn406
replace emo=. if emo<0
label var  emo "Frequency of feeling depressed over the past week; 1 = rarely, 4 = most of the time (5–7 days)" 
tab gender,sum(emo)  

//10.popularity
tab qm2011 
tabstat qm2011,stat(mean n) by(qm2011) 
gen popularity=qm2011
replace popularity=. if popularity<0
label var  popularity "0–10 scale. Higher values indicate better interpersonal relations" 
tab gender,sum(popularity) 

//11.Health status
tab qp201
tabstat qp201, stat(mean n) by(qp201) 
gen health=qp201
replace health=. if health<0
label var health "Very Healthy=1;Unhealthy=5"
tab gender,sum(health)  


//12.Internet use
tab qu201
tabstat qu201, stat(mean n) by(qu201) 
gen mobile=qu201 
replace mobile=. if mobile<0
tab qu202 
gen computer=qu202 
replace computer=. if computer<0
egen internet =rowmax( mobile computer)
tab internet
label variable internet "Internet use"
tab gender,sum(internet)

//13.Urban residence
tab  urban18
tabstat  urban18, stat(mean n) by( urban18) 
gen urban= urban18
replace urban=. if urban<0
label variable urban "Urban residence"
tab gender ,sum(urban)

//14. Housework hours
tab qq9010
tabstat qq9010, stat(mean n) by(qq9010) 
gen housework_original=qq9010
replace housework_original=. if housework_original<0
label variable housework_original "Time spent on housework (hours)"
tab gender, sum(housework_original)

//15.province
tab provcd18
tabstat provcd18, stat(mean n) by(provcd18) 
list pid if provcd18==.
gen province=provcd18
list pid proxytype if provcd18==.
tab proxytype
tabstat proxytype, stat(mean n) by(proxytype) 

//16.Observations cleaning: Remove samples who are still in school
tab qc1
tabstat qc1, stat(mean n) by(qc1)
drop if qc1==1

//17.Observations cleaning: Remove samples who are still in school or training, those who are retired, those who have lost work capacity because of disability or illness
tab qgb6
tabstat qgb6, stat(mean n) by(qgb6)
drop if qgb6==3 | qgb6==5 | qgb6==7

//18.employment status
tab employ
tabstat employ, stat(mean n) by(employ) 
replace employ=. if employ<0
label variable  employ "current employment status. 0=unemloyment;1=emploment；3=Withdraw from the labor market"

//19.Job Type
tab jobclass
tabstat jobclass, stat(mean n) by(jobclass) 
gen job_type=jobclass
replace job_type=. if jobclass<1
label variable job_type"Main types of employment: 1= Self-employed in agricultural production and management; 2= Private enterprises/sole proprietorships/other self-employment; 3= Agricultural laborers; 4= Employed; 5=Non-agricultural casual workers"

//20.Spouse's Educational attainment
tab qea202
tabstat qea202, stat(mean n) by(qea202)
gen sp_edu1=qea202
replace sp_edu1=. if sp_edu1<0
tab sp_edu1
replace sp_edu1=sp_edu1-1
replace sp_edu1=1 if sp_edu1==-1 | sp_edu1==9
label variable sp_edu1"Spouse's Educational attainment.Illiterate/Semi-literate/No need for education = 1; Primary school = 2; Junior high school = 3; High school/Technical secondary school/Vocational high school = 4; Junior college = 5; Bachelor's degree = 6; Master's degree = 7; Doctoral degree = 8"

//21.Marital status
tab qea0 
tabstat qea0, stat(mean n) by(qea0)
gen marriage1=qea0
replace marriage1=. if marriage1<0
label variable marriage1"current marital status. 1=Unmarried; 2=Married (with a spouse); 3=Cohabiting; 4=Divorced; 5=Widowed"

//22.Job satisfaction
tab qg406
tabstat qg406, stat(mean n) by(qg406)
gen work_satisfy=qg406 
replace work_satisfy=. if work_satisfy<0
tab work_satisfy
label variable work_satisfy "Job satisfaction"

save 2018adult.dta ,replace

/*==================================================
              3: Family Relationship Database
==================================================*/

use "D:\Data for paper\2018\cfps2018famconf_202008_家庭关系.dta", clear

bys pid: egen num = count(pid) 
tab num 

//1. Number of children
forvalues i = 1(1)10{
	gen xx`i' = 1 if (alive_a18_c`i' == 1)    
 }  
egen childnumber2 = rowtotal(xx*)  
tab childnumber2 
label var  childnumber2 "Number of children"

//2.Age distribution of children
tab tb1y_a_c1
tabstat tb1y_a_c1, stat(mean n) by(tb1y_a_c1) 
 forvalues i = 1(1)10{
  replace tb1y_a_c`i' = . if tb1y_a_c`i' < 0       
  gen tb1b_a_c`i'=cyear-tb1y_a_c`i'  
    gen child0_2`i' = 1 if (alive_a18_c`i'== 1 & tb1b_a_c`i'<3 & tb1b_a_c`i' !=.) 
  replace child0_2`i' = 0 if (alive_a18_c`i'== 1 & tb1b_a_c`i' >2 & tb1b_a_c`i' !=. )   
    gen child3_5`i' = 1 if (alive_a18_c`i'== 1 & (tb1b_a_c`i'>2 & tb1b_a_c`i'<6) & tb1b_a_c`i' !=.) 
  replace child3_5`i' = 0 if (alive_a18_c`i'== 1 & (tb1b_a_c`i'<3| tb1b_a_c`i'>5) & tb1b_a_c`i' !=. )  
    gen child6_15`i' = 1 if (alive_a18_c`i'== 1 & (tb1b_a_c`i'>5 & tb1b_a_c`i'<18) & tb1b_a_c`i' !=.) 
  replace child6_15`i' = 0 if (alive_a18_c`i'== 1 & (tb1b_a_c`i'<6| tb1b_a_c`i'>15) & tb1b_a_c`i' !=. )  
  }
**Number of children aged 0–2
egen childnumber_0_2 = anycount(child0_2*),v(1) 
replace childnumber_0_2=. if childnumber2==0  
tab childnumber_0_2
label variable childnumber_0_2 "Number of children aged 0–2"
**Number of children aged 3–5
egen childnumber_3_5 = anycount(child3_5*),v(1)  
replace childnumber_3_5=. if childnumber2==0  
tab childnumber_3_5
label variable childnumber_3_5 "Number of children aged 3–5"
** Number of children aged 6–15
egen childnumber_6_15 = anycount(child6_15*),v(1) 
replace childnumber_6_15=. if childnumber2==0  
tab childnumber_6_15
label variable childnumber_6_15 "Number of children aged 6–15"
***age of the youngest child
gen c0_2=0  
replace c0_2=1 if (childnumber_0_2>0)
replace c0_2=. if childnumber2==0 
tab c0_2
label variable c0_2 "The youngest children are 0–2 years old"

gen c3_5=0
replace c3_5=1 if (childnumber_0_2==0 & childnumber_3_5>0)
replace c3_5=. if childnumber2==0 
tab c3_5
label variable c3_5 "The youngest children are 3–5 years old"

gen c6_15=0
replace c6_15=1 if (childnumber_0_2==0 & childnumber_3_5==0 & childnumber_6_15>0)
replace c6_15=. if childnumber2==0 
tab c6_15
label variable c6_15 "The youngest children are 6–15 years old"

//3.whether Live with an elderly person
tab tb6_a18_f
tabstat tb6_a18_f, stat(mean n) by(tb6_a18_f) 
gen par_f_reside=0
replace par_f_reside=1 if tb6_a18_f==1
tab par_f_reside
label variable par_f_reside"whether father live at home"

tab tb6_a18_m
tabstat tb6_a18_m, stat(mean n) by(tb6_a18_m) 
gen par_m_reside=0
replace par_m_reside=1 if tb6_a18_m==1
tab par_m_reside  
label variable par_m_reside"whether mother live at home"

tab tb6_a18_p
tabstat tb6_a18_p, stat(mean n) by(tb6_a18_p) 
gen p_reside=0
replace p_reside=1 if tb6_a18_p==1
tab p_reside
label variable p_reside"whether yourself live at home"

tab tb6_a18_s
tabstat tb6_a18_s, stat(mean n) by(tb6_a18_s) 
gen p_s_reside=0
replace p_s_reside=1 if tb6_a18_s==1
tab p_s_reside
label variable p_s_reside"whether  your spouse live at home"

//4.Is the spouse still alive
tab alive_a18_s
tabstat alive_a18_s, stat(mean n) by(alive_a18_s) 
gen sp_alive= alive_a18_s
replace sp_alive=. if sp_alive<0
label variable sp_alive "0=dead；1=alive"

//5.Spouse's Educational attainment
tab tb4_a18_s
tabstat tb4_a18_s, stat(mean n) by(tb4_a18_s) 
gen sp_edu2=tb4_a18_s
replace sp_edu2=. if sp_edu2<0
tab sp_edu2
label variable sp_edu2 "Spouse's Educational attainment. Illiterate/Semi-literate/No need for education = 1; Primary school = 2; Junior high school = 3; High school/Technical secondary school/Vocational high school = 4; Junior college = 5; Bachelor's degree = 6; Master's degree = 7; Doctoral degree = 8"
tab  tb2_a_p, sum(sp_edu2) 

//6.Marital status
tab tb3_a18_p
tabstat tb3_a18_p, stat(mean n) by(tb3_a18_p) 
gen marriage2=tb3_a18_p
replace marriage2=. if marriage2<0
label variable marriage2"current marital status. 1=Unmarried; 2=Married (with a spouse); 3=Cohabiting; 4=Divorced; 5=Widowed"
tab marriage2

//7.Education attainment
tab tb4_a18_p
tabstat tb4_a18_p,stat(mean n) by(tb4_a18_p)
gen edu2=tb4_a18_p
replace edu2=. if edu2<0
tab edu2
label var  edu2 "Education attainment" 
tab tb2_a_p,sum(edu2)

//8.alive or not
tab alive_a18_p
gen alive=alive_a18_p
tab alive

//9.Urban residence
tab fid_urban18
tabstat fid_urban18, stat(mean n) by(fid_urban18) 
gen urban2=fid_urban18
replace urban2=. if urban2<0
label variable urban2 "Urban residence"
tab tb2_a_p ,sum(urban2)

//10.province
tab fid_provcd18
tabstat fid_provcd18, stat(mean n) by(fid_provcd18) 
gen province2=fid_provcd18
replace province2=. if province2<0

keep pid childnumber2 c0_2 c3_5 c6_15 par_f par_m par_f_reside par_m_reside sp_alive sp_edu2 marriage2 code_a_s pid_a_s ta4y_a18_s p_reside p_s_reside edu2 alive urban2 province2

save 2018childnumber.dta, replace

keep pid p_reside
rename pid pid_c
save 2018children2.dta, replace

/*==================================================
             4: Consolidate data
==================================================*/
*Merging personal and family relationship databases
use 2018adult.dta ,clear
merge 1:1 pid using 7.2018childnumber.dta
keep if _merge==3  
drop _merge
order childnumber childnumber2
save 2018adult.dta ,replace

*Divide the adult database into a mother database and a father database by gender
use 2018adult.dta ,clear
keep if gender==1
renvars _all, postfix(_f)
save 2018_father.dta,replace

use 2018adult.dta ,clear
keep if gender==0
renvars _all, postfix(_m)
save 2018_mother.dta,replace

use 2018children.dta,clear  
merge 1:1 pid_c using 2018children2.dta
keep if _merge!=2  
drop _merge

merge m:1 pid_m using 2018_mother.dta
keep if _merge==3 
drop _merge

gen year=2018
save 2018mo_child_1.dta,replace 

/*==================================================
 5: Observations cleaning (excluding single mothers over the age of 55 who are unemployed)
==================================================*/
use 2018mo_child_1.dta,clear 
merge m:1 pid_f using 2018_father.dta
keep if _merge==3 | _merge==1  
rename _merge _merge_f
save 2018mo_fa_child_1.dta,replace  

use 2018mo_fa_child_1.dta,clear   
bys pid_c: egen num = count(pid_c)   
tab num 

//Observations cleaning :Make pid_m unique 
bys pid_m: egen num2 = count(pid_m)   
tab num2 childnumber2_m 

tab num2, sum(e_5)

duplicates drop pid_m e_5, force  
bys pid_m: egen num3 = count(pid_m)   
tab num3 childnumber2_m
drop if (num3==2 & e_5==.)  
bys pid_m: egen num4 = count(pid_m)   
tab num4 childnumber2_m
drop if (num4==2 & e_5==0)  
bys pid_m: egen num5 = count(pid_m)   
tab num5 childnumber2_m

tab e_5
tab e_5m
sum e_5
tab childnumber2_m, sum(e_5)

gen cn=childnumber2_m if childnumber2_m<=2
replace cn=3 if childnumber2_m>2
tab cn
tab cn, sum(e_5)


list childnumber2_m c0_2_m c3_5_m c6_15_m if childnumber2_m==0  
replace c0_2_m=0 if childnumber2_m==0  
replace c3_5_m=0 if childnumber2_m==0
replace c6_15_m=1 if childnumber2_m==0
replace childnumber2_m=1 if childnumber2_m==0

//Age Filter—Exclude women over 55
tab age_m  
keep if age_m<56

//excluding single mothers
tab marriage1_m  
list pid_m code_a_s_m pid_a_s_m pid_f marriage1_f code_a_s_f pid_a_s_f if marriage1_m==3 

list pid_m marriage2_m code_a_s_m pid_a_s_m pid_f marriage1_f code_a_s_f pid_a_s_f if marriage2_m==4  
tabstat pid_a_s_m if pid_a_s_m<100, stat(mean n) by(pid_a_s_m) 
drop if marriage2_m==4 & (pid_a_s_m==-8 | pid_a_s_m==79) 

list pid_m marriage2_m code_a_s_m pid_a_s_m qea209y_m pid_f marriage1_f code_a_s_f pid_a_s_f qea209y_f if marriage2_m==5  
drop if marriage1_m==5

//Clear the sample regarding the death of a spouse
tab sp_alive_m
list pid_m marriage1_m marriage2_m qea209y_m if sp_alive_m==0
drop if  sp_alive_m==0

//Excluding Non-Employed Individuals
tab employ_m
keep if employ_m==1 

//Modify outliers
replace c6_15_m=1 if c0_2_m==0 & c3_5_m==0 & c6_15_m==0 

save 2018mo_fa_child_2.dta,replace