*Purpose: This program creates the tables for the hospital staffing analysis.
*Author: Martha Johnson
*Date: 1/13/2020


** Contents **


*1) Calculate nurse FTE needs from patient days
*2) Calculate actual nurse FTEs from annual nursing hours
*3) Calculate gaps
*4) Calculate RN and LPN shares
*5) Calculate total FTE nurses and patients
*6) Output data for report


*set directories
global input "U:\NY Nurse Project\input"
global output "U:\NY Nurse Project\output"

*load analysis data
use "$input\hospital_data", clear


** 1) Calculate nurse FTE needs from patient days. **


*1 patient per nurse
foreach prefix in pediatric_surgical surgical_adult {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*1)
}

*2 patients per nurse
foreach prefix in critical_care_adult critical_care_peds leveliiiiv_crit_care {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*2)
}

*3 patients per nurse
foreach prefix in medical_peds peds_mixed_acuity medsurg_peds_combd ///
	stepdn_telemtry_peds step_dn_telemtry_adult adult_mixed_acuity ///
	other_ed neonate_mixed_acuity leveli_contg_care levelii_int obstetrics {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*3)
}

*4 patients per nurse
foreach prefix in adult_psych adolescent_psych childadolest_psych child_psych medsurg_combd_adult medical_adult ///
	geropsych specialty_psych other_unit_type_psy behavl_healthchemicl multiple_unit_types {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*4)
}

*5 patients per nurse
foreach prefix in adult_rehab pediatric_rehab rehab_ped_mixd_acuity rehab_mixd_acuity_ad skilled_nursing {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*5)
}

*6 patients per nurse
foreach prefix in well_baby_nursery {
	gen `prefix'_need=`prefix'_pv*24/(31.5*50*6)
}


** 2) Calculate actual nurse FTEs from annual nursing patient-care hours **


*FTE nurses in each service unit
foreach var of varlist *_nh {
	gen `var'_act=`var'/(31.5*50)
}
rename *_nh_act *_act


** 3) Calculate gaps **


foreach prefix in adolescent_psych adult_mixed_acuity adult_psych adult_rehab behavl_healthchemicl child_psych childadolest_psych critical_care_adult critical_care_peds geropsych leveli_contg_care levelii_int leveliiiiv_crit_care medsurg_combd_adult medical_adult surgical_adult medical_peds medsurg_peds_combd multiple_unit_types neonate_mixed_acuity obstetrics other_ed other_unit_type_psy pediatric_rehab pediatric_surgical peds_mixed_acuity rehab_mixd_acuity_ad rehab_ped_mixd_acuity skilled_nursing specialty_psych step_dn_telemtry_adult stepdn_telemtry_peds well_baby_nursery {
	gen `prefix'_gap=max(0,`prefix'_need-`prefix'_act)
}


** 4) Estimate RN and LPN shares


*sum nurse hours by nurse type
egen hrs_all=rowtotal(*_rns *_lpns)
egen hrs_rns=rowtotal(*_rns)
egen hrs_lpns=rowtotal(*_lpns)
egen hrs_uaps=rowtotal(*_uaps)

*percentage of each position type
gen pctrn=hrs_rns/hrs_all
gen pctlpn=hrs_lpns/hrs_all

*For two hospitals not reporting nurse hours, replace with average
foreach type in rn lpn {
	sum pct`type'
	local avpct`type'=r(mean)
	replace pct`type'=`avpct`type'' if mi(pct`type')
}

*sum gaps across service units and multiply by rn and lpn percentages
egen totgap=rowtotal(*gap)
gen rnlpn_need=totgap
gen rn_need=totgap*pctrn
gen lpn_need=totgap*pctlpn


** 5) Calculate total FTE nurses and patients


*total FTE nurses across service units:
egen tot_nh=rowtotal(*_nh)
gen fte_nh=tot_nh/(31.5*50)
gen fte_rns=fte_nh*pctrn
gen fte_lpns=fte_nh*pctlpn

*total patient FTEs across all service units
egen tot_pv=rowtotal(*_pv)
gen fte_pats=(tot_pv*24)/(31.5*50)


** 6) Output data for report


** Table 1 **

preserve

collapse (count) reporters=beds (sum) beds rnlpn_need rn_need lpn_need fte_rns fte_lpns fte_pats, by(phipregion)

*for appendix table, adjust FTE definition to 37.5 hours of patient care per week
gen rn_need_app=rn_need*(31.5/37.5)
gen lpn_need_app=lpn_need*(31.5/37.5)

*label variables for table
la var phipregion "Region"
la var reporters "Reporters"
la var beds "Certified beds"
la var rnlpn_need "RN/LPN need"
la var rn_need "RN need"
la var lpn_need "LPN need"
la var fte_rns "RNs"
la var fte_lpns "LPNs"
la var fte_pats "Patients"
la var rn_need_app "RN need for appendix"
la var lpn_need_app "LPN need for appendix"

*export table
export excel using "$output\Hospital_Tables", sheet("Table1_all_hosp_orig_ratios", replace) firstrow(varlabels)


** Table 2 **


*drop unnecessary variables
drop fte_pats rnlpn_need *_app

*merge in wage data
merge 1:1 phipregion using "$input\wages", keepusing(hosp_wage_rn hosp_wage_lpn)
keep if _m==3
drop _m

*adjust to 2019 dollars (1.055 is based on first 3 quarters of 2017 and 2019 employment cost index from BLS for health industries, series CIU1026200000000I (K))
foreach var of varlist *wage* {
	replace `var'=`var'*1.055
}

*existing wage costs
gen cost=hosp_wage_rn*fte_rns*36*52+hosp_wage_lpn*fte_lpns*36*52
sum cost
local cost=r(sum)
display `cost'

*new wages, lower bound and upper bound
foreach type in rn lpn {

	*lower bound
	gen wage_cost_old`type's_lb=fte_`type's*0.05*hosp_wage_`type'*36*52
	gen wage_cost_new`type's_lb=`type'_need*1.05*hosp_wage_`type'*36*52
	gen wage_cost_tot`type's_lb=wage_cost_old`type's_lb+wage_cost_new`type's_lb
	
	*upper bound
	gen wage_cost_old`type's_ub=fte_`type's*0.15*hosp_wage_`type'*36*52
	gen wage_cost_new`type's_ub=`type'_need*1.15*hosp_wage_`type'*36*52
	gen wage_cost_tot`type's_ub=wage_cost_old`type's_ub+wage_cost_new`type's_ub
	
}

*sum rn and lpn costs
foreach type1 in old new tot {
	foreach type2 in lb ub {
		gen wage_cost_`type1'_`type2'=wage_cost_`type1'rns_`type2'+wage_cost_`type1'lpns_`type2'
	}
}

*for appendix table, adjust FTE definition to 37.5 hours of patient care per week and 40 hours paid per week
gen wage_cost_tot_lb_app=wage_cost_tot_lb*(31.5/37.5)*(40/36)
gen wage_cost_tot_ub_app=wage_cost_tot_ub*(31.5/37.5)*(40/36)

*format
format wage_cost* %15.0fc

*sort
sort phipregion

*order
keep phipregion wage_cost_old_lb wage_cost_old_ub wage_cost_new_lb wage_cost_new_ub wage_cost_tot_lb wage_cost_tot_ub wage_cost_tot_lb_app wage_cost_tot_ub_app
order phipregion wage_cost_old_lb wage_cost_old_ub wage_cost_new_lb wage_cost_new_ub wage_cost_tot_lb wage_cost_tot_ub wage_cost_tot_lb_app wage_cost_tot_ub_app

*label variables for table
la var phipregion "Region"
la var wage_cost_old_lb "Lower Bound Wage Cost Existing Nurses"
la var wage_cost_new_lb "Lower Bound Wage Cost New Nurses"
la var wage_cost_tot_lb "Lower Bound Wage Cost Total"
la var wage_cost_old_ub "Upper Bound Wage Cost Existing Nurses"
la var wage_cost_new_ub "Upper Bound Wage Cost New Nurses"
la var wage_cost_tot_ub "Upper Bound Wage Cost Total"
la var wage_cost_tot_lb_app "Lower Bound Wage Cost Total for Appendix"
la var wage_cost_tot_ub_app "Upper Bound Wage Cost Total for Appendix"

*export table
export excel using "$output\Hospital_Tables", sheet("Table2_all_hosp_orig_ratios", replace) firstrow(varlabels)

*restore
restore


** Table 3 **


*preserve
preserve

*Collapse completely, then convert to long
collapse (count) facilities=fac_facid (sum) *_nh *_pv *_gap

*Convert to long
foreach type in nh pv gap {
	rename *_`type' `type'_*
}
reshape long nh_ pv_ gap_, i(facilities) string

*drop facilities and beds
drop facilities

*drop accidental rows---prefixes that didn't correspond to service units
drop if inlist(_j,"fte","tot")

*rename variables
rename _j service_unit
replace service_unit=ustrregexra(service_unit,"_"," ")

*Convert patient days to patient hours by multiplying by 24 hours.
gen pats_=pv_*24

*Convert both nurse hours and patient hours to FTE
foreach type in nh pats {
	gen fte_`type'=`type'_/(31.5*50)
}

*rename variables ending with underscore
rename *_ *

*drop hours and days variables
drop nh pv pats

*which ratio?
gen patients_per_nurse=.
replace patients=1 if inlist(service_unit, "surgical adult", "pediatric surgical")
replace patients=2 if inlist(service_unit, "leveliiiiv crit care", "critical care adult", "critical care peds")
replace patients=3 if inlist(service_unit, "other ed", "step dn telemtry adult", "stepdn telemtry peds", "medsurg peds combd", "medical peds")
replace patients=3 if inlist(service_unit, "peds mixed acuity", "levelii int", "leveli contg care", "neonate mixed acuity", "adult mixed acuity")
replace patients=4 if inlist(service_unit, "medsurg combd adult", "medical adult", "adolescent psych", "child psych", "childadolest psych")
replace patients=4 if inlist(service_unit, "adult psych", "geropsych", "multiple unit types", "other unit type psy", "specialty psych", "behavl healthchemicl")
replace patients=5 if inlist(service_unit, "rehab mixd acuity ad", "rehab ped mixd acuity", "skilled nursing", "adult rehab", "pediatric rehab")
replace patients=6 if inlist(service_unit, "well baby nursery")
replace patients=3 if inlist(service_unit, "obstetrics")

*rename service_units
replace service_unit="Level III/IV, Neo-Natal Critical Care" if service_unit=="leveliiiiv crit care"
replace service_unit="Level II, Neo-Natal Intermediate" if service_unit=="levelii int"
replace service_unit="Level I, Neo-Natal Continuing Care" if service_unit=="leveli contg care"
replace service_unit="Neo-Natal Mixed Acuity" if service_unit=="neonate mixed acuity"
replace service_unit="Well Baby Nursery" if service_unit=="well baby nursery"
replace service_unit="Critical Care, Pediatric" if service_unit=="critical care peds"
replace service_unit="Medical/Surgical Combined, Pediatric" if service_unit=="medsurg peds combd"
replace service_unit="Medical, Pediatric" if service_unit=="medical peds"
replace service_unit="Mixed Acuity, Pediatric" if service_unit=="peds mixed acuity"
replace service_unit="Step Down and Telemetry, Pediatric" if service_unit=="stepdn telemtry peds"
replace service_unit="Surgical, Pediatric" if service_unit=="pediatric surgical"
replace service_unit="Critical Care, Adult" if service_unit=="critical care adult"
replace service_unit="Mixed Acuity, Adult" if service_unit=="adult mixed acuity"
replace service_unit="Obstetrics" if service_unit=="obstetrics"
replace service_unit="Skilled Nursing, Adult" if service_unit=="skilled nursing"
replace service_unit="Step Down and Telemetry, Adult" if service_unit=="step dn telemtry adult"
replace service_unit="Medical/Surgical Combined, Adult" if service_unit=="medsurg combd adult"
replace service_unit="Medical, Adult" if service_unit=="medical adult"
replace service_unit="Surgical, Adult" if service_unit=="surgical adult"
replace service_unit="Adolescent Psych" if service_unit=="adolescent psych"
replace service_unit="Adult Psych" if service_unit=="adult psych"
replace service_unit="Behavioral Health/Chemical Psych" if service_unit=="behavl healthchemicl"
replace service_unit="Child/Adolescent Psych" if service_unit=="childadolest psych"
replace service_unit="Child Psych" if service_unit=="child psych"
replace service_unit="Geropsych" if service_unit=="geropsych"
replace service_unit="Multiple Unit Types Psych" if service_unit=="multiple unit types"
replace service_unit="Other Psychiatric" if service_unit=="other unit type psy"
replace service_unit="Specialty Psych" if service_unit=="specialty psych"
replace service_unit="Rehabilitation, Adult Mixed Acuity" if service_unit=="rehab mixd acuity ad"
replace service_unit="Rehabilitation, Adult" if service_unit=="adult rehab"
replace service_unit="Rehabilitation, Pediatric Mixed Acuity" if service_unit=="rehab ped mixd acuity"
replace service_unit="Rehabilitation, Pediatric" if service_unit=="pediatric rehab"
replace service_unit="Emergency Department" if service_unit=="other ed"

*service categories
gen service_unit_cat=""
replace service_unit_cat="Neonatal" if inlist(service_unit, "Level I, Neo-Natal Continuing Care", "Level II, Neo-Natal Intermediate", "Level III/IV, Neo-Natal Critical Care", "Neo-Natal Mixed Acuity", "Well Baby Nursery")
replace service_unit_cat="Pediatric" if inlist(service_unit, "Critical Care, Pediatric", "Medical, Pediatric", "Medical/Surgical Combined, Pediatric", "Mixed Acuity, Pediatric", "Surgical, Pediatric", "Step Down and Telemetry, Pediatric")
replace service_unit_cat="Adult" if inlist(service_unit, "Surgical, Adult", "Medical, Adult", "Medical/Surgical Combined, Adult", "Mixed Acuity, Adult", "Skilled Nursing, Adult", "Step Down and Telemetry, Adult", "Critical Care, Adult", "Obstetrics")
replace service_unit_cat="Psychiatric" if inlist(service_unit, "Adolescent Psych", "Adult Psych", "Behavioral Health/Chemical Psych", "Child Psych", "Child/Adolescent Psych", "Multiple Unit Types Psych", "Other Psychiatric", "Specialty Psych", "Geropsych")
replace service_unit_cat="Rehabilitation" if inlist(service_unit, "Rehabilitation, Adult", "Rehabilitation, Adult Mixed Acuity", "Rehabilitation, Pediatric", "Rehabilitation, Pediatric Mixed Acuity")
replace service_unit_cat="Emergency Department" if inlist(service_unit, "Emergency Department")

*export to excel
order service_unit service_unit_cat patients_per_nurse gap fte_nh fte_pats
la var fte_nh "RN/LPNs"
la var fte_pats "Patients"
la var service_unit "Service Unit"
la var service_unit_cat "Service Category"
la var patients_per_nurse "Max Patients per Nurse"
la var gap "RN/LPN need"
export excel using "$output\Hospital_Tables", sheet("Table3_all_hosp_orig_ratios", replace) firstrow(varlabels)

*restore
restore