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


** Contents **


*1) Calculate actual nurse FTEs from hours paid
*2) Calculate nurse FTE needs from patient days
*3) Calculate gaps
*4) 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\nursing_home_data", clear


** 1) Calculate actual nurse FTEs from hours paid **


*preserve
preserve

*calculate reported nursing FTEs from hours paid
*assume paid for three 12-hour shifts per week, 52 weeks per year
foreach type in rn lpn aoa {
	gen myfte`type'=hrspd`type'/(36*52)
}


** 2) Calculate nurse FTE needs from patient days **


*calculate proposed nursing FTEs from required staffing hours per patient day
*assume an FTE nurse provides 31.5 hours of care per week, 50 weeks per year
*(note the beds variables are actually patient days) 
gen fteneedrn=(stotalbeds*.75)/(31.5*50)
gen fteneedlpn=(stotalbeds*1.3)/(31.5*50)
gen fteneedaoa=(stotalbeds*2.8)/(31.5*50)


** 3) Calculate gaps **


*gaps
foreach type in rn lpn aoa {
	gen ftegap`type'=max(0,fteneed`type'-myfte`type')
}


** 4) Output data for report **


** Table 1 **


*collapse by region
collapse (count) facilities=Organization (sum) stotalbeds myftern myftelpn ///
	myfteaoa fteneedrn fteneedlpn fteneedaoa ftegaprn ftegaplpn ftegapaoa, by(phipregion)
	
*labels
la var facilities "Number of facilities"
la var stotalbeds "Reported annual patient days"
la var myftern "Reported RNs"
la var myftelpn "Reported LPNs"
la var myfteaoa "Reported AOAs"
la var fteneedrn "Required RNs"
la var fteneedlpn "Required LPNs"
la var fteneedaoa "Required AOAs"
la var ftegaprn "Additional RNs needed"
la var ftegaplpn "Additional LPNs needed"
la var ftegapaoa "Additional AOAs needed"
la var phipregion "Region"

*export to excel
export excel using "$output\Nursing_Home_Tables", sheet("Table1", replace) firstrow(varlabels)


** Table 2 **


*merge in wage data
merge 1:1 phipregion using "$input\wages", keepusing(nh_wage_rn nh_wage_lpn nh_wage_aoa)
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=nh_wage_rn*myftern*36*52+nh_wage_lpn*myftelpn*36*52+nh_wage_aoa*myfteaoa*36*52
sum cost if phipregion!="Total"
local cost=r(sum)
display `cost'

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

	*lower bound
	gen wage_cost_old`type's_lb=myfte`type'*0.05*nh_wage_`type'*36*52
	gen wage_cost_new`type's_lb=ftegap`type'*1.05*nh_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=myfte`type'*0.15*nh_wage_`type'*36*52
	gen wage_cost_new`type's_ub=ftegap`type'*1.15*nh_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 and aoa 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'+wage_cost_`type1'aoas_`type2'
	}
}

*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
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

*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"

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


** Appendix Versions **


*restore
restore

*for appendix, change FTE calculations to assume 37.5 hours of patient care per week and 40 hours paid per week.
foreach type in rn lpn aoa {
	gen myfte`type'=hrspd`type'/(40*52)
}

*calculate proposed nursing FTEs from required staffing hours per patient day
gen fteneedrn=(stotalbeds*.75)/(37.5*50)
gen fteneedlpn=(stotalbeds*1.3)/(37.5*50)
gen fteneedaoa=(stotalbeds*2.8)/(37.5*50)

*gaps
foreach type in rn lpn aoa {
	gen ftegap`type'=max(0,fteneed`type'-myfte`type')
}

*collapse by region
collapse (count) facilities=Organization (sum) stotalbeds myftern myftelpn ///
	myfteaoa fteneedrn fteneedlpn fteneedaoa ftegaprn ftegaplpn ftegapaoa, by(phipregion)
	
*export to excel
la var facilities "Number of facilities"
la var stotalbeds "Reported annual patient days"
la var myftern "Reported RNs"
la var myftelpn "Reported LPNs"
la var myfteaoa "Reported AOAs"
la var fteneedrn "Required RNs"
la var fteneedlpn "Required LPNs"
la var fteneedaoa "Required AOAs"
la var ftegaprn "Additional RNs needed"
la var ftegaplpn "Additional LPNs needed"
la var ftegapaoa "Additional AOAs needed"
la var phipregion "Region"
export excel using "$output\Nursing_Home_Tables", sheet("Table1_appendix", replace) firstrow(varlabels)

*merge in wage data
merge 1:1 phipregion using "$input\wages", keepusing(nh_wage_rn nh_wage_lpn nh_wage_aoa)
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
}

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

	*lower bound
	gen wage_cost_old`type's_lb=myfte`type'*0.05*nh_wage_`type'*40*52
	gen wage_cost_new`type's_lb=ftegap`type'*1.05*nh_wage_`type'*40*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=myfte`type'*0.15*nh_wage_`type'*40*52
	gen wage_cost_new`type's_ub=ftegap`type'*1.15*nh_wage_`type'*40*52
	gen wage_cost_tot`type's_ub=wage_cost_old`type's_ub+wage_cost_new`type's_ub
	
}

*sum rn and lpn and aoa 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'+wage_cost_`type1'aoas_`type2'
	}
}

*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
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

*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"

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