Created
March 27, 2012 22:08
-
-
Save rkelly/2220840 to your computer and use it in GitHub Desktop.
T-SQL function to calculate breast cancer risk based on Gail 1999 model
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [UCI_Athena] | |
GO | |
/****** Object: UserDefinedFunction [dbo].[ufn_RiskGail1999] Script Date: 03/27/2012 13:45:05 ******/ | |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RiskGail1999]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) | |
DROP FUNCTION [dbo].[ufn_RiskGail1999] | |
GO | |
USE [UCI_Athena] | |
GO | |
/****** Object: UserDefinedFunction [dbo].[ufn_RiskGail1999] Script Date: 03/27/2012 13:45:05 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
-- ============================================= | |
-- Author: Richard Kelly | |
-- Organization: University of California | |
-- Create date: 3/27/2012 | |
-- Description: Function to calculate breast cancer risk based on Gail model described in: | |
-- Gail MH, Costantino JP, Bryant J, et al. Weighing the risks and the benefits of -- tamoxifen treatment for preventing breast cancer. J Natl Cancer Inst 1999; -- 91:1829-1846. Vol. 91, No. 21, November 3, 1999 | |
-- http://university.asco.org/sites/university.asco.org/files/ClassicReferences_Breast_General%20References_3..pdf | |
-- Usage: SELECT dbo.ufn_RiskGail1999(<agemenarche>,<age>,<numbiopsies>,<ageflb>,<numrels>, | |
-- <atypiabiopsies>,<race>,<resulttype>) | |
-- ============================================= | |
CREATE FUNCTION [dbo].[ufn_RiskGail1999] | |
( | |
@agemenarche int -- Age at menarche | |
,@age int -- Age at counseling | |
,@numbiopsies int -- Number of breast biopsies | |
,@ageflb int -- Age at first live birth (-1 if nulliparous) | |
,@numrels int -- Number of first degree relatives with breast cancer | |
,@atypia int -- Atypical hyperplasia category | |
-- 1 No biopsies | |
-- 2 At least one biopsy and no atypical hyperplasia found in any biopsy specimen | |
-- 3 No atypical hyperplasia found and hyperplasia status unknown for at least one biopsy specimen | |
-- 4 Atypical hyperplasia found in at least one biopsy specimen | |
,@race int -- Race category | |
-- 1 Black | |
-- 2 Not black | |
,@resulttype int -- Result category | |
-- 1 Relative risk | |
-- 2 Projected 5-year risk | |
) | |
RETURNS real | |
AS | |
BEGIN | |
DECLARE @factora real | |
SELECT @factora = CASE | |
WHEN @agemenarche >= 14 THEN 1 | |
WHEN @agemenarche >= 12 AND @agemenarche <=13 THEN 1.1 | |
WHEN @agemenarche < 12 THEN 1.21 | |
ELSE '@agemenarche out of bounds' | |
END | |
DECLARE @factorb real | |
SELECT @factorb = CASE | |
WHEN @age < 50 THEN CASE | |
WHEN @numbiopsies =0 THEN 1 | |
WHEN @numbiopsies =1 THEN 1.7 | |
WHEN @numbiopsies >=2 THEN 2.88 | |
ELSE '@age<50 @numbiopsies out of bounds' | |
END | |
ELSE CASE | |
WHEN @numbiopsies =0 THEN 1 | |
WHEN @numbiopsies =1 THEN 1.27 | |
WHEN @numbiopsies >=2 THEN 1.62 | |
ELSE '@age>=50 @numbiopsies out of bounds' | |
END | |
END | |
DECLARE @factorc real | |
SELECT @factorc = CASE | |
WHEN @ageflb >0 AND @ageflb <20 THEN | |
CASE | |
WHEN @numrels =0 THEN 1 | |
WHEN @numrels =1 THEN 1 | |
WHEN @numrels >=2 THEN 6.8 | |
ELSE '@ageflb<20 @numrels out of bounds' | |
END | |
WHEN @ageflb >=20 AND @ageflb <=24 THEN | |
CASE | |
WHEN @numrels =0 THEN 1.24 | |
WHEN @numrels =1 THEN 2.68 | |
WHEN @numrels >=2 THEN 5.78 | |
ELSE '@ageflb<=24 @numrels out of bounds' | |
END | |
WHEN ( @ageflb >=25 AND @ageflb <=29 ) OR @ageflb =-1 THEN | |
CASE | |
WHEN @numrels =0 THEN 1.55 | |
WHEN @numrels =1 THEN 2.76 | |
WHEN @numrels >=2 THEN 4.91 | |
ELSE '@ageflb<=29 @numrels out of bounds' | |
END | |
WHEN @ageflb >=30 THEN | |
CASE | |
WHEN @numrels =0 THEN 1.93 | |
WHEN @numrels =1 THEN 2.83 | |
WHEN @numrels >=2 THEN 4.17 | |
ELSE '@ageflb>=30 @numrels out of bounds' | |
END | |
ELSE '@ageflb out of bounds' | |
END | |
DECLARE @factord real | |
SELECT @factord = CASE | |
WHEN @atypia =1 THEN 1 | |
WHEN @atypia =2 THEN .93 | |
WHEN @atypia =3 THEN 1 | |
WHEN @atypia =4 THEN 1.82 | |
ELSE '@atypia out of bounds' | |
END | |
DECLARE @baselinefiveyrpct real | |
SELECT @baselinefiveyrpct = CASE | |
WHEN @age >=20 and @age <=24 THEN | |
CASE | |
WHEN @race =1 THEN 0.014 | |
WHEN @race =2 THEN 0.012 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=25 and @age <=29 THEN | |
CASE | |
WHEN @race =1 THEN 0.050 | |
WHEN @race =2 THEN 0.049 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=30 and @age <=34 THEN | |
CASE | |
WHEN @race =1 THEN 0.120 | |
WHEN @race =2 THEN 0.134 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=35 and @age <=39 THEN | |
CASE | |
WHEN @race =1 THEN 0.224 | |
WHEN @race =2 THEN 0.278 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=40 and @age <=44 THEN | |
CASE | |
WHEN @race =1 THEN 0.310 | |
WHEN @race =2 THEN 0.450 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=45 and @age <=49 THEN | |
CASE | |
WHEN @race =1 THEN 0.355 | |
WHEN @race =2 THEN 0.584 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=50 and @age <=54 THEN | |
CASE | |
WHEN @race =1 THEN 0.416 | |
WHEN @race =2 THEN 0.703 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=55 and @age <=59 THEN | |
CASE | |
WHEN @race =1 THEN 0.511 | |
WHEN @race =2 THEN 0.859 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=60 and @age <=64 THEN | |
CASE | |
WHEN @race =1 THEN 0.562 | |
WHEN @race =2 THEN 1.018 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=65 and @age <=69 THEN | |
CASE | |
WHEN @race =1 THEN 0.586 | |
WHEN @race =2 THEN 1.116 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=70 and @age <=74 THEN | |
CASE | |
WHEN @race =1 THEN 0.646 | |
WHEN @race =2 THEN 1.157 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=75 and @age <=79 THEN | |
CASE | |
WHEN @race =1 THEN 0.713 | |
WHEN @race =2 THEN 1.140 | |
ELSE '@race out of bounds' | |
END | |
WHEN @age >=80 and @age <=84 THEN | |
CASE | |
WHEN @race =1 THEN 0.659 | |
WHEN @race =2 THEN 1.006 | |
ELSE '@race out of bounds' | |
END | |
END | |
DECLARE @riskrelative real | |
SELECT @riskrelative = @factora * @factorb * @factorc * @factord | |
DECLARE @riskfiveyrpct real | |
SELECT @riskfiveyrpct = @riskrelative * @baselinefiveyrpct | |
DECLARE @result real | |
SELECT @result = CASE @resulttype WHEN 1 THEN @riskrelative WHEN 2 THEN @riskfiveyrpct END | |
RETURN @result | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Updated two risk values from 1989 values to 1999.