Dear Statalist members,
Kindly help me understand why every time I tab wbc,m and rbc,m the value of missing keep on changing on each of the variables.
I think for you to understand the problem you real need all my data and rerun the code a multiple time. I don't know how to attach it all here.It has around 60,000 observations.
Anyway can any one understand whats funny in my code?help
/**********************
Clean malaria dataset inorder to merge with the PneumoX dataset
***********************/
*cd "/Volumes/homedrive/fodiwuor/studies/Pneumox"
cd "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox"
clear all
set more off
clear matrix
set mem 3g
capture log close
log using "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox/logs/Clean_Malaria.smcl",replace
**Reading haema_tologyData**
**lab_specimen
set odbcdriver ansi
odbc query kidms
odbc load, exec("select * from lab_specimen")clear datestring
preserve
save data/temp/ALL_labskept_lab_specimen,replace
restore
compress
br
**when I can't run the code
use data/temp/ALL_labskept_lab_specimen,clear
keep id fk_lab pk_specimen_id
rename id fk_specimen
**have just added top lab ;before I was told only CTL lab process malaria results.that is was i used to generate "data/temp/malaria_clean" before adding top lab;remember data malaria clean has been used in for pneumox results(silaba paper.Don't alter data/temp/malaria_clean therefore')
*keep if fk_lab==1|fk_lab==2
count
sort fk_specimen
duplicates report fk_specimen
save lab_specimen,replace
**haematology_table this is a composite of parasites per 500 RBCs, parasites per 100 WBCs, RBC and WBC counts (top lab give results per mps_100wbc or 500_rbc)
odbc load, exec("select * from report_haematology") datestring clear
compress
preserve
save data/temp/ALL_haematologytable,replace
restore
***when i can't run haematology table because of network.
use data/temp/ALL_haematologytable,clear
keep fk_study serial_study_id fk_specimen pk_specimen_id specimen_type date_collect mps_100wbc mps_500rbc species mps mps_2000rbc wbc rbc pigment gametocytes
replace gametocytes=lower(trim(gametocytes))
***gametocytes
tab gametocytes,m
replace gametocytes="no" if inlist(gametocytes,"n","0","nil","no")
replace gametocytes="yes" if inlist(gametocytes,"y","yes")
**pigment
replace pigment=lower(trim(pigment))
tab pigment,m
replace pigment="no" if inlist(pigment,"n","0","nil","no")
replace pigment="yes" if inlist(pigment,"y","yes")
keep if fk_study==3
tab mps,m
replace mps=trim(lower(mps))
tab mps,m
replace mps="not requested" if strpos(mps,"re")
replace mps=trim(mps)
ren mps mps_haem
br if mps_haem=="no"
*keep serial_study_id specimen_type fk_study fk_specimen mps_haem date_collect wbc rbc mps_100wbc mps_500rbc mps_2000rbc
ren serial_study_id serial
gen doc=date(date_collect,"YMD")
format doc %td
drop date_collect
replace serial="98768" if serial=="98/768"
replace serial="103859" if serial=="`103859"
replace serial="108975" if serial=="`108975"
duplicates report fk_specimen
duplicates tag fk_specimen,gen(dups)
drop dups
tab mps_haem,m
replace mps_haem="negative" if inlist(mps_haem,"0","00","n o","n0","no","np")
replace mps_haem="positive" if strpos(mps_haem,"pos")|strpos(mps_haem,"yes")
tab mps_haem
replace serial=trim(serial)
save data/temp/Malaria_haem_reportF,replace
use data/temp/Malaria_haem_reportF,clear
***those totaly missing results
br serial fk_specimen pk_specimen_id fk_study mps_haem mps_100wbc mps_500rbc gametocytes pigment if mps_haem=="positive" & (missing(mps_100wbc)& missing(mps_500rbc)) & (pigment=="no" & gametocytes=="no")
cap drop _merge
cap drop dups
**merged haema_specimen
merge m:1 fk_specimen using "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox/lab_specimen"
drop if _merge==2
drop _merge
***check duplicates**
duplicates tag serial,gen(dups)
sort serial fk_specimen
order serial fk_specimen mps_haem fk_lab
br if dups>0
***generate sorting variable
gen sort1=1 if mps_haem=="positive"
replace sort1=2 if mps_haem=="negative"
replace sort1=3 if mps_haem=="not requested"
replace sort1=4 if missing(mps_haem)
clonevar pk_specimen_id1=pk_specimen_id
clonevar fk_specimen1=fk_specimen
clonevar mps_haem1=mps_haem
sort serial sort1 pk_specimen_id1
order serial fk_specimen mps_haem mps_haem1 fk_lab
sort serial sort1 pk_specimen_id1
bys serial:replace mps_haem1=mps_haem1[1]
bys serial:replace fk_specimen=fk_specimen[1]
bys serial:replace pk_specimen_id=pk_specimen_id[1]
order serial fk_specimen fk_specimen1 pk_specimen_id pk_specimen_id1 mps_haem mps_haem1 mps_100wbc mps_500rbc mps_2000rbc species wbc rbc
***encode serial wbc
*destring wbc,gen(wbc1) force
*replace wbc1=0 if missing(wbc1)
*gsort serial -wbc1
*bys serial:replace wbc=wbc[1]
***rbc
*destring rbc,gen(rbc1) force
*br if missing(rbc1)
*replace rbc1=0 if missing(rbc1)
*gsort serial -rbc1
*bys serial:replace rbc=rbc[1]
****mps_100wbc
*destring mps_100wbc,gen(mps_100wbc1) force
*br if missing(mps_100wbc1)
*replace mps_100wbc1=0 if missing(mps_100wbc1)
*br if mps_100wbc1==0
*replace mps_100wbc1=1 if mps_100wbc=="0"
*gsort serial -mps_100wbc1
*bys serial:replace mps_100wbc=mps_100wbc[1]
***mps_500rbc 100021
*clonevar mps_500rbcn=mps_500rbc
*br if missing(mps_500rbc1)
*replace mps_500rbc1=0 if missing(mps_500rbc1)
*br if mps_500rbc1==0
*replace mps_500rbc1=1 if mps_500rbc=="0"
*gsort serial -mps_500rbc1
*bys serial:replace mps_500rbc=mps_500rbc[1]
****mps_2000rbc
*clonevar mps_500rbcn=mps_500rbc
*destring mps_2000rbc,gen(mps_2000rbc1) force
*br if missing(mps_2000rbc1)
*replace mps_2000rbc1=0 if missing(mps_2000rbc1)
*br if mps_2000rbc1==0
*replace mps_2000rbc1=1 if mps_2000rbc=="0"
*gsort serial -mps_2000rbc1
*bys serial:replace mps_2000rbc=mps_2000rbc[1]
*order serial fk_specimen mps_haem mps_haem1 mps_100wbc mps_100wbc1 mps_500rbcn mps_500rbc mps_500rbc1
*drop wbc1 rbc1 mps_100wbc1 mps_500rbc1 mps_500rbcn mps_2000rbc1
**DROPPING not requested and missing br if dups>0 & mps_haem1=="positive"
*keep if fk_lab==1|mps_haem!="not requested"|mps_haem!=""
*br if fk_lab==1 & (mps_haem=="not requested"|mps_haem=="")
**dropping duplicates
*duplicates report,gen(copyx)
*duplicates drop
*duplicates drop serial mps_haem,force
*duplicates tag serial,gen(dups)
*sort serial
*order serial mps_haem _merge
*br if dups>=1
*bys serial: gen n=_N
*order serial mps_haem n _merge
*drop if dups>=1 & n==2 &(mps_haem=="not requested"|mps_haem=="")
*drop dups
*drop _merge
*order serial mps_haem
*br if mps_haem=="not requested"|mps_haem==""
*count if mps_haem=="not requested"|mps_haem==""
*drop n
*drop if mps_haem=="not requested"| mps_haem==""
*rename mps_haem mps
*save haema_specimenF,replace
*sort serial
*duplicates tag serial,gen(dups)
*br if dups>0
*drop if dups>0 & mps=="negative"
*keep serial mps
****duplicates drop serial
sort serial sort1 pk_specimen_id1
preserve
*gen original=1 if mps_haem=="positive"
keep if mps_haem1=="positive"
sort serial sort1 pk_specimen_id1
duplicates drop serial,force
keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc pigment gametocytes
save paed_malria_PositiveOnly,replace
keep serial
save paed_malria_PositiveOnlyFormatch,replace
restore
**merge with peads malaria
merge m:1 serial using paed_malria_PositiveOnlyFormatch
keep if _merge==1
drop _merge
preserve
sort serial sort1 pk_specimen_id1
duplicates drop serial,force
keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc pigment gametocytes
save paed_malria_NegMisOnly,replace
restore
***Append the data sets
use paed_malria_PositiveOnly,clear
append using paed_malria_NegMisOnly
duplicates drop serial,force
*br if mps_haem!=mps_haem1
*compare mps_haem mps_haem1
*br if missing(mps_haem1)
*drop mps_haem1
*keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc mps_haem species wbc rbc pigment gametocytes
order serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc gametocytes pigment species wbc rbc
rename mps_haem mps
gen datah="paeds_MalariaSlide"
****do some queries on mps_100wbc mps_500rbc,wbc,rbc ;Total malaria positive is 5362
**130 positive missing wbc ask kalume
tab wbc if mps=="positive",m
**132 positive missing rbc ask kalume
tab rbc if mps=="positive",m
regards,
Fred Orwa
Kindly help me understand why every time I tab wbc,m and rbc,m the value of missing keep on changing on each of the variables.
I think for you to understand the problem you real need all my data and rerun the code a multiple time. I don't know how to attach it all here.It has around 60,000 observations.
Anyway can any one understand whats funny in my code?help
/**********************
Clean malaria dataset inorder to merge with the PneumoX dataset
***********************/
*cd "/Volumes/homedrive/fodiwuor/studies/Pneumox"
cd "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox"
clear all
set more off
clear matrix
set mem 3g
capture log close
log using "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox/logs/Clean_Malaria.smcl",replace
**Reading haema_tologyData**
**lab_specimen
set odbcdriver ansi
odbc query kidms
odbc load, exec("select * from lab_specimen")clear datestring
preserve
save data/temp/ALL_labskept_lab_specimen,replace
restore
compress
br
**when I can't run the code
use data/temp/ALL_labskept_lab_specimen,clear
keep id fk_lab pk_specimen_id
rename id fk_specimen
**have just added top lab ;before I was told only CTL lab process malaria results.that is was i used to generate "data/temp/malaria_clean" before adding top lab;remember data malaria clean has been used in for pneumox results(silaba paper.Don't alter data/temp/malaria_clean therefore')
*keep if fk_lab==1|fk_lab==2
count
sort fk_specimen
duplicates report fk_specimen
save lab_specimen,replace
**haematology_table this is a composite of parasites per 500 RBCs, parasites per 100 WBCs, RBC and WBC counts (top lab give results per mps_100wbc or 500_rbc)
odbc load, exec("select * from report_haematology") datestring clear
compress
preserve
save data/temp/ALL_haematologytable,replace
restore
***when i can't run haematology table because of network.
use data/temp/ALL_haematologytable,clear
keep fk_study serial_study_id fk_specimen pk_specimen_id specimen_type date_collect mps_100wbc mps_500rbc species mps mps_2000rbc wbc rbc pigment gametocytes
replace gametocytes=lower(trim(gametocytes))
***gametocytes
tab gametocytes,m
replace gametocytes="no" if inlist(gametocytes,"n","0","nil","no")
replace gametocytes="yes" if inlist(gametocytes,"y","yes")
**pigment
replace pigment=lower(trim(pigment))
tab pigment,m
replace pigment="no" if inlist(pigment,"n","0","nil","no")
replace pigment="yes" if inlist(pigment,"y","yes")
keep if fk_study==3
tab mps,m
replace mps=trim(lower(mps))
tab mps,m
replace mps="not requested" if strpos(mps,"re")
replace mps=trim(mps)
ren mps mps_haem
br if mps_haem=="no"
*keep serial_study_id specimen_type fk_study fk_specimen mps_haem date_collect wbc rbc mps_100wbc mps_500rbc mps_2000rbc
ren serial_study_id serial
gen doc=date(date_collect,"YMD")
format doc %td
drop date_collect
replace serial="98768" if serial=="98/768"
replace serial="103859" if serial=="`103859"
replace serial="108975" if serial=="`108975"
duplicates report fk_specimen
duplicates tag fk_specimen,gen(dups)
drop dups
tab mps_haem,m
replace mps_haem="negative" if inlist(mps_haem,"0","00","n o","n0","no","np")
replace mps_haem="positive" if strpos(mps_haem,"pos")|strpos(mps_haem,"yes")
tab mps_haem
replace serial=trim(serial)
save data/temp/Malaria_haem_reportF,replace
use data/temp/Malaria_haem_reportF,clear
***those totaly missing results
br serial fk_specimen pk_specimen_id fk_study mps_haem mps_100wbc mps_500rbc gametocytes pigment if mps_haem=="positive" & (missing(mps_100wbc)& missing(mps_500rbc)) & (pigment=="no" & gametocytes=="no")
cap drop _merge
cap drop dups
**merged haema_specimen
merge m:1 fk_specimen using "/Users/fodiwuor/Library/CloudStorage/OneDrive-KemriWellcomeTrust/fodiwuor/studies/Pneumox/lab_specimen"
drop if _merge==2
drop _merge
***check duplicates**
duplicates tag serial,gen(dups)
sort serial fk_specimen
order serial fk_specimen mps_haem fk_lab
br if dups>0
***generate sorting variable
gen sort1=1 if mps_haem=="positive"
replace sort1=2 if mps_haem=="negative"
replace sort1=3 if mps_haem=="not requested"
replace sort1=4 if missing(mps_haem)
clonevar pk_specimen_id1=pk_specimen_id
clonevar fk_specimen1=fk_specimen
clonevar mps_haem1=mps_haem
sort serial sort1 pk_specimen_id1
order serial fk_specimen mps_haem mps_haem1 fk_lab
sort serial sort1 pk_specimen_id1
bys serial:replace mps_haem1=mps_haem1[1]
bys serial:replace fk_specimen=fk_specimen[1]
bys serial:replace pk_specimen_id=pk_specimen_id[1]
order serial fk_specimen fk_specimen1 pk_specimen_id pk_specimen_id1 mps_haem mps_haem1 mps_100wbc mps_500rbc mps_2000rbc species wbc rbc
***encode serial wbc
*destring wbc,gen(wbc1) force
*replace wbc1=0 if missing(wbc1)
*gsort serial -wbc1
*bys serial:replace wbc=wbc[1]
***rbc
*destring rbc,gen(rbc1) force
*br if missing(rbc1)
*replace rbc1=0 if missing(rbc1)
*gsort serial -rbc1
*bys serial:replace rbc=rbc[1]
****mps_100wbc
*destring mps_100wbc,gen(mps_100wbc1) force
*br if missing(mps_100wbc1)
*replace mps_100wbc1=0 if missing(mps_100wbc1)
*br if mps_100wbc1==0
*replace mps_100wbc1=1 if mps_100wbc=="0"
*gsort serial -mps_100wbc1
*bys serial:replace mps_100wbc=mps_100wbc[1]
***mps_500rbc 100021
*clonevar mps_500rbcn=mps_500rbc
*br if missing(mps_500rbc1)
*replace mps_500rbc1=0 if missing(mps_500rbc1)
*br if mps_500rbc1==0
*replace mps_500rbc1=1 if mps_500rbc=="0"
*gsort serial -mps_500rbc1
*bys serial:replace mps_500rbc=mps_500rbc[1]
****mps_2000rbc
*clonevar mps_500rbcn=mps_500rbc
*destring mps_2000rbc,gen(mps_2000rbc1) force
*br if missing(mps_2000rbc1)
*replace mps_2000rbc1=0 if missing(mps_2000rbc1)
*br if mps_2000rbc1==0
*replace mps_2000rbc1=1 if mps_2000rbc=="0"
*gsort serial -mps_2000rbc1
*bys serial:replace mps_2000rbc=mps_2000rbc[1]
*order serial fk_specimen mps_haem mps_haem1 mps_100wbc mps_100wbc1 mps_500rbcn mps_500rbc mps_500rbc1
*drop wbc1 rbc1 mps_100wbc1 mps_500rbc1 mps_500rbcn mps_2000rbc1
**DROPPING not requested and missing br if dups>0 & mps_haem1=="positive"
*keep if fk_lab==1|mps_haem!="not requested"|mps_haem!=""
*br if fk_lab==1 & (mps_haem=="not requested"|mps_haem=="")
**dropping duplicates
*duplicates report,gen(copyx)
*duplicates drop
*duplicates drop serial mps_haem,force
*duplicates tag serial,gen(dups)
*sort serial
*order serial mps_haem _merge
*br if dups>=1
*bys serial: gen n=_N
*order serial mps_haem n _merge
*drop if dups>=1 & n==2 &(mps_haem=="not requested"|mps_haem=="")
*drop dups
*drop _merge
*order serial mps_haem
*br if mps_haem=="not requested"|mps_haem==""
*count if mps_haem=="not requested"|mps_haem==""
*drop n
*drop if mps_haem=="not requested"| mps_haem==""
*rename mps_haem mps
*save haema_specimenF,replace
*sort serial
*duplicates tag serial,gen(dups)
*br if dups>0
*drop if dups>0 & mps=="negative"
*keep serial mps
****duplicates drop serial
sort serial sort1 pk_specimen_id1
preserve
*gen original=1 if mps_haem=="positive"
keep if mps_haem1=="positive"
sort serial sort1 pk_specimen_id1
duplicates drop serial,force
keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc pigment gametocytes
save paed_malria_PositiveOnly,replace
keep serial
save paed_malria_PositiveOnlyFormatch,replace
restore
**merge with peads malaria
merge m:1 serial using paed_malria_PositiveOnlyFormatch
keep if _merge==1
drop _merge
preserve
sort serial sort1 pk_specimen_id1
duplicates drop serial,force
keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc pigment gametocytes
save paed_malria_NegMisOnly,replace
restore
***Append the data sets
use paed_malria_PositiveOnly,clear
append using paed_malria_NegMisOnly
duplicates drop serial,force
*br if mps_haem!=mps_haem1
*compare mps_haem mps_haem1
*br if missing(mps_haem1)
*drop mps_haem1
*keep serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc species wbc rbc mps_haem species wbc rbc pigment gametocytes
order serial fk_specimen pk_specimen_id mps_haem mps_100wbc mps_500rbc gametocytes pigment species wbc rbc
rename mps_haem mps
gen datah="paeds_MalariaSlide"
****do some queries on mps_100wbc mps_500rbc,wbc,rbc ;Total malaria positive is 5362
**130 positive missing wbc ask kalume
tab wbc if mps=="positive",m
**132 positive missing rbc ask kalume
tab rbc if mps=="positive",m
regards,
Fred Orwa
Comment