06 Mar Data: We shall use the same data we have used for all our previous exercises – HMGT400Hosp.CSV? Using Excel To analyze the dat

Exercise #5

For this week's exercise, we need to try a few logit regression (or logistic regression) models (see this link for more information on these regression models:  https://stats.idre.ucla.edu/r/dae/logit-regression/ ). You should also review the email attachment I sent to you called "Interpreting Odds Ratios".

Data: We shall use the same data we have used for all our previous exercises - HMGT400Hosp.CSV 

Using Excel

To analyze the data using the logit regression model (Logistic Regression analysis) in Excel, we cannot use regular Analysis ToolPak. But we can use either the XLMiner Analysis ToolPak add-in or the RegressItLogistic add-in. Of course, XLMiner Analysis ToolPak is an add-in for Google Sheets or Excel Online. 

The “RegressItLogistic” add-in was developed by Professor Robert Nau, at the Duke University Business School in North Carolina. Instructions for downloading and using the add-in are at   https://regressit.com/regressitlogistic.html  . Note that we need the add-in that runs regressions including logistic regressions called “RegressItLogistic”. You can use it to run both linear regression and logistic regression models. There is a version for running just linear regression models called “RegressItPC”.

“RegressItLogistic” – Excel Add-In Software

To install “RegressItLogistic”, create a new “c:RegressIt” file folder in which to store your RegressIt files. Then use one of these two links to download the program file:

1. If your computer will not allow the direct download of an executable file, then use this link to get the program file  RegressItLogistic.xlam    (version 2020.03.04)  Right-click this link and choose the "save link as" option to save it to your new RegressIt file folder.

2. Otherwise use this link to get the program file in zip form:  RegressItLogistic.zip Right-click the link and choose the "save link as" option to save it to your RegressIt file folder. Then right-click on the saved file and choose "unzip to here". The program file will be extracted from the zip file.

Then follow these instructions to run the add-in for the first time:

1. To "Unblock" the program file go to the File Explorer, right-click on the file and choose Properties.

· At the bottom of the dialog box check the Unblock box.

· Then click Apply further below the unblock box.

· Then click OK

· This only needs to be done once. You should close the file explorer when running analyses because it may cause errors when producing non-editable graphs.

2. To run the program, start Excel, open the RegressItLogistic.xlam file, and click either "Trust all from the publisher" or "Enable macros" at the security prompt. You should see a RegressIt tab appear at the top of the Excel window. When you click on it you should see the RegressIt ribbon interface. You may click the "Instructions" button at the far right for details on how to load data and begin your analyses.

After you have tested the add-in as specified in the Instructions, please run the three models (using the HMGT400Hospital.CSV dataset we have used for all exercises) and complete the template tables below.

Data Setup for all Analyses (“RegressItLogistic”, "XLMiner Analysis ToolPak" or R :

Create an extract of the HMGT400Hospital.CSV dataset by selecting the columns having the dependent variable “system_member”, and the independent variables Total Hospital Costs, Total Hospital Revenue, Medicare Discharges, Medicaid Discharges, and Total Hospital Discharges. Create the Medicare Discharge ratio and the Medicaid Discharge ratio. Then as you would for all regressions, clean the data by deleting all rows (hospitals) that have missing values or #DIV/0! values. Be sure to state and describe in your report how you cleaned the data, indicating the number of hospitals you deleted and which variables had missing values or #DIV/0! Values. Re-save the data file as a file with a ___.CSV extension.

Using RegressItLogistic

Logit Model 1: Run a logit model to explain the "being a member of a network" variable (system_member). The independent variable is Total Hospital Costs. And choose 0.95 Confidence level. In options select the Logit and Exponentiated Coefficient Table (not just Logit) and request for P-values.The exponentiated coefficients (exp(coeff) ETC.) are the odds ratios. You may also request for the logistic curve or other plots or graphs you want, and request for the high-resolution graph format.

Table 1 – Logit Model 1

	Coefficient.	ST. ERR	P-Value		Exp (coeff.)	Exp (z SE)	Exp (Std. Coeff.)
Intercept
Total Hospital Costs
R-Squared

What is the impact of hospital costs on "being a member of a network"?

 

Logit Model 2: Run a logit model to explain the "being a member of a network" variable (system_member). The independent variables area Total Hospital Costs and Total Hospital Revenues. And choose 0.95 Confidence level. In options select the Logit and Exponentiated Coefficient Table (not just Logit) and request for P-values. You may also request for the logistic curve and high-resolution graph format.

Table 2 – Logit Model 2

	Coefficient.	ST. ERR	P-Value		Exp (coeff.)	Exp (z SE)	Exp (Std. Coeff.)
Intercept
Total Hospital Costs
Total Hospital Revenue
R-Squared

What is the impact of hospital costs and hospital revenue on "being a member of a network"?

 

Logit Model 3: For model 3, add the Medicare-discharge-ratio and the Medicaid-discharge-ratio variables to your Model 2, as independent variables.

Table 3 – Logit Model 3

	Coefficient.	ST. ERR	P-Value		Exp (coeff.)	Exp (z SE)	Exp (Std. Coeff.)
Intercept
Total Hospital Costs
Total Hospital Revenue
Medicare discharge ratio
Medicaid discharge ratio
R-Squared

What is the impact of hospital costs and hospital revenue, and each of the two ratios you added in Model 3 on "being a member of a network"?

Based on your findings from the three models, would you recommend that hospitals keep their system memberships? Why or why not? Discuss 3 policies you would advocate for based on your findings.

Please attach any plotted or graphed information you may want to use to make your case.

NOTE: After completing Exercise 5 students may start on the Final Exam (see content in week 8). The final Exam format follows Exercises 1 to 5 with regard to the data analysis. So go back and review the exercises and my feedback to your submissions.

======================================

Using R (Through RStudio)

If you chose to use RStudio you should do the following:

Get the R script from here:   E5-codes

Logit Model 1 : Run a logit model using being a member of a hospital system (system_member) as the dependent variable. The independent variables are Hospital beds, For-Profit Dummy, Public Ownership Dummy, Other Owner Type Dummy.

Note: AIC is the Akaike Information Criterion. The AIC is calculated from: the number of independent variables used to build the model. the “maximum likelihood estimate” of the model (how well the model reproduces the data). AIC is used to compare different possible models and determine which one is the best fit for the data.

Table 1 – Logit Model 1

	Coefficient -Estimate	Std. Err	z value	Pr(>|z|)	Exp(coeff.)
Hospital beds
For-Profit Dummy
Public Ownership Dummy
Other Owner Type Dummy
AIC =

What is the impact of hospital beds and the three ownership dummy variables on "being a member of a network"?

Logit Model 2 : Now, add hospital revenue as an additional independent variable

Table 2 – Logit Model 2

	Coefficient.	Std. Err	z value	Pr(>|z|)	Exp(coeff.)
Hospital beds
For-Profit Dummy
Public Ownership Dummy
Other Owner Type Dummy
Hospital Revenue
AIC =

Logit Model 3 : For model 3, add the Medicare-discharge-ratio and the Medicaid-discharge-ratio variables to your Model 2, as additional independent variables.

	Coefficient.	Std. Err	z value	Pr(>|z|)	Exp(coeff.)
Hospital beds
For-Profit Dummy
Public Ownership Dummy
Other Owner Type Dummy
Hospital Revenue
Medicare discharge ratio
Medicaid discharge ratio
AIC =

What is the impact of hospital beds, hospital revenue, the three ownership dummy variables, each of the two ratios you added in Model 3 on " being a member of a hospital system "?

Based on your findings from the three models, would you recommend that hospitals keep their system memberships? Why or why not? Discuss 3 policies you would advocate for based on your findings.

Please attach any plotted or graphed information you may want to use to make your case.

,

<

stata_name	stcd	year	total_hosp_cost	total_hosp_revenue	hospital_beds	bedsize_cat	teaching_hospital	system_member	level_trauma	white	rural_area	herf_cat	herf_index	non_white	log_hosp_cost	log_hosp_revenue	total_hospital_beds	total_hospital_medicare_days	total_hospital_medicaid_days	interns_and_residents	total_hospital_employees_on_payr	total_hospital_non_paid_workers	total_hospital_medicare_discharg	total_hospital_medicaid_discharg	total_hospital_discharges	own
Arizona	86	2012	1.89E+07	1.73E+07	19	1	0	0	0	58.7	0	1	0	41.3	16.75435	16.66785	168.92	11551.5	8206.92		855.048		2695.488	2867	8879	0
Arizona	86	2012	8.01E+07	7.94E+07	88	3	0	0	0	58.5	0	1	2	41.5	18.19875	18.19	138.02	14629.86	2423.52		1209.024		4117.736	697	6998	1
Arizona	86	2012	1.47E+08	1.33E+08	134	4	0	0	0	82	0	1	2	18	18.80468	18.70265	74.16	3784.2	4354.38		490.464		1305.488	1253	4320	0
Arizona	86	2012	7.74E+07	8.81E+07	72	3	0	0	0	82	0	1	2	18	18.16424	18.29439	25.75	306	225.42		132.84		74.504	66	257	0
Arizona	86	2012	1.53E+08	1.41E+08	187	4	0	0	0	58.7	0	1	2	41.3	18.84588	18.76257	19.57	1545.3	98.94		139.608		259.096	18	429	2
Arizona	86	2012	1.60E+07	1.70E+07	21	1	0	0	0	20.4	1	0	2	79.6	16.58738	16.65044	20.6	1042.44	235.62		185.148		160.128	77	366	2
Arizona	86	2012	7.02E+08	7.55E+08	460	7	0	0	1	55.3	0	1	2	44.7	20.36947	20.4425	493.37	28329.48	46840.44	356.34	4570.98		5216.392	9139	26341	2
Arizona	86	2012	2.07E+07	2.29E+07	14	1	0	0	3	58.5	0	1	2	41.5	16.84361	16.94799	237.93	5724.24	11063.94	95.39	1073.82		1195.4	1481	6836	0
Arizona	86	2012	1.67E+08	1.72E+08	163	4	0	0	3	55.3	0	1	2	44.7	18.93446	18.96016	14.42	961.86	255		154.62		151.232	98	356	1
Arizona	86	2012	2.32E+07	2.06E+07	56	3	0	1	0	16	0	1	2	84	16.95965	16.84255	61.8	3816.84	1180.14		307.2		1159.816	312	2738	0
Arizona	86	2012	9.60E+07	1.20E+08	3550	4	0	1	0	58.7	0	1	0	41.3	18.37936	18.59896	37.08	1138.32	2433.72		315.804		433.68	660	2426	0
Arizona	86	2012	1.31E+08	1.49E+08	110	4	0	1	0	82	0	1	2	18	18.68941	18.82107	101.97	8494.56	3713.82	8	882.924		2048.304	934	4332	1
Arizona	86	2012	1.81E+08	1.99E+08	460	5	0	1	0	58.7	0	1	0	41.3	19.01667	19.10902	360.5	12812.22	9098.4		1110		3155.856	2109	10925	1
Arizona	86	2012	5.37E+07	3.92E+07	3550	2	0	1	0	58.7	0	1	2	41.3	17.79862	17.48499	210.12	10312.2	9235.08		1303.464		2696.6	2715	12235	0
Arizona	86	2012	2.28E+08	2.45E+08	3550	6	0	1	0	58.7	0	1	0	41.3	19.24457	19.31838	354.32	27260.52	9731.82		1711.284		7236.896	2559	18413	1
Arizona	86	2012	3.15E+08	3.79E+08	267	5	0	1	1	55.2	0	2	2	44.8	19.56899	19.7525	252.35	16108.86	15733.5		1894.188		3968.728	3487	12895	2
Arizona	86	2012	2.64E+08	2.81E+08	460	5	0	1	1	58.7	0	1	0	41.3	19.39099	19.45325	273.98	17960.16	16340.4		1704.816		4232.272	3447	16298	2
Arizona	86	2012	4.70E+08	5.17E+08	3550	8	0	1	1	58.7	0	1	0	41.3	19.96899	20.06368	273.98	17960.16	16340.4		1704.816		4232.272	3447	16298	1
Arizona	86	2012	3.94E+08	4.35E+08	3550	7	0	1	1	58.7	0	1	0	41.3	19.79191	19.89182	273.98	17960.16	16340.4		1704.816		4232.272	3447	16298	1
Arizona	86	2012	3.48E+07	3.67E+07	25	2	0	1	3	65.9	0	1	2	34.1	17.36395	17.41857										0
Arizona	86	2012	5.24E+07	5.62E+07	49	2	1	0	0	52.3	0	2	2	47.7	17.77366	17.8452	507.79	29487.18	40869.36	44.66	3096.06		7225.776	8762	29644	1
Arizona	86	2012	4.28E+08	4.54E+08	553	8	1	0	0	55.3	0	1	2	44.7	19.8756	19.9329	50.47	2233.8	2249.1		500.772		563.784	654	2095	0
Arizona	86	2012	1.22E+08	1.23E+08	89	3	1	0	3	43.9	1	1	2	56.1	18.61977	18.62993	91.67	4144.26	5121.42		788.256		1223.2	1544	3712	0
Arizona	86	2012	2.23E+08	2.16E+08	3550	4	1	1	0	58.7	0	1	0	41.3	19.22436	19.19189	244.11	29369.88	2147.1	89.71	5149.536		6878.832	264	12315	2
Arizona	86	2012	2.27E+08	2.68E+08	3550	6	1	1	0	58.7	0	1	0	41.3	19.24058	19.40528	397.58	38496.84	4977.6	1.88	2182.056		9723.328	1340	22069	1
Arizona	86	2012	9.23E+08	9.84E+08	244	5	1	1	0	58.7	0	1	0	41.3	20.64341	20.70665	327.54	32268.72	11889.12		1869.108		8493.456	2780	20464	2
Arizona	86	2012	2.72E+08	2.95E+08	3550	7	1	1	0	58.7	0	1	0	41.3	19.42014	19.50291	169.95	8433.36	12750		1059.324		2466.416	4690	13654	0
Arizona	86	2012	6.06E+08	6.80E+08	3550	8	1	1	1	58.7	0	1	0	41.3	20.22179	20.33736	585.04	37101.48	53716.26	129.3	4415.16		7460.408	10624	36275	0
Arizona	86	2012	1.98E+08	2.41E+08	3550	5	1	1	2	58.7	0	1	0	41.3	19.1031	19.30064	220.42	12159.42	24371.88		1554.48		3299.304	5620	18796	2
Arkansas	71	2012	8125045	7994666	49	2	0	0	0	84	0	1	2	16	15.91046	15.89429	111.24	8150.82	1690.14		717.768		2257.36	803	4634	2
Arkansas	71	2012	7.38E+07	7.72E+07	125	4	0	0	0	95.2	0	2	2	4.800003	18.11635	18.16157	120.51	11514.78	1907.4		704.808		2766.656	561	4925	1
Arkansas	71	2012	7.12E+07	7.71E+07	124	4	0	0	0	89	0	1	2	11	18.08072	18.16073	13.39		1.02					1	1	2
Arkansas	71	2012	2.28E+07	2.36E+07	33	2	0	0	0	68.2	1	0	2	31.8	16.9437	16.97513	25.75	1785	57.12		103.896		394.76	26	502	1
Arkansas	71	2012	1.05E+07	1.03E+07	85	3	0	0	0	71	1	0	2	29	16.16907	16.143	25.75	1164.84	44.88		178.392		306.912	22	395	1
Arkansas	71	2012	1.62E+07	1.88E+07	25	2	0	0	0	40.3	1	0	2	59.7	16.60155	16.75008	25.75	1051.62	194.82		117.936		259.096	62	414	2
Arkansas	71	2012	9525674	8233617	25	2	0	0	0	92.2	1	0	2	7.800003	16.0695	15.92374	25.75	2970.24	762.96		180.468		760.608	429	1243	2
Arkansas	71	2012	2.08E+07	2.22E+07	72	3	0	0	0	84.1	1	0	2	15.9	16.85083	16.91463	25.75	3277.26	839.46		177.708		684.992	144	1136	2
Arkansas	71	2012	1.90E+07	1.95E+07	35	2	0	0	0	58	1	0	2	42	16.75869	16.7843	25.75	1048.56	140.76		108.792		253.536	47	380	1
Arkansas	71	2012	7280002	6124331	26	2	0	0	0	95	1	0	2	5	15.80064	15.62778	25.75	2063.46	1147.5		203.184		529.312	427	1213	2
Arkansas	71	2012	8981868	8779914	25	2	0	0	0	46.8	1	0	2	53.2	16.01072	15.98798	25.75	2063.46	1147.5		203.184		529.312	427	1213	2
Arkansas	71	2012	1.80E+08	1.80E+08