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

/*==================================================
         1: child database
==================================================*/
use "D:\Data for paper\2020\cfps2020person_202306_个人库.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
keep if age>9 & age<16  //样本为2126

//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 fid20 fid18 fid16 fid14 fid12 fid10 urban20 age gender pid_f pid_m  qk5 e_5 qn406 emotion_1 e_5m emotion_1m
rename pid pid_c 
save 2020children.dta ,replace

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

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

keep pid fid20 fid18 fid16 fid14 fid12 fid10 provcd20  age gender pid_a_f pid_a_m urban20 cfps2020edu qg14  emp_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
sum age
tab gender,sum(age)

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

//4.Education attainment
summ cfps2020edu w01
tab cfps2020edu
tabstat cfps2020edu,stat(mean n) by(cfps2020edu)
gen edu=cfps2020edu
replace edu=. if edu<0
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==.
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 emp_income
tabstat emp_income,stat(mean n) by(emp_income) 
gen yinc=emp_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 urban20
tabstat urban20, stat(mean n) by(urban20) 
gen urban=urban20
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=qq9010
replace housework=. if housework<0
label variable housework "Time spent on housework (hours)"
tab gender, sum(housework)

//15.province
tab provcd20 
tabstat provcd20, stat(mean n) by(provcd20) 
gen province=provcd20

//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 2020adult.dta ,replace

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

use "D:\Data for paper\2020\cfps2020famconf_202306_家庭关系.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_a20_c`i' == 1)     
 }  
egen childnumber2 = rowtotal(xx*) 
tab childnumber2  
label var  childnumber2 "Family Relationship Database_ 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_a20_c`i'== 1 & tb1b_a_c`i'<3 & tb1b_a_c`i' !=.) 
  replace child0_2`i' = 0 if (alive_a20_c`i'== 1 & tb1b_a_c`i' >2 & tb1b_a_c`i' !=. )   
    gen child3_5`i' = 1 if (alive_a20_c`i'== 1 & (tb1b_a_c`i'>2 & tb1b_a_c`i'<6) & tb1b_a_c`i' !=.) 
  replace child3_5`i' = 0 if (alive_a20_c`i'== 1 & (tb1b_a_c`i'<3| tb1b_a_c`i'>5) & tb1b_a_c`i' !=. )  
    gen child6_15`i' = 1 if (alive_a20_c`i'== 1 & (tb1b_a_c`i'>5 & tb1b_a_c`i'<16) & tb1b_a_c`i' !=.) 
  replace child6_15`i' = 0 if (alive_a20_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_a20_f
tabstat tb6_a20_f, stat(mean n) by(tb6_a20_f) 
gen par_f_reside=0
replace par_f_reside=1 if tb6_a20_f==1
tab par_f_reside
label variable par_f_reside"whether father live at home"

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

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

tab tb6_a20_s
tabstat tb6_a20_s, stat(mean n) by(tb6_a20_s) 
gen p_s_reside=0
replace p_s_reside=1 if tb6_a20_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_a20_s
tabstat alive_a20_s, stat(mean n) by(alive_a20_s) 
gen sp_alive= alive_a20_s
replace sp_alive=. if sp_alive<0
label variable sp_alive "0=dead；1=alive"

//5.Spouse's Educational attainment
tab tb4_a20_s
tabstat tb4_a20_s, stat(mean n) by(tb4_a20_s) 
gen sp_edu2=tb4_a20_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_a20_p
tabstat tb3_a20_p, stat(mean n) by(tb3_a20_p) 
gen marriage2=tb3_a20_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_a20_p
tabstat tb4_a20_p,stat(mean n) by(tb4_a20_p)
gen edu2=tb4_a20_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_a20_p
gen alive=alive_a20_p
tab alive

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

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


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

save 2020childnumber.dta, replace

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

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

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

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


use 2020children.dta,clear  
merge 1:1 pid_c using 2020children2.dta
keep if _merge!=2  
drop _merge
merge m:1 pid_m using 2020_mother.dta
keep if _merge==3
drop _merge

gen year=2020
save 2020mo_child_1.dta,replace  

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

use 2020mo_fa_child_1.dta,clear  

//Observations cleaning :Make pid_m unique
bys pid_c: egen num = count(pid_c)   
tab num  
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)

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

//excluding single mothers
tab marriage1_m  
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 marriage1_m==1  
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 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 marriage1_m==4  
drop if marriage1_m==4
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 marriage1_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


//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 2020mo_fa_child_2.dta,replace