Logo

LOGITPRED

Updated 2023-11-01 20:48:05.237000

Syntax

SELECT [westclintech].[wct].[LOGITPRED](
  <@B, nvarchar(max),>
 ,<@New_x, nvarchar(max),>)

Description

Use the scalar function LOGITPRED to calculate the probability that Y = 1 given a set of coefficients from a logistic regression and a set of x-values. The probability is estimated as:

p = \hat{\pi} = \frac{e^{\beta_0 + \beta_1x_1+...+\beta_nx_n}}{1 + e^{\beta_0 + \beta_1x_1 + ... +\beta_nx_n}

Arguments

@B

the coefficients from a logit regression. The coefficients are passed in as a string and can either be in the form of a SELECT statement or as the explicit coefficient values separated by commas.

@New_x

the new x-values. The new x-values are passed in as a string and can either be in the form of a SELECT statement or as the explicit x-values to be used in the prediction calculation.

Return Type

float

Remarks

You should not pass an x-value for the ß0 coefficient.

Examples

We will run a logistic regression on the following data and then compare the observed y-value to the predicted y-value using the LOGITPROD function. We will use the LOGITSUM function to calculate the coefficients.

{"columns":[{"field":"Temp","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Water","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Male","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Female","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Temp":"20","Water":"0","Male":"21","Female":"0"},{"Temp":"21","Water":"0","Male":"90","Female":"6"},{"Temp":"22","Water":"0","Male":"91","Female":"23"},{"Temp":"23","Water":"0","Male":"61","Female":"73"},{"Temp":"24","Water":"0","Male":"11","Female":"41"},{"Temp":"25","Water":"0","Male":"4","Female":"28"},{"Temp":"20","Water":"1","Male":"18","Female":"4"},{"Temp":"21","Water":"1","Male":"75","Female":"9"},{"Temp":"22","Water":"1","Male":"68","Female":"21"},{"Temp":"23","Water":"1","Male":"59","Female":"65"},{"Temp":"24","Water":"1","Male":"17","Female":"46"},{"Temp":"25","Water":"1","Male":"7","Female":"22"}]}
--Put the data into a table
SELECT *,
       male / CAST(male + female as float) as y_obs
INTO   #t
  FROM (   VALUES (20, 0, 21, 0),
                  (21, 0, 90, 6),
                  (22, 0, 91, 23),
                  (23, 0, 61, 73),
                  (24, 0, 11, 41),
                  (25, 0, 4, 28),
                  (20, 1, 18, 4),
                  (21, 1, 75, 9),
                  (22, 1, 68, 21),
                  (23, 1, 59, 65),
                  (24, 1, 17, 46),
                  (25, 1, 7, 22)) n (temp, water, male, female);

--Perform the regression
SELECT *
INTO   #coef
  FROM wct.LOGITSUM('SELECT temp,water,male,female FROM #T', 3, 4);

--Calculate the predicted y-values and compare
--to the observed y-values
SELECT t.temp,
       t.water,
       t.y_obs,
       wct.LOGITPRED('SELECT
          stat_val
       FROM
          #coef
       WHERE stat_name = ''b''', CONCAT(t.temp, ',', t.water)) as y_pred
  FROM #t t;

This produces the following result.

{"columns":[{"field":"temp","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"water","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"y_obs","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"y_pred","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"temp":"20","water":"0","y_obs":"1","y_pred":"0.962666164888472"},{"temp":"21","water":"0","y_obs":"0.9375","y_pred":"0.898550857816897"},{"temp":"22","water":"0","y_obs":"0.798245614035088","y_pred":"0.752621841432599"},{"temp":"23","water":"0","y_obs":"0.455223880597015","y_pred":"0.511014157141803"},{"temp":"24","water":"0","y_obs":"0.211538461538462","y_pred":"0.264148436577182"},{"temp":"25","water":"0","y_obs":"0.125","y_pred":"0.109769453268386"},{"temp":"20","water":"1","y_obs":"0.818181818181818","y_pred":"0.961211464524059"},{"temp":"21","water":"1","y_obs":"0.892857142857143","y_pred":"0.89487074873382"},{"temp":"22","water":"1","y_obs":"0.764044943820225","y_pred":"0.745149498986974"},{"temp":"23","water":"1","y_obs":"0.475806451612903","y_pred":"0.501081697117896"},{"temp":"24","water":"1","y_obs":"0.26984126984127","y_pred":"0.25649731606484"},{"temp":"25","water":"1","y_obs":"0.241379310344828","y_pred":"0.105946142147757"}]}

Let's say we wanted to calculate the probability of Y = 1 when temp = 21.5 and water = 0. We can enter any of the following statement to get the result.

SELECT wct.LOGITPRED(
           'SELECT
          stat_val
        FROM
          #coef
        WHERE
          stat_name = ''b''
        ORDER BY
          idx ASC',
           '21.5,0') as p;
SELECT wct.LOGITPRED('24.62142607,-1.06858097,-0.039736962', '21.5,0') as p;

SELECT wct.LOGITPRED('SELECT 24.62142607,-1.06858097,-0.039736962', 'SELECT 21.5,0') as p;

SELECT wct.LOGITPRED(
           '24.62142607,-1.06858097,-0.039736962', 'SELECT X FROM (VALUES (0,21.5),(1,0))n(idx,x) ORDER BY idx') as p;

All of the preceding statements product the following result.

{"columns":[{"field":"p","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"p":"0.838476405223172"}]}

Using the data from Example #2 in the LOGIT documentation as input into a table called #mydata, we calculated the the coefficients using the following the SQL which stores the results in table called #mylogit.

SELECT *
INTO   #mydata
  FROM (   VALUES (1, 0, 380, 3.61, 3),
                  (2, 1, 660, 3.67, 3),
                  (3, 1, 800, 4, 1),
                  (4, 1, 640, 3.19, 4),
                  (5, 0, 520, 2.93, 4),
                  (6, 1, 760, 3, 2),
                  (7, 1, 560, 2.98, 1),
                  (8, 0, 400, 3.08, 2),
                  (9, 1, 540, 3.39, 3),
                  (10, 0, 700, 3.92, 2),
                  (11, 0, 800, 4, 4),
                  (12, 0, 440, 3.22, 1),
                  (13, 1, 760, 4, 1),
                  (14, 0, 700, 3.08, 2),
                  (15, 1, 700, 4, 1),
                  (16, 0, 480, 3.44, 3),
                  (17, 0, 780, 3.87, 4),
                  (18, 0, 360, 2.56, 3),
                  (19, 0, 800, 3.75, 2),
                  (20, 1, 540, 3.81, 1),
                  (21, 0, 500, 3.17, 3),
                  (22, 1, 660, 3.63, 2),
                  (23, 0, 600, 2.82, 4),
                  (24, 0, 680, 3.19, 4),
                  (25, 1, 760, 3.35, 2),
                  (26, 1, 800, 3.66, 1),
                  (27, 1, 620, 3.61, 1),
                  (28, 1, 520, 3.74, 4),
                  (29, 1, 780, 3.22, 2),
                  (30, 0, 520, 3.29, 1),
                  (31, 0, 540, 3.78, 4),
                  (32, 0, 760, 3.35, 3),
                  (33, 0, 600, 3.4, 3),
                  (34, 1, 800, 4, 3),
                  (35, 0, 360, 3.14, 1),
                  (36, 0, 400, 3.05, 2),
                  (37, 0, 580, 3.25, 1),
                  (38, 0, 520, 2.9, 3),
                  (39, 1, 500, 3.13, 2),
                  (40, 1, 520, 2.68, 3),
                  (41, 0, 560, 2.42, 2),
                  (42, 1, 580, 3.32, 2),
                  (43, 1, 600, 3.15, 2),
                  (44, 0, 500, 3.31, 3),
                  (45, 0, 700, 2.94, 2),
                  (46, 1, 460, 3.45, 3),
                  (47, 1, 580, 3.46, 2),
                  (48, 0, 500, 2.97, 4),
                  (49, 0, 440, 2.48, 4),
                  (50, 0, 400, 3.35, 3),
                  (51, 0, 640, 3.86, 3),
                  (52, 0, 440, 3.13, 4),
                  (53, 0, 740, 3.37, 4),
                  (54, 1, 680, 3.27, 2),
                  (55, 0, 660, 3.34, 3),
                  (56, 1, 740, 4, 3),
                  (57, 0, 560, 3.19, 3),
                  (58, 0, 380, 2.94, 3),
                  (59, 0, 400, 3.65, 2),
                  (60, 0, 600, 2.82, 4),
                  (61, 1, 620, 3.18, 2),
                  (62, 0, 560, 3.32, 4),
                  (63, 0, 640, 3.67, 3),
                  (64, 1, 680, 3.85, 3),
                  (65, 0, 580, 4, 3),
                  (66, 0, 600, 3.59, 2),
                  (67, 0, 740, 3.62, 4),
                  (68, 0, 620, 3.3, 1),
                  (69, 0, 580, 3.69, 1),
                  (70, 0, 800, 3.73, 1),
                  (71, 0, 640, 4, 3),
                  (72, 0, 300, 2.92, 4),
                  (73, 0, 480, 3.39, 4),
                  (74, 0, 580, 4, 2),
                  (75, 0, 720, 3.45, 4),
                  (76, 0, 720, 4, 3),
                  (77, 0, 560, 3.36, 3),
                  (78, 1, 800, 4, 3),
                  (79, 0, 540, 3.12, 1),
                  (80, 1, 620, 4, 1),
                  (81, 0, 700, 2.9, 4),
                  (82, 0, 620, 3.07, 2),
                  (83, 0, 500, 2.71, 2),
                  (84, 0, 380, 2.91, 4),
                  (85, 1, 500, 3.6, 3),
                  (86, 0, 520, 2.98, 2),
                  (87, 0, 600, 3.32, 2),
                  (88, 0, 600, 3.48, 2),
                  (89, 0, 700, 3.28, 1),
                  (90, 1, 660, 4, 2),
                  (91, 0, 700, 3.83, 2),
                  (92, 1, 720, 3.64, 1),
                  (93, 0, 800, 3.9, 2),
                  (94, 0, 580, 2.93, 2),
                  (95, 1, 660, 3.44, 2),
                  (96, 0, 660, 3.33, 2),
                  (97, 0, 640, 3.52, 4),
                  (98, 0, 480, 3.57, 2),
                  (99, 0, 700, 2.88, 2),
                  (100, 0, 400, 3.31, 3),
                  (101, 0, 340, 3.15, 3),
                  (102, 0, 580, 3.57, 3),
                  (103, 0, 380, 3.33, 4),
                  (104, 0, 540, 3.94, 3),
                  (105, 1, 660, 3.95, 2),
                  (106, 1, 740, 2.97, 2),
                  (107, 1, 700, 3.56, 1),
                  (108, 0, 480, 3.13, 2),
                  (109, 0, 400, 2.93, 3),
                  (110, 0, 480, 3.45, 2),
                  (111, 0, 680, 3.08, 4),
                  (112, 0, 420, 3.41, 4),
                  (113, 0, 360, 3, 3),
                  (114, 0, 600, 3.22, 1),
                  (115, 0, 720, 3.84, 3),
                  (116, 0, 620, 3.99, 3),
                  (117, 1, 440, 3.45, 2),
                  (118, 0, 700, 3.72, 2),
                  (119, 1, 800, 3.7, 1),
                  (120, 0, 340, 2.92, 3),
                  (121, 1, 520, 3.74, 2),
                  (122, 1, 480, 2.67, 2),
                  (123, 0, 520, 2.85, 3),
                  (124, 0, 500, 2.98, 3),
                  (125, 0, 720, 3.88, 3),
                  (126, 0, 540, 3.38, 4),
                  (127, 1, 600, 3.54, 1),
                  (128, 0, 740, 3.74, 4),
                  (129, 0, 540, 3.19, 2),
                  (130, 0, 460, 3.15, 4),
                  (131, 1, 620, 3.17, 2),
                  (132, 0, 640, 2.79, 2),
                  (133, 0, 580, 3.4, 2),
                  (134, 0, 500, 3.08, 3),
                  (135, 0, 560, 2.95, 2),
                  (136, 0, 500, 3.57, 3),
                  (137, 0, 560, 3.33, 4),
                  (138, 0, 700, 4, 3),
                  (139, 0, 620, 3.4, 2),
                  (140, 1, 600, 3.58, 1),
                  (141, 0, 640, 3.93, 2),
                  (142, 1, 700, 3.52, 4),
                  (143, 0, 620, 3.94, 4),
                  (144, 0, 580, 3.4, 3),
                  (145, 0, 580, 3.4, 4),
                  (146, 0, 380, 3.43, 3),
                  (147, 0, 480, 3.4, 2),
                  (148, 0, 560, 2.71, 3),
                  (149, 1, 480, 2.91, 1),
                  (150, 0, 740, 3.31, 1),
                  (151, 1, 800, 3.74, 1),
                  (152, 0, 400, 3.38, 2),
                  (153, 1, 640, 3.94, 2),
                  (154, 0, 580, 3.46, 3),
                  (155, 0, 620, 3.69, 3),
                  (156, 1, 580, 2.86, 4),
                  (157, 0, 560, 2.52, 2),
                  (158, 1, 480, 3.58, 1),
                  (159, 0, 660, 3.49, 2),
                  (160, 0, 700, 3.82, 3),
                  (161, 0, 600, 3.13, 2),
                  (162, 0, 640, 3.5, 2),
                  (163, 1, 700, 3.56, 2),
                  (164, 0, 520, 2.73, 2),
                  (165, 0, 580, 3.3, 2),
                  (166, 0, 700, 4, 1),
                  (167, 0, 440, 3.24, 4),
                  (168, 0, 720, 3.77, 3),
                  (169, 0, 500, 4, 3),
                  (170, 0, 600, 3.62, 3),
                  (171, 0, 400, 3.51, 3),
                  (172, 0, 540, 2.81, 3),
                  (173, 0, 680, 3.48, 3),
                  (174, 1, 800, 3.43, 2),
                  (175, 0, 500, 3.53, 4),
                  (176, 1, 620, 3.37, 2),
                  (177, 0, 520, 2.62, 2),
                  (178, 1, 620, 3.23, 3),
                  (179, 0, 620, 3.33, 3),
                  (180, 0, 300, 3.01, 3),
                  (181, 0, 620, 3.78, 3),
                  (182, 0, 500, 3.88, 4),
                  (183, 0, 700, 4, 2),
                  (184, 1, 540, 3.84, 2),
                  (185, 0, 500, 2.79, 4),
                  (186, 0, 800, 3.6, 2),
                  (187, 0, 560, 3.61, 3),
                  (188, 0, 580, 2.88, 2),
                  (189, 0, 560, 3.07, 2),
                  (190, 0, 500, 3.35, 2),
                  (191, 1, 640, 2.94, 2),
                  (192, 0, 800, 3.54, 3),
                  (193, 0, 640, 3.76, 3),
                  (194, 0, 380, 3.59, 4),
                  (195, 1, 600, 3.47, 2),
                  (196, 0, 560, 3.59, 2),
                  (197, 0, 660, 3.07, 3),
                  (198, 1, 400, 3.23, 4),
                  (199, 0, 600, 3.63, 3),
                  (200, 0, 580, 3.77, 4),
                  (201, 0, 800, 3.31, 3),
                  (202, 1, 580, 3.2, 2),
                  (203, 1, 700, 4, 1),
                  (204, 0, 420, 3.92, 4),
                  (205, 1, 600, 3.89, 1),
                  (206, 1, 780, 3.8, 3),
                  (207, 0, 740, 3.54, 1),
                  (208, 1, 640, 3.63, 1),
                  (209, 0, 540, 3.16, 3),
                  (210, 0, 580, 3.5, 2),
                  (211, 0, 740, 3.34, 4),
                  (212, 0, 580, 3.02, 2),
                  (213, 0, 460, 2.87, 2),
                  (214, 0, 640, 3.38, 3),
                  (215, 1, 600, 3.56, 2),
                  (216, 1, 660, 2.91, 3),
                  (217, 0, 340, 2.9, 1),
                  (218, 1, 460, 3.64, 1),
                  (219, 0, 460, 2.98, 1),
                  (220, 1, 560, 3.59, 2),
                  (221, 0, 540, 3.28, 3),
                  (222, 0, 680, 3.99, 3),
                  (223, 1, 480, 3.02, 1),
                  (224, 0, 800, 3.47, 3),
                  (225, 0, 800, 2.9, 2),
                  (226, 1, 720, 3.5, 3),
                  (227, 0, 620, 3.58, 2),
                  (228, 0, 540, 3.02, 4),
                  (229, 0, 480, 3.43, 2),
                  (230, 1, 720, 3.42, 2),
                  (231, 0, 580, 3.29, 4),
                  (232, 0, 600, 3.28, 3),
                  (233, 0, 380, 3.38, 2),
                  (234, 0, 420, 2.67, 3),
                  (235, 1, 800, 3.53, 1),
                  (236, 0, 620, 3.05, 2),
                  (237, 1, 660, 3.49, 2),
                  (238, 0, 480, 4, 2),
                  (239, 0, 500, 2.86, 4),
                  (240, 0, 700, 3.45, 3),
                  (241, 0, 440, 2.76, 2),
                  (242, 1, 520, 3.81, 1),
                  (243, 1, 680, 2.96, 3),
                  (244, 0, 620, 3.22, 2),
                  (245, 0, 540, 3.04, 1),
                  (246, 0, 800, 3.91, 3),
                  (247, 0, 680, 3.34, 2),
                  (248, 0, 440, 3.17, 2),
                  (249, 0, 680, 3.64, 3),
                  (250, 0, 640, 3.73, 3),
                  (251, 0, 660, 3.31, 4),
                  (252, 0, 620, 3.21, 4),
                  (253, 1, 520, 4, 2),
                  (254, 1, 540, 3.55, 4),
                  (255, 1, 740, 3.52, 4),
                  (256, 0, 640, 3.35, 3),
                  (257, 1, 520, 3.3, 2),
                  (258, 1, 620, 3.95, 3),
                  (259, 0, 520, 3.51, 2),
                  (260, 0, 640, 3.81, 2),
                  (261, 0, 680, 3.11, 2),
                  (262, 0, 440, 3.15, 2),
                  (263, 1, 520, 3.19, 3),
                  (264, 1, 620, 3.95, 3),
                  (265, 1, 520, 3.9, 3),
                  (266, 0, 380, 3.34, 3),
                  (267, 0, 560, 3.24, 4),
                  (268, 1, 600, 3.64, 3),
                  (269, 1, 680, 3.46, 2),
                  (270, 0, 500, 2.81, 3),
                  (271, 1, 640, 3.95, 2),
                  (272, 0, 540, 3.33, 3),
                  (273, 1, 680, 3.67, 2),
                  (274, 0, 660, 3.32, 1),
                  (275, 0, 520, 3.12, 2),
                  (276, 1, 600, 2.98, 2),
                  (277, 0, 460, 3.77, 3),
                  (278, 1, 580, 3.58, 1),
                  (279, 1, 680, 3, 4),
                  (280, 1, 660, 3.14, 2),
                  (281, 0, 660, 3.94, 2),
                  (282, 0, 360, 3.27, 3),
                  (283, 0, 660, 3.45, 4),
                  (284, 0, 520, 3.1, 4),
                  (285, 1, 440, 3.39, 2),
                  (286, 0, 600, 3.31, 4),
                  (287, 1, 800, 3.22, 1),
                  (288, 1, 660, 3.7, 4),
                  (289, 0, 800, 3.15, 4),
                  (290, 0, 420, 2.26, 4),
                  (291, 1, 620, 3.45, 2),
                  (292, 0, 800, 2.78, 2),
                  (293, 0, 680, 3.7, 2),
                  (294, 0, 800, 3.97, 1),
                  (295, 0, 480, 2.55, 1),
                  (296, 0, 520, 3.25, 3),
                  (297, 0, 560, 3.16, 1),
                  (298, 0, 460, 3.07, 2),
                  (299, 0, 540, 3.5, 2),
                  (300, 0, 720, 3.4, 3),
                  (301, 0, 640, 3.3, 2),
                  (302, 1, 660, 3.6, 3),
                  (303, 1, 400, 3.15, 2),
                  (304, 1, 680, 3.98, 2),
                  (305, 0, 220, 2.83, 3),
                  (306, 0, 580, 3.46, 4),
                  (307, 1, 540, 3.17, 1),
                  (308, 0, 580, 3.51, 2),
                  (309, 0, 540, 3.13, 2),
                  (310, 0, 440, 2.98, 3),
                  (311, 0, 560, 4, 3),
                  (312, 0, 660, 3.67, 2),
                  (313, 0, 660, 3.77, 3),
                  (314, 1, 520, 3.65, 4),
                  (315, 0, 540, 3.46, 4),
                  (316, 1, 300, 2.84, 2),
                  (317, 1, 340, 3, 2),
                  (318, 1, 780, 3.63, 4),
                  (319, 1, 480, 3.71, 4),
                  (320, 0, 540, 3.28, 1),
                  (321, 0, 460, 3.14, 3),
                  (322, 0, 460, 3.58, 2),
                  (323, 0, 500, 3.01, 4),
                  (324, 0, 420, 2.69, 2),
                  (325, 0, 520, 2.7, 3),
                  (326, 0, 680, 3.9, 1),
                  (327, 0, 680, 3.31, 2),
                  (328, 1, 560, 3.48, 2),
                  (329, 0, 580, 3.34, 2),
                  (330, 0, 500, 2.93, 4),
                  (331, 0, 740, 4, 3),
                  (332, 0, 660, 3.59, 3),
                  (333, 0, 420, 2.96, 1),
                  (334, 0, 560, 3.43, 3),
                  (335, 1, 460, 3.64, 3),
                  (336, 1, 620, 3.71, 1),
                  (337, 0, 520, 3.15, 3),
                  (338, 0, 620, 3.09, 4),
                  (339, 0, 540, 3.2, 1),
                  (340, 1, 660, 3.47, 3),
                  (341, 0, 500, 3.23, 4),
                  (342, 1, 560, 2.65, 3),
                  (343, 0, 500, 3.95, 4),
                  (344, 0, 580, 3.06, 2),
                  (345, 0, 520, 3.35, 3),
                  (346, 0, 500, 3.03, 3),
                  (347, 0, 600, 3.35, 2),
                  (348, 0, 580, 3.8, 2),
                  (349, 0, 400, 3.36, 2),
                  (350, 0, 620, 2.85, 2),
                  (351, 1, 780, 4, 2),
                  (352, 0, 620, 3.43, 3),
                  (353, 1, 580, 3.12, 3),
                  (354, 0, 700, 3.52, 2),
                  (355, 1, 540, 3.78, 2),
                  (356, 1, 760, 2.81, 1),
                  (357, 0, 700, 3.27, 2),
                  (358, 0, 720, 3.31, 1),
                  (359, 1, 560, 3.69, 3),
                  (360, 0, 720, 3.94, 3),
                  (361, 1, 520, 4, 1),
                  (362, 1, 540, 3.49, 1),
                  (363, 0, 680, 3.14, 2),
                  (364, 0, 460, 3.44, 2),
                  (365, 1, 560, 3.36, 1),
                  (366, 0, 480, 2.78, 3),
                  (367, 0, 460, 2.93, 3),
                  (368, 0, 620, 3.63, 3),
                  (369, 0, 580, 4, 1),
                  (370, 0, 800, 3.89, 2),
                  (371, 1, 540, 3.77, 2),
                  (372, 1, 680, 3.76, 3),
                  (373, 1, 680, 2.42, 1),
                  (374, 1, 620, 3.37, 1),
                  (375, 0, 560, 3.78, 2),
                  (376, 0, 560, 3.49, 4),
                  (377, 0, 620, 3.63, 2),
                  (378, 1, 800, 4, 2),
                  (379, 0, 640, 3.12, 3),
                  (380, 0, 540, 2.7, 2),
                  (381, 0, 700, 3.65, 2),
                  (382, 1, 540, 3.49, 2),
                  (383, 0, 540, 3.51, 2),
                  (384, 0, 660, 4, 1),
                  (385, 1, 480, 2.62, 2),
                  (386, 0, 420, 3.02, 1),
                  (387, 1, 740, 3.86, 2),
                  (388, 0, 580, 3.36, 2),
                  (389, 0, 640, 3.17, 2),
                  (390, 0, 640, 3.51, 2),
                  (391, 1, 800, 3.05, 2),
                  (392, 1, 660, 3.88, 2),
                  (393, 1, 600, 3.38, 3),
                  (394, 1, 620, 3.75, 2),
                  (395, 1, 460, 3.99, 3),
                  (396, 0, 620, 4, 2),
                  (397, 0, 560, 3.04, 3),
                  (398, 0, 460, 2.63, 2),
                  (399, 0, 700, 3.65, 2),
                  (400, 0, 600, 3.89, 3)) n (rn, admit, gre, gpa, [rank]);

SELECT *
INTO   #mylogit
  FROM wct.LOGIT(
           'SELECT
    admit
   ,gre
   ,gpa
   ,CASE RANK
       WHEN 2 THEN 1
      ELSE 0
    END
   ,CASE RANK
       WHEN 3 THEN 1
       ELSE 0
    END
   ,CASE RANK
       WHEN 4 THEN 1
       ELSE 0
    END
   FROM
       #mydata',
           1);

Remember that gre and gpa as treated as continuous data while rank has been treated as discrete data. The possible values for rank are 1, 2, 3, 4.

We calculate the predicted probability of admission at each value of rank by holding gre and gpa at their means.

SELECT ROUND(AVG(cast(gre as float)), 0) as gre,
       ROUND(AVG(cast(gpa as float)), 2) as gpa,
       x.rank as rank,
       ROUND(
           wct.LOGITPRED(
               'SELECT
          stat_val
        FROM
          #mylogit
        WHERE
          stat_name = ''b''',
               CONCAT(AVG(cast(gre as float)), ',', AVG(cast(gpa as float)), ',', x.rankstr)),
           3) as y_pred
  FROM #mydata
 CROSS APPLY (   VALUES (1, '0,0,0'),
                        (2, '1,0,0'),
                        (3, '0,1,0'),
                        (4, '0,0,1')) x (rank, rankstr)
 GROUP BY x.rank,
          x.rankstr;

This produces the following result.

{"columns":[{"field":"gre","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"gpa","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ank","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"_pred","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"gre":"588","gpa":"3.39","ank":"1","_pred":"0.517"},{"gre":"588","gpa":"3.39","ank":"2","_pred":"0.352"},{"gre":"588","gpa":"3.39","ank":"3","_pred":"0.219"},{"gre":"588","gpa":"3.39","ank":"4","_pred":"0.185"}]}

We can see from the above output that the predicted probability of success is 0.517 when the rank is 1 and 0.185 when the rank is 4 holding gre and gpa at their means.

See Also

LINEST - the Ordinary Least Squares (OLS) solution for a series of x-values and y-values

LOGEST - Logarithmic regression

LOGIT - Logit regression

LOGITPROB - Calculate the probability that Y = 1 given a set of coefficients from a logistic regression

LOGITSUM - Logit regression using summary data

VIF - Variance inflation factors