SQL Server LOGITSUM Function

Updated 2024-02-13 20:13:13.277000

Description

Use the table-valued function LOGITSUM to calculate the binary logistic regression coefficients from a table of grouped x-values (the independent variables) with counts of the successes and failures for each group. The successes and failures can appear in any column and the column number of the successes and failures is specified at input.

For dichotomous values, where the y-values are {0,1}, use the LOGIT function.

Logistic regression estimates the probability of an event occurring. Unlike ordinary least squares (see LINEST and LINEST_q) which estimates the value of a dependent variable based on the independent variables, LOGIT measures the probability (p) of an event occurring (1) or not occurring (0). 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}}

The LOGIT function works by finding the coefficient (ß) values that maximize the log-likelihood statistic, which is defined as:

LL = \sum_{i=1}^n N_i \times \{y_i \times \ln \hat{\pi}(x_i) + (1 - y_i) \times \ln (1 - \hat{\pi}(x_i)\}

using a method of iteratively re-weighted least squares.

Syntax

SELECT * FROM [westclintech].[wct].[LOGITSUM](
  <@Matrix_RangeQuery, nvarchar(max),>
 ,<@Success_ColumnNumber, int,>
 ,<@Failure_ColumnNumber, int,>)

Arguments

@Matrix_RangeQuery

the SELECT statement, as a string, which, when executed, creates the resultant table of x-values including the counts of successes and failures.

@Success_ColumnNumber

the number of the column in the resultant table returned by @Matrix_RangeQuery which contains the count of successes. @Success_ColumnNumber must be of the type int or of a type that implicitly converts to int.

@Failure_ColumnNumber

the number of the column in the resultant table returned by @Matrix_RangeQuery which contains the count of failures. @Failure_ColumnNumber must be of the type int or of a type that implicitly converts to int.

Return Type

table

Remarks

If @Success_ColumnNumber is NULL then the second right-most column in the resultant table is assumed to contain the count of successes.

If @Failure_ColumnNumber is NUL then the right-most column in the resultant table is assumed to contain the count of failures.

@Matrix_RangeQuery must return at least 3 columns or an error will be returned.

If @Success_ColumnNumber is not NULL and @Success_ColumnNumber < 1 an error will be returned.

If @Failure_ColumnNumber is not NULL and @Success_ColumnNumber < 1 an error will be returned.

If @Success_ColumnNumber is not NULL and @Success_ColumnNumber greater than the number of columns returned by @Matrix_RangeQuery an error will be returned.

If @Failure_ColumnNumber is not NULL and @Failure_ColumnNumber greater than the number of columns returned by @Matrix_RangeQuery an error will be returned.

Examples

Using the coronary heart disease data from LOGIT Example #1, we summarize the data into the 8 different age groups in Hosmer and use the summarized data in the logistic regression. This summarization can be handled entirely within the @MatrixRangeQuery string passed into the function.

SELECT *
INTO   #chd
  FROM (   VALUES (20, 0),
                  (23, 0),
                  (24, 0),
                  (25, 0),
                  (25, 1),
                  (26, 0),
                  (26, 0),
                  (28, 0),
                  (28, 0),
                  (29, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 0),
                  (30, 1),
                  (32, 0),
                  (32, 0),
                  (33, 0),
                  (33, 0),
                  (34, 0),
                  (34, 0),
                  (34, 1),
                  (34, 0),
                  (34, 0),
                  (35, 0),
                  (35, 0),
                  (36, 0),
                  (36, 1),
                  (36, 0),
                  (37, 0),
                  (37, 1),
                  (37, 0),
                  (38, 0),
                  (38, 0),
                  (39, 0),
                  (39, 1),
                  (40, 0),
                  (40, 1),
                  (41, 0),
                  (41, 0),
                  (42, 0),
                  (42, 0),
                  (42, 0),
                  (42, 1),
                  (43, 0),
                  (43, 0),
                  (43, 1),
                  (44, 0),
                  (44, 0),
                  (44, 1),
                  (44, 1),
                  (45, 0),
                  (45, 1),
                  (46, 0),
                  (46, 1),
                  (47, 0),
                  (47, 0),
                  (47, 1),
                  (48, 0),
                  (48, 1),
                  (48, 1),
                  (49, 0),
                  (49, 0),
                  (49, 1),
                  (50, 0),
                  (50, 1),
                  (51, 0),
                  (52, 0),
                  (52, 1),
                  (53, 1),
                  (53, 1),
                  (54, 1),
                  (55, 0),
                  (55, 1),
                  (55, 1),
                  (56, 1),
                  (56, 1),
                  (56, 1),
                  (57, 0),
                  (57, 0),
                  (57, 1),
                  (57, 1),
                  (57, 1),
                  (57, 1),
                  (58, 0),
                  (58, 1),
                  (58, 1),
                  (59, 1),
                  (59, 1),
                  (60, 0),
                  (60, 1),
                  (61, 1),
                  (62, 1),
                  (62, 1),
                  (63, 1),
                  (64, 0),
                  (64, 1),
                  (65, 1),
                  (69, 1)) n (age, chd)
SELECT *
  FROM wct.LOGITSUM(
           ' SELECT a.age as [Age] ,SUM(c.chd) as Present ,COUNT(*) - SUM(c.chd) as Absent FROM #chd c CROSS APPLY ( SELECT TOP 1 age FROM (VALUES (20,1),(30,2),(35,3),(40,4) ,(45,5),(50,6),(55,7),(60,8) )n(age,grp) WHERE n.age <= c.age ORDER BY n.age DESC )a GROUP BY a.age',
           2,
           3)

This produces the following result.

As you can see the results are similar but not exactly the same as those produced using the raw data in LOGIT . Using the summarized data, the fitted values are given by the equation:

We can compare the results of the two models for a 60-year old using the following SQL.

SELECT 1 / (1 + EXP(-1 * (-5.30945337391905 + 0.1109211422069 * 60))) as P_raw,
       1 / (1 + EXP(-1 * (-4.85868536330207 + 0.106379775834921 * 60))) as P_sum

This produces the following result.

Example #2

In this example we have 3 independent variables, a count of the number of successes and a count of the number of observations for each grouping. We will need to calculate the count of failures in @Matrix_RangeQuery .

SELECT *
INTO   #t
  FROM (   VALUES (100, 1, 10, 28, 156),
                  (150, 1, 10, 33, 144),
                  (200, 1, 10, 44, 171),
                  (250, 1, 10, 56, 196),
                  (300, 1, 10, 55, 158),
                  (350, 1, 10, 44, 100),
                  (400, 1, 10, 57, 126),
                  (450, 1, 10, 77, 166),
                  (500, 1, 10, 84, 166),
                  (100, 2, 10, 23, 153),
                  (150, 2, 10, 31, 165),
                  (200, 2, 10, 40, 179),
                  (250, 2, 10, 42, 152),
                  (300, 2, 10, 55, 181),
                  (350, 2, 10, 68, 200),
                  (400, 2, 10, 59, 148),
                  (450, 2, 10, 69, 156),
                  (500, 2, 10, 75, 157),
                  (100, 1, 11, 19, 164),
                  (150, 1, 11, 23, 147),
                  (200, 1, 11, 35, 182),
                  (250, 1, 11, 46, 196),
                  (300, 1, 11, 41, 143),
                  (350, 1, 11, 60, 189),
                  (400, 1, 11, 59, 162),
                  (450, 1, 11, 75, 187),
                  (500, 1, 11, 59, 129),
                  (100, 2, 11, 9, 105),
                  (150, 2, 11, 22, 179),
                  (200, 2, 11, 30, 182),
                  (250, 2, 11, 32, 155),
                  (300, 2, 11, 41, 164),
                  (350, 2, 11, 58, 200),
                  (400, 2, 11, 60, 181),
                  (450, 2, 11, 75, 199),
                  (500, 2, 11, 59, 141)) n (x1, x2, x3, success, N)
SELECT *
  FROM wct.LOGITSUM('SELECT x1,x2,x3,success,n-success from #t', 4, 5)

This produces the following result.

See Also

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

LOGEST - Logarithmic regression

LOGIT - Logit regression

LOGITPRED - Calculate predicted values based on a logit regression

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

VIF - Variance inflation factors