{"id":923,"date":"2012-11-17T16:13:45","date_gmt":"2012-11-17T07:13:45","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=923"},"modified":"2017-04-27T12:47:51","modified_gmt":"2017-04-27T03:47:51","slug":"execute-fishers-exact-test-with-t-sql","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=923","title":{"rendered":"Execute Fisher’s exact test with T-SQL"},"content":{"rendered":"

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. <\/p>\n

\r\nCREATE TABLE [dbo].[T_DATA]\r\n    (    ID       nchar(8)         NOT NULL, PRIMARY KEY\r\n    ,    Test     decimal(4, 2)    NOT NULL\r\n    ,    Outcome  nchar(1)         NOT NULL\r\n    );\r\nGO\r\nALTER TABLE T_DATA ADD CONSTRAINT CK_Outcome CHECK (Outcome = '0' OR Outcome = '1');\r\nGO<\/pre>\n
Run the code below to create function, converting from factorial to sum of logarithm. <\/p>\n
\r\nCREATE FUNCTION [dbo].[LOG_FACT](\r\n    @SrcNumber FLOAT\r\n)\r\nRETURNS FLOAT\r\nBEGIN\r\n    DECLARE @DesNumber   FLOAT\r\n    SET @DesNumber = LOG(1)\r\n    WHILE @SrcNumber > 0\r\n    BEGIN\r\n        SET @DesNumber = @DesNumber + LOG(@SrcNumber)\r\n        SET @SrcNumber = @SrcNumber - 1\r\n    END\r\n    RETURN @DesNumber\r\nEND\r\nGO<\/pre>\n
Run the code below to create stored procedure that creates cross table from T_DATA. <\/p>\n
\r\nCREATE PROCEDURE [dbo].[sp_Cut_by_Test]\r\n    @CutOff  decimal(4, 2)\r\nAS\r\nBEGIN\r\nWITH Cross_Table AS\r\n(\r\n  SELECT COUNT(*) AS 'N'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff AND T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'a'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff AND T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'b'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff AND T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'c'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff AND T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'd'\r\n    ,    SUM(CASE WHEN                            T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'a+c'\r\n    ,    SUM(CASE WHEN                            T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'b+d'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff                          THEN 1 ELSE 0 END) AS 'a+b'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff                          THEN 1 ELSE 0 END) AS 'c+d'\r\n    FROM T_DATA\r\n)\r\n  SELECT @CutOff\r\n    ,    Cross_Table.[N]\r\n    ,    Cross_Table.[a]\r\n    ,    Cross_Table.[b]\r\n    ,    Cross_Table.\r\n    ,    Cross_Table.[d]\r\n    ,    Cross_Table.[a+c]\r\n    ,    Cross_Table.[b+d]\r\n    ,    Cross_Table.[a+b]\r\n    ,    Cross_Table.\r\n    ,    Cross_Table.[a]\/Cross_Table.[a+c] AS 'Sensitivity'\r\n    ,    Cross_Table.[d]\/Cross_Table.[b+d] AS 'Specificity'\r\n    ,    1 - Cross_Table.[d]\/Cross_Table.[b+d] AS 'FalsePositive'\r\n    FROM Cross_Table;\r\nEND\r\nGO<\/pre>\n
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. <\/p>\n
\r\nCREATE PROCEDURE [dbo].[FisherExactTest]\r\n    (    @Start  decimal(4, 2)\r\n    ,    @End    decimal(4, 2)\r\n    ,    @Step   decimal(4, 2)\r\n    )\r\nAS\r\nBEGIN\r\n    CREATE TABLE #Result\r\n    (    [CutOff]    decimal(4, 2)    NOT NULL\r\n    ,    N    int    NOT NULL\r\n    ,    a    int    NOT NULL\r\n    ,    b    int    NOT NULL\r\n    ,    c    int    NOT NULL\r\n    ,    d    int    NOT NULL\r\n    ,    [a+c]    int    NOT NULL\r\n    ,    [b+d]    int    NOT NULL\r\n    ,    [a+b]    int    NOT NULL\r\n    ,        int    NOT NULL\r\n    ,    Sensitivity    FLOAT    NOT NULL\r\n    ,    Specificity    FLOAT    NOT NULL\r\n    ,    FalsePositive    FLOAT    NOT NULL\r\n    )\r\n    DECLARE @CutOff  decimal(4, 2)\r\n    SET @CutOff = @Start\r\n    WHILE @CutOff <= @End\r\n    BEGIN\r\n       INSERT INTO #Result EXEC sp_Cut_by_Test @CutOff\r\n       SET @CutOff = @CutOff + @Step\r\n    END\r\n  SELECT CutOff\r\n    ,    N\r\n    ,    a\r\n    ,    b\r\n    ,    c\r\n    ,    d\r\n    ,    [a+c]\r\n    ,    [b+d]\r\n    ,    [a+b]\r\n    ,    [c+d]\r\n    ,    Sensitivity\r\n    ,    Specificity\r\n    ,    FalsePositive\r\n    ,    EXP(dbo.LOG_FACT([a+b])\r\n         +   dbo.LOG_FACT([c+d])\r\n         +   dbo.LOG_FACT([a+c])\r\n         +   dbo.LOG_FACT([b+d])\r\n         -   dbo.LOG_FACT(N)\r\n         -   dbo.LOG_FACT(CASE WHEN a = 0 THEN 1 ELSE a END)\r\n         -   dbo.LOG_FACT(CASE WHEN b = 0 THEN 1 ELSE b END)\r\n         -   dbo.LOG_FACT(CASE WHEN c = 0 THEN 1 ELSE c END)\r\n         -   dbo.LOG_FACT(CASE WHEN d = 0 THEN 1 ELSE d END)) AS [FisherExact_Test]\r\n    FROM #Result\r\n   ORDER BY CutOff\r\nEND<\/pre>\n
References;
\nHow to calculate Fisher\u2019s exact test with logarithm?<\/a>
\nHow to calculate four numbers from marginal total in cross tabulation?<\/a>
\nStored procedure to calculate factorial with natural logarithm<\/a><\/p>\n