/*
	Biostat 212 Week 4
	Data Cleaning Part 2
	(Advanced Topics)
	
	Cleaning do-file:
	This do-file cleans pt1.dta and pt2.dta
*/


/*	Note that we are not opening a dataset within this do-file. Instead we are
	going to use this do-file to clean both pt1.dta and pt2.dta. We will 
	change the directory and call the datasets in the next practice do-file for 
	this lecture, "merge.do" 
*/

***************************************************************
/*
	Dates:
	1) Turn date variable (date_str) into Stata date format - admit_date
	2) Create variable that indicates date of discharge (admission date + length 
	of stay)- discharge_date
*/
***************************************************************	
codebook date_str // verify string var & check to see how dates were entered
browse //confirm every data is in the same century
	
gen admit_date = date(date_str, "DMY")
	browse admit_date //see that Stata reports as numbers (no very useful, so format)
format admit_date %td
	browse admit_date date_str // compare old and new dates, make sure it worked
	
codebook lengthofstay // can assume this is in # of days
	
gen discharge_date = admit_date + lengthofstay
format discharge_date %td
	
browse admit_date lengthofstay discharge_date //confirm that this worked


***************************************************************	
/*
	Missing Values
*/
***************************************************************
* Find missing values
	misstable sum // no missing values, great! Wait...not so fast. Need to do range checks
	codebook // notice "hospital", "co2", "lungcapacity", "test1, test2", smokinghx", "familyhx"
	
	summarize co2 test1 test2 lungcapacity, detail
	
* Check categorical variables
	tab smokinghx, miss
	tab familyhx, miss

* For numeric variables where missings are stored as the number -99, we can use 
* mvdecode. This won't work if stored as a string (i.e. "-99").
	mvdecode _all, mv(-99=. \ -98=.a)
	
	/* this won't work because mvdecode doesn't work on strings:
	mvdecode _all, mv("-99") */
	
* Let's encode and recode appropriately for the categorical variables
	/* Note that encode automatically generates a label for the new variables */

* Encode familyhx
	encode familyhx, gen(famhist)
	labelbook // see label Stata generated for famhist
	tab familyhx famhist, nolab // nolab prevents labeling new var with old var values 
	tab familyhx famhist // crosstab is useless without nolab option!
	recode famhist (1=.) (2=0) (3=1) // recode
	lab def famhist 0"No" 1"Yes", modify // re-label - modify means we can skip label values step
	tab familyhx famhist, miss // check to see things look right

* Encode smokinghx
	encode smokinghx, gen(smokehist)
	tab smokinghx smokehist, nolab
	recode smokehist (4=0) (3=1) (2=2) (1=.)
	lab def smokehist 0"Never" 1"Former" 2"Current", modify
	tab smokinghx smokehist, missing
	
* Drop old variables
	drop familyhx smokinghx //only do this once you've compared new & old variables
	
	
* Use a special missing value .a to mean "refused to answer"
	mvdecode lungcapacity co2, mv(-98 = .a) 
	
	label define refused_label .a "refused to answer"
	label values lungcapacity refused_label
	codebook lungcapacity // tells us label applied
	tab lungcapacity, missing // shows the values
	
* Set any out-of-range ages to missing value .b to mean "data error"
	sum age
	spikeplot age
	replace age = .b if age > 120 | age < 18
		
		*good practice to then label ".b" values:
		label define dataerror .b "data error"
		label values age dataerror 
		tab age if age >100, mi //check how the label reads in output
		codebook age, det //anotehr way to look at missingness
	
* Look at patterns of missings (use AFTER you cleaned data to correctly distinguish missingness)
	misstable patterns
	misstable sum // note which values are designated as ., >., and <.
	
	/*
		xfill: user-written stata command to fill in missing values. Useful for 
		multiple observations for a single identifier. For example, in these 
		datasets, we have multiple rows with the same hospital ID, but we're 
		missing the hospital name from many of these rows. Hospital name is 
		constant within hospital ID. We can use xfill here to fill in the 
		missing values.
		
		Download and store the xfill.do do file and save it in your "do" folder.
		
	*/

* Make sure xfill is installed
	quietly run "do/xfill.do"

* Browse variables we want to use xfill for
	browse hospid hospital // note hospid 1 = UCLA, hospid 2 = UCSF, but most of the values for hospital are missing
	
* Fill in missing values
	xfill hospital, i(hospid) // we are filling in missing values of "hospital" to match the value that does exist for hospid.
	
	browse hospid hospital // check to see this worked

	/** IMPORTANT: This won't work if hospid did not UNIQUELY identify
	hospital - in other words, if we had multiple values of hospital within
	each hospid */
	
***************************************************************
/*
	Duplicates
*/
***************************************************************
* Count how many duplicated observations there are (duplicates across all vars)
	duplicates report
	
* Count how many duplicates there are on ptid
	duplicates report ptid
	
* Tag duplicates
	duplicates tag, gen(dup)
	tab dup
	
* Drop all but first copy of each duplicate
	duplicates drop //careful, cannot undo!
	
* Check to see if ptid and hospid are unique identifiers (together)
	isid ptid hospid //no error message tells us this variable set does uniquely identify observations