Execute Fisher’s exact test with T-SQL

Fisher’s probability is function depend on cut-off value. In this article, you could draw scatter plot ROC curve and get Fisher’s probability with query below. In ROC curve, you would plot false positive rate on horizontal axis and sensitivity on vertical axis. You could plot cut-off value on horizontal axis and Fisher’s probability on vertical axis. You could get best cut-off value with minimum probability in line graph if minimum probability would be smaller than 0.05.

CREATE TABLE [dbo].[T_DATA]
    (    ID       nchar(8)         NOT NULL, PRIMARY KEY
    ,    Test     decimal(4, 2)    NOT NULL
    ,    Outcome  nchar(1)         NOT NULL
    );
GO
ALTER TABLE T_DATA ADD CONSTRAINT CK_Outcome CHECK (Outcome = '0' OR Outcome = '1');
GO

Run the code below to create function, converting from factorial to sum of logarithm.

CREATE FUNCTION [dbo].[LOG_FACT](
    @SrcNumber FLOAT
)
RETURNS FLOAT
BEGIN
    DECLARE @DesNumber   FLOAT
    SET @DesNumber = LOG(1)
    WHILE @SrcNumber > 0
    BEGIN
        SET @DesNumber = @DesNumber + LOG(@SrcNumber)
        SET @SrcNumber = @SrcNumber - 1
    END
    RETURN @DesNumber
END
GO

Run the code below to create stored procedure that creates cross table from T_DATA.

CREATE PROCEDURE [dbo].[sp_Cut_by_Test]
    @CutOff  decimal(4, 2)
AS
BEGIN
WITH Cross_Table AS
(
  SELECT COUNT(*) AS 'N'
    ,    SUM(CASE WHEN T_DATA.Test   @CutOff AND T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'c'
    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff AND T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'd'
    ,    SUM(CASE WHEN                            T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'a+c'
    ,    SUM(CASE WHEN                            T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'b+d'
    ,    SUM(CASE WHEN T_DATA.Test   @CutOff                          THEN 1 ELSE 0 END) AS 'c+d'
    FROM T_DATA
)
  SELECT @CutOff
    ,    Cross_Table.[N]
    ,    Cross_Table.[a]
    ,    Cross_Table.[b]
    ,    Cross_Table.
    ,    Cross_Table.[d]
    ,    Cross_Table.[a+c]
    ,    Cross_Table.[b+d]
    ,    Cross_Table.[a+b]
    ,    Cross_Table.
    ,    Cross_Table.[a]/Cross_Table.[a+c] AS 'Sensitivity'
    ,    Cross_Table.[d]/Cross_Table.[b+d] AS 'Specificity'
    ,    1 - Cross_Table.[d]/Cross_Table.[b+d] AS 'FalsePositive'
    FROM Cross_Table;
END
GO

Run the code below to create stored procedure that calculates Fisher’s probability. @Start means minimum value of test, @End means maximum value of test and @Step means step value of test from @Start to @End. For example, @Start 2.0, @End 4.0 and @Step 0.1, respectively.

CREATE PROCEDURE [dbo].[FisherExactTest]
    (    @Start  decimal(4, 2)
    ,    @End    decimal(4, 2)
    ,    @Step   decimal(4, 2)
    )
AS
BEGIN
    CREATE TABLE #Result
    (    [CutOff]    decimal(4, 2)    NOT NULL
    ,    N    int    NOT NULL
    ,    a    int    NOT NULL
    ,    b    int    NOT NULL
    ,    c    int    NOT NULL
    ,    d    int    NOT NULL
    ,    [a+c]    int    NOT NULL
    ,    [b+d]    int    NOT NULL
    ,    [a+b]    int    NOT NULL
    ,        int    NOT NULL
    ,    Sensitivity    FLOAT    NOT NULL
    ,    Specificity    FLOAT    NOT NULL
    ,    FalsePositive    FLOAT    NOT NULL
    )
    DECLARE @CutOff  decimal(4, 2)
    SET @CutOff = @Start
    WHILE @CutOff 
References;

How to calculate Fisher’s exact test with logarithm?

How to calculate four numbers from marginal total in cross tabulation?

Stored procedure to calculate factorial with natural logarithm