* File: 04-find-athletes.do
**************************************************************
** STANDARD HEADERS TO SETUP THE SESSION                    **
**************************************************************
version 13          /* set the version                      */
capture clear       /* clear any existing data              */
capture log close   /* close any open logs                  */
set more off, perm  /* set 'more' to off in scrolling       */

global  basedir    "D:\athletics\"
global  importdir  "${basedir}rawdata\"
global  datadir    "${basedir}data\"

* Import the campus orgs file
import excel "${importdir}All Campus Org Affiliations.xlsx", firstrow

* Isolate the teams of interest
gen 	desc=""
replace desc="Pyramid" if org=="PYRM"
replace desc="Rollerball" if org=="FLNK"
replace desc="Flonkerton" if org=="CALV"
replace desc="Calvinball" if org=="QUID"
replace desc="ultimate Skeeball" if org=="SKE"
replace desc="Podracing" if org=="PODR"
keep if desc!=""

* Clean up
keep person_id f_name l_name *** dob desc start_date end_date
order person_id f_name l_name *** dob desc start_date end_date

* Create some variables to hold a person's team in any given year
gen t1516=""
gen t1617=""
gen t1718=""
gen t1819=""
gen t1920=""
 

* Identify whether or not the athlete was on the roster for any given academic year
* Drop cases where the participation ended before the 15/16 academic year
drop if end_date<date("05/31/15", "MDY", 2019) & end!=.

* Drop cases where participation started a long time ago (spurious records)
drop if start_date<date("6/1/2011", "MDY",2019) & start!=.

* 15/16 year: did they start before 5/1/16 and have no end date?
replace t1516=desc if start<date("5/01/16", "MDY", 2019) & end==.

* 15/16 year: did they end after 9/15/15 but start before that date?
replace t1516=desc if end>date("09/15/15", "MDY", 2019) & end!=. & start<date("09/15/15", "MDY", 2019)


* Repeat for each year
replace t1617=desc if start<date("05/01/17", "MDY", 2019) & end==.
replace t1617=desc if end>date("09/15/16", "MDY", 2019) & end!=. & start<date("09/15/16", "MDY", 2019)

replace t1718=desc if start<date("05/01/18", "MDY", 2019) & end==.
replace t1718=desc if end>date("09/15/17", "MDY", 2019) & end!=. & start<date("09/15/19", "MDY", 2019)

replace t1819=desc if start<date("05/01/19", "MDY", 2019) & end==.
replace t1819=desc if end>date("09/15/18", "MDY", 2019) & end!=. & start<date("09/15/18", "MDY", 2019)

replace t1920=desc if start<date("05/01/20", "MDY", 2020) & end ==.
replace t1920=desc if end>date("09/15/19", "MDY", 2020) & end!=. & start<date("09/15/19", "MDY", 2019)

* Reshape and clean up for importing
keep person_id f_name l_name dob t*

* Only keep records with athletic participation in at least 
* one year of interest
keep if t15!="" | t16!="" | t17!="" | t18!="" | t19!=""
collapse (firstnm) f_name-t1920, by(person)