Calculating Kaplan Meier Survival Curves and Their Confidence Intervals in SQL Server
Written by Peter Rosenmai on 1 Jan 2016.
I provide here a SQL Server script to calculate Kaplan Meier survival curves and their confidence
intervals (plain, log and log-log) for time-to-event data.
Example 1: Customer Attrition, Ungrouped, Without Censoring
Suppose a web-application company has seen its ten customers cancel their subscriptions after
0.5, 1, 10, 10, 11, 13.5, 14, 19, 19.5 and 30 months (from the start of their respective subscriptions). Based on this
data, we want to estimate the probability of a new
customer remaining a customer more than, say, 12 months—and we want a confidence interval around that estimate.
We create the input table:
create table #input
(
[Group] nvarchar(255),
[TimeToEvent] float,
[Event] int
);
insert into #input values('Web-App Ltd', 0.5, 1);
insert into #input values('Web-App Ltd', 1.0, 1);
insert into #input values('Web-App Ltd', 10.0, 1);
insert into #input values('Web-App Ltd', 10.0, 1);
insert into #input values('Web-App Ltd', 11.0, 1);
insert into #input values('Web-App Ltd', 13.5, 1);
insert into #input values('Web-App Ltd', 14.0, 1);
insert into #input values('Web-App Ltd', 19.0, 1);
insert into #input values('Web-App Ltd', 19.5, 1);
insert into #input values('Web-App Ltd', 30.0, 1);
As our data is ungrouped—see example three, below, for a grouped example—it doesn't matter what value we put in the Group
column so long as it isn't null and is the same for all rows. (I've used "Web-App Ltd".)
Having run the script, below, we output the Kaplan Meier estimate from the resulting table:
select
[TimeToEvent],
[SurvivalProb]
from
#output
order by
[TimeToEvent];
TimeToEvent SurvivalProb
0.5 0.9
1.0 0.8
10.0 0.6
11.0 0.5
13.5 0.4
14.0 0.3
19.0 0.2
19.5 0.1
30.0 0.0
Here's a graph of the above output:
As you can see, Kaplan Meier survival curves are stepwise functions from [0, x] to [0, 1] where x≥0.
And (0, 1) is always included in the curve.
The graph tells us, for example, that the Kaplan Meier estimate of the probability of a new
customer remaining a customer more than 12 months is 0.5:
Let's get a 95% confidence band for that curve:
select
[TimeToEvent],
[SurvivalProb],
[LCI95LogSurvivalProb],
[UCI95LogSurvivalProb]
from
#output
order by
[TimeToEvent];
TimeToEvent SurvivalProb LCI95LogSurvivalProb UCI95LogSurvivalProb
0.5 0.9 0.732 1.000
1.0 0.8 0.587 1.000
10.0 0.6 0.362 0.995
11.0 0.5 0.269 0.929
13.5 0.4 0.187 0.855
14.0 0.3 0.116 0.773
19.0 0.2 0.057 0.691
19.5 0.1 0.016 0.642
30.0 0.0 0.000 NULL
And let's graph that confidence band:
This gives us for the above estimate a 95% confidence interval of [0.269, 0.929]. That is, we estimate that the probability of
a new customer remaining a customer more than 12 months is 0.5 and we are 95% confident that the true probability is between
0.269 and 0.929. Here's a graph:
Example 2: Customer Attrition, Ungrouped, With Censoring
Building on the previous example, suppose the web-application company has, in addition to the customers who have cancelled their
subscriptions, one customer who is still a customer after three months and another who is still a customer after ten months. So the
customers to date have cancelled after 0.5, 1, 3+, 10, 10, 10+, 11, 13.5, 14, 19, 19.5 and 30 months, where "x+" means
"unknown, but more than x months" (i.e. censored at x months).
We set Event to 0 in the input table for the censored points:
create table #input
(
[Group] nvarchar(255),
[TimeToEvent] float,
[Event] int
);
insert into #input values('Web-App Ltd', 0.5, 1);
insert into #input values('Web-App Ltd', 1, 1);
insert into #input values('Web-App Ltd', 3, 0);
insert into #input values('Web-App Ltd', 10, 1);
insert into #input values('Web-App Ltd', 10, 1);
insert into #input values('Web-App Ltd', 10, 0);
insert into #input values('Web-App Ltd', 11, 1);
insert into #input values('Web-App Ltd', 13.5, 1);
insert into #input values('Web-App Ltd', 14, 1);
insert into #input values('Web-App Ltd', 19, 1);
insert into #input values('Web-App Ltd', 19.5, 1);
insert into #input values('Web-App Ltd', 30, 1);
As in the previous example, our data is ungrouped, so it doesn't matter what value we put in the Group
column provided it is not null and is the same for all rows.
Once again, we run the script (see below) and output the results:
select
[TimeToEvent],
[SurvivalProb],
[LCI95LogSurvivalProb],
[UCI95LogSurvivalProb]
from
#output order by [TimeToEvent];
TimeToEvent SurvivalProb LCI95LogSurvivalProb UCI95LogSurvivalProb
0.5 0.917 0.773 1.000
1.0 0.833 0.647 1.000
10.0 0.648 0.421 0.998
11.0 0.540 0.308 0.946
13.5 0.432 0.212 0.880
14.0 0.324 0.131 0.804
19.0 0.216 0.064 0.725
19.5 0.108 0.017 0.680
30.0 0.000 0.000 NULL
As you can see, the Kaplan Meier estimates and their confidence intervals are slightly different to those shown
in the previous example. That is reflected also (look carefully!) in this graph:
Note how the above graph is slightly different to the last graph shown in the previous example. That's due to the extra two (censored) points.
Example 3: Customer Attrition, Grouped, With Censoring
Let's now suppose that we're comparing time to subscription cancellation for two different types of customer: Corporations and small businesses.
The corporations have these times to cancellation: 0, 0+, 2, 3, 6, 6, 7.5, 8, 8, 8, 9, 11+, 13, 14, 19.
And the small businesses have these times to cancellation: 1, 1.5, 3, 3.5+, 4, 4 and 6+.
We use the Group column of the input table to differentiate the two datasets:
create table #input
(
[Group] nvarchar(255),
[TimeToEvent] float,
[Event] int
);
insert into #input values('Corporation', 0.0, 1);
insert into #input values('Corporation', 0.0, 0);
insert into #input values('Corporation', 2.0, 1);
insert into #input values('Corporation', 3.0, 1);
insert into #input values('Corporation', 6.0, 1);
insert into #input values('Corporation', 6.0, 1);
insert into #input values('Corporation', 7.5, 1);
insert into #input values('Corporation', 8.0, 1);
insert into #input values('Corporation', 8.0, 1);
insert into #input values('Corporation', 8.0, 1);
insert into #input values('Corporation', 9.0, 1);
insert into #input values('Corporation', 11.0, 0);
insert into #input values('Corporation', 13.0, 1);
insert into #input values('Corporation', 14.0, 1);
insert into #input values('Corporation', 19.0, 1);
insert into #input values('Small Business', 1.0, 1);
insert into #input values('Small Business', 1.5, 1);
insert into #input values('Small Business', 3.0, 1);
insert into #input values('Small Business', 3.5, 0);
insert into #input values('Small Business', 4.0, 1);
insert into #input values('Small Business', 4.0, 1);
insert into #input values('Small Business', 6.0, 0);
We run our script (see below) as before and output the results:
select
[Group],
[TimeToEvent],
[AtRisk],
[Deaths] as [CustomerLosses],
[SurvivalProb],
[GWStdErrSurvivalProb] as [StdError],
[LCI95LogSurvivalProb],
[UCI95LogSurvivalProb]
from
#output
order by
[Group],
[TimeToEvent];
Group TimeToEvent AtRisk CustomerLosses SurvivalProb StdError LCI95LogSurvivalProb UCI95LogSurvivalProb
Corporation 0.0 15 1 0.933 0.0644 0.8153 1.0000
Corporation 2.0 13 1 0.862 0.0911 0.7003 1.0000
Corporation 3.0 12 1 0.790 0.1081 0.6039 1.0000
Corporation 6.0 11 2 0.646 0.1275 0.4389 0.9513
Corporation 7.5 9 1 0.574 0.1320 0.3660 0.9013
Corporation 8.0 8 3 0.359 0.1283 0.1781 0.7234
Corporation 9.0 5 1 0.287 0.1211 0.1257 0.6563
Corporation 13.0 3 1 0.191 0.1124 0.0606 0.6049
Corporation 14.0 2 1 0.096 0.0880 0.0158 0.5798
Corporation 19.0 1 1 0.000 NULL 0.0000 NULL
Small Business 1.0 7 1 0.857 0.1323 0.6334 1.0000
Small Business 1.5 6 1 0.714 0.1707 0.4471 1.0000
Small Business 3.0 5 1 0.571 0.1870 0.3008 1.0000
Small Business 4.0 3 2 0.190 0.1676 0.0340 1.0000
Here's a graph of the two survival curves and their confidence intervals:
Comparing With R
Let's replicate example three, above, in R. Here's the code:
require(survival)
df_surv <- data.frame(months = c(0, 0, 2, 3, 6, 6, 7.5, 8, 8, 8, 9, 11, 13, 14, 19, 1, 1.5, 3, 3.5, 4, 4, 6),
status = c(1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0),
group = c(rep('Corporation', 15), rep('Small Business', 7)))
fit <- survfit(Surv(months, status) ~ group, data=df_surv)
summary(fit)
plot(fit, conf.int=TRUE, col=c("red", "blue"),
main="Kaplan Meier Survival Curve for Customer Loss",
sub ="Corporate=Red, Small Business=Blue",
xlab="Months to Customer Loss",
ylab="Probability")
The output:
Call: survfit(formula = Surv(months, status) ~ group, data=df_surv)
group=Corporation
time n.risk n.event survival std.err lower 95% CI upper 95% CI
0.0 15 1 0.9333 0.0644 0.8153 1.000
2.0 13 1 0.8615 0.0911 0.7003 1.000
3.0 12 1 0.7897 0.1081 0.6039 1.000
6.0 11 2 0.6462 0.1275 0.4389 0.951
7.5 9 1 0.5744 0.1320 0.3660 0.901
8.0 8 3 0.3590 0.1283 0.1781 0.723
9.0 5 1 0.2872 0.1211 0.1257 0.656
13.0 3 1 0.1915 0.1124 0.0606 0.605
14.0 2 1 0.0957 0.0880 0.0158 0.580
19.0 1 1 0.0000 NaN NA NA
group=Small Business
time n.risk n.event survival std.err lower 95% CI upper 95% CI
1.0 7 1 0.857 0.132 0.633 1
1.5 6 1 0.714 0.171 0.447 1
3.0 5 1 0.571 0.187 0.301 1
4.0 3 2 0.190 0.168 0.034 1
As you can see, the output matches that from SQL, above. And the plot matches that which we produced at the end of example three.
(Note that the survival package marks times at which censoring occurs in the survival curves with crosses.)
By default the survfit function produces 95% confidence intervals based on the log(survival) function. To get confidence intervals based on the
survival function, set the conf.type argument to "plain"; to get confidence intervals based on the log(-log(survival)) function, set it
to "log-log". And to get, say, 80% rather than 95% confidence intervals, set conf.int=0.80.
For example, the confidence intervals produced by this call will match the LCI50PlainSurvivalProb and UCI50PlainSurvivalProb columns:
fit <- survfit(Surv(months, status) ~ group, data=df_surv, conf.type="plain", conf.int=0.50)
Output Columns
The SQL script (below) produces a lot of columns:
| Group | The group (e.g. "Corporation" or "Small Business"). |
| TimeToEvent | Time to death/event or non-death/non-event exit. A non-negative float. |
| Deaths | The number of deaths/events at this point in time. E.g. customer losses. |
| NonDeathExits | The number of subjects who leave the study for a reason other than death/event at this point in time. |
| PriorDeaths | Deaths or events prior to this point in time. |
| PriorNonDeathExits | Subjects who left the study for a reason other than death/event prior to this point in time. |
| AtRisk | The number of subjects at risk of death/event at this point in time. |
| ConditionalSurvivalProb | The estimated probability of a subject surviving past this point in time given that he/she has survived to this point in time. |
| SurvivalProb | The Kaplan Meier survival estimate, i.e. the estimated probability of a subject surviving beyond this point in time. |
| GreenwoodSum | Used in calculations—see the code. |
| GWStdErrSurvivalProb | The standard error to be used to produce confidence intervals around the SurvivalProb. |
| GWStdErrLogSurvivalProb | Used in calculation of the confidence intervals based on the log(survival) function. |
| GWStdErrLogLogSurvivalProb | Used in calculation of the confidence intervals based on the log(-log(survival)) function. |
| LCI50PlainSurvivalProb | Lower 50% confidence interval, calculated from the survival function. |
| UCI50PlainSurvivalProb | Upper 50% confidence interval, calculated from the survival function. |
| LCI80PlainSurvivalProb | Lower 80% confidence interval, calculated from the survival function. |
| UCI80PlainSurvivalProb | Upper 80% confidence interval, calculated from the survival function. |
| LCI90PlainSurvivalProb | Lower 90% confidence interval, calculated from the survival function. |
| UCI90PlainSurvivalProb | Upper 90% confidence interval, calculated from the survival function. |
| LCI95PlainSurvivalProb | Lower 95% confidence interval, calculated from the survival function. |
| UCI95PlainSurvivalProb | Upper 95% confidence interval, calculated from the survival function. |
| LCI99PlainSurvivalProb | Lower 99% confidence interval, calculated from the survival function. |
| UCI99PlainSurvivalProb | Upper 99% confidence interval, calculated from the survival function. |
| LCI50LogSurvivalProb | Lower 50% confidence interval, calculated from the log(survival) function. |
| UCI50LogSurvivalProb | Upper 50% confidence interval, calculated from the log(survival) function. |
| LCI80LogSurvivalProb | Lower 80% confidence interval, calculated from the log(survival) function. |
| UCI80LogSurvivalProb | Upper 80% confidence interval, calculated from the log(survival) function. |
| LCI90LogSurvivalProb | Lower 90% confidence interval, calculated from the log(survival) function. |
| UCI90LogSurvivalProb | Upper 90% confidence interval, calculated from the log(survival) function. |
| LCI95LogSurvivalProb | Lower 95% confidence interval, calculated from the log(survival) function. |
| UCI95LogSurvivalProb | Upper 95% confidence interval, calculated from the log(survival) function. |
| LCI99LogSurvivalProb | Lower 99% confidence interval, calculated from the log(survival) function. |
| UCI99LogSurvivalProb | Upper 99% confidence interval, calculated from the log(survival) function. |
| LCI50LogLogSurvivalProb | Lower 50% confidence interval, calculated from the log(-log(survival)) function. |
| UCI50LogLogSurvivalProb | Upper 50% confidence interval, calculated from the log(-log(survival)) function. |
| LCI80LogLogSurvivalProb | Lower 80% confidence interval, calculated from the log(-log(survival)) function. |
| UCI80LogLogSurvivalProb | Upper 80% confidence interval, calculated from the log(-log(survival)) function. |
| LCI90LogLogSurvivalProb | Lower 90% confidence interval, calculated from the log(-log(survival)) function. |
| UCI90LogLogSurvivalProb | Upper 90% confidence interval, calculated from the log(-log(survival)) function. |
| LCI95LogLogSurvivalProb | Lower 95% confidence interval, calculated from the log(-log(survival)) function. |
| UCI95LogLogSurvivalProb | Upper 95% confidence interval, calculated from the log(-log(survival)) function. |
| LCI99LogLogSurvivalProb | Lower 99% confidence interval, calculated from the log(-log(survival)) function. |
| UCI99LogLogSurvivalProb | Upper 99% confidence interval, calculated from the log(-log(survival)) function. |
Note that the confidence intervals based on the log(survival) function seem to be the most commonly used. They're also the default used by the
survfit function in R's survival package.
The Script
with
partial_calcs_1 as
(
-- Count the deaths and non-death exits at each supplied time point.
select
[Group],
[TimeToEvent],
sum([Event]) as [Deaths],
sum(1 - [Event]) as [NonDeathExits]
from
#input
group by
[Group],
[TimeToEvent]
),
partial_calcs_2 as
(
-- Cumulate the deaths and the non-death exits.
select
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits],
coalesce(sum(pc2.[Deaths]), 0) as [PriorDeaths],
coalesce(sum(pc2.[NonDeathExits]), 0) as [PriorNonDeathExits]
from
partial_calcs_1 pc1
left outer join
partial_calcs_1 pc2
on
pc1.[Group] = pc2.[Group]
and pc1.[TimeToEvent] > pc2.[TimeToEvent]
group by
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits]
),
partial_calcs_3 as
(
-- Determine how many subjects were at risk a moment (a delta) before each time point.
select
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits],
pc1.[PriorDeaths],
pc1.[PriorNonDeathExits],
sum(pc2.[Deaths]) + sum(pc2.[NonDeathExits]) - pc1.[PriorDeaths] - pc1.[PriorNonDeathExits] as [AtRisk]
from
partial_calcs_2 pc1
left outer join
partial_calcs_2 pc2
on
pc1.[Group] = pc2.[Group]
group by
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits],
pc1.[PriorDeaths],
pc1.[PriorNonDeathExits]
),
partial_calcs_4 as
(
-- Estimate the probability of surviving past each time point given that a subject has survived to that point.
-- Also create the terms used in the calculation of the Greenwood sums.
select
*,
case
when [AtRisk] = 0 then null
else ([AtRisk] - [Deaths]) / cast([AtRisk] as float)
end as [ConditionalSurvivalProb],
case
when [AtRisk] * ([AtRisk] - [Deaths]) = 0 then null
else cast([Deaths] as float) / ([AtRisk] * ([AtRisk] - [Deaths]))
end as [Greenwood]
from
partial_calcs_3
),
partial_calcs_5 as
(
-- Calculate the Kaplan-Meier survival estimates and the Greenwood sums.
select
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits],
pc1.[PriorDeaths],
pc1.[PriorNonDeathExits],
pc1.[AtRisk],
pc1.[ConditionalSurvivalProb],
case -- Here we use the fact that a_1 * a_2 * ... * a_n = exp(ln(a_1) + ln(a_2) + ... + ln(a_n))
when min(pc2.[ConditionalSurvivalProb]) = 0 then 0
else exp(sum(case when pc2.[ConditionalSurvivalProb] = 0 then 0 else log(pc2.[ConditionalSurvivalProb]) end))
end as [SurvivalProb],
sum(pc2.[Greenwood]) as [GreenwoodSum]
from
partial_calcs_4 pc1
join
partial_calcs_4 pc2
on
pc1.[Group] = pc2.[Group]
and pc1.[TimeToEvent] >= pc2.[TimeToEvent]
group by
pc1.[Group],
pc1.[TimeToEvent],
pc1.[Deaths],
pc1.[NonDeathExits],
pc1.[PriorDeaths],
pc1.[PriorNonDeathExits],
pc1.[AtRisk],
pc1.[ConditionalSurvivalProb]
),
partial_calcs_6 as
(
-- Calculate the statistics used to determine confidence intervals for the survival probability estimates.
select
*,
case
when [SurvivalProb] = 0 then null
else sqrt([GreenwoodSum]) * [SurvivalProb]
end as [GWStdErrSurvivalProb],
sqrt([GreenwoodSum]) as [GWStdErrLogSurvivalProb],
case
when [SurvivalProb] = 0 then null
else sqrt([GreenwoodSum]) / log([SurvivalProb])
end as [GWStdErrLogLogSurvivalProb]
from
partial_calcs_5
)
select
-- Calculate the plain, log and log-log confidence intervals (50%, 80%, 90%, 95% and 99%).
*,
-- The plain confidence intervals.
case
when [SurvivalProb] - 0.674490 * [GWStdErrSurvivalProb] < 0 then 0
else [SurvivalProb] - 0.674490 * [GWStdErrSurvivalProb]
end as [LCI50PlainSurvivalProb],
case
when [SurvivalProb] + 0.674490 * [GWStdErrSurvivalProb] > 1 then 1
else [SurvivalProb] + 0.674490 * [GWStdErrSurvivalProb]
end as [UCI50PlainSurvivalProb],
case
when [SurvivalProb] - 1.281552 * [GWStdErrSurvivalProb] < 0 then 0
else [SurvivalProb] - 1.281552 * [GWStdErrSurvivalProb]
end as [LCI80PlainSurvivalProb],
case
when [SurvivalProb] + 1.281552 * [GWStdErrSurvivalProb] > 1 then 1
else [SurvivalProb] + 1.281552 * [GWStdErrSurvivalProb]
end as [UCI80PlainSurvivalProb],
case
when [SurvivalProb] - 1.644854 * [GWStdErrSurvivalProb] < 0 then 0
else [SurvivalProb] - 1.644854 * [GWStdErrSurvivalProb]
end as [LCI90PlainSurvivalProb],
case
when [SurvivalProb] + 1.644854 * [GWStdErrSurvivalProb] > 1 then 1
else [SurvivalProb] + 1.644854 * [GWStdErrSurvivalProb]
end as [UCI90PlainSurvivalProb],
case
when [SurvivalProb] - 1.959964 * [GWStdErrSurvivalProb] < 0 then 0
else [SurvivalProb] - 1.959964 * [GWStdErrSurvivalProb]
end as [LCI95PlainSurvivalProb],
case
when [SurvivalProb] + 1.959964 * [GWStdErrSurvivalProb] > 1 then 1
else [SurvivalProb] + 1.959964 * [GWStdErrSurvivalProb]
end as [UCI95PlainSurvivalProb],
case
when [SurvivalProb] - 2.575829 * [GWStdErrSurvivalProb] < 0 then 0
else [SurvivalProb] - 2.575829 * [GWStdErrSurvivalProb]
end as [LCI99PlainSurvivalProb],
case
when [SurvivalProb] + 2.575829 * [GWStdErrSurvivalProb] > 1 then 1
else [SurvivalProb] + 2.575829 * [GWStdErrSurvivalProb]
end as [UCI99PlainSurvivalProb],
-- The log confidence intervals.
case
when [SurvivalProb] = 0 then 0
when exp(log([SurvivalProb]) - 0.674490 * [GWStdErrLogSurvivalProb]) < 0 then 0
else exp(log([SurvivalProb]) - 0.674490 * [GWStdErrLogSurvivalProb])
end as [LCI50LogSurvivalProb],
case
when [SurvivalProb] = 0 then null
when exp(log([SurvivalProb]) + 0.674490 * [GWStdErrLogSurvivalProb]) > 1 then 1
else exp(log([SurvivalProb]) + 0.674490 * [GWStdErrLogSurvivalProb])
end as [UCI50LogSurvivalProb],
case
when [SurvivalProb] = 0 then 0
when exp(log([SurvivalProb]) - 1.281552 * [GWStdErrLogSurvivalProb]) < 0 then 0
else exp(log([SurvivalProb]) - 1.281552 * [GWStdErrLogSurvivalProb])
end as [LCI80LogSurvivalProb],
case
when [SurvivalProb] = 0 then null
when exp(log([SurvivalProb]) + 1.281552 * [GWStdErrLogSurvivalProb]) > 1 then 1
else exp(log([SurvivalProb]) + 1.281552 * [GWStdErrLogSurvivalProb])
end as [UCI80LogSurvivalProb],
case
when [SurvivalProb] = 0 then 0
when exp(log([SurvivalProb]) - 1.644854 * [GWStdErrLogSurvivalProb]) < 0 then 0
else exp(log([SurvivalProb]) - 1.644854 * [GWStdErrLogSurvivalProb])
end as [LCI90LogSurvivalProb],
case
when [SurvivalProb] = 0 then null
when exp(log([SurvivalProb]) + 1.644854 * [GWStdErrLogSurvivalProb]) > 1 then 1
else exp(log([SurvivalProb]) + 1.644854 * [GWStdErrLogSurvivalProb])
end as [UCI90LogSurvivalProb],
-- The log-log confidence intervals.
case
when [SurvivalProb] = 0 then 0
when exp(log([SurvivalProb]) - 1.959964 * [GWStdErrLogSurvivalProb]) < 0 then 0
else exp(log([SurvivalProb]) - 1.959964 * [GWStdErrLogSurvivalProb])
end as [LCI95LogSurvivalProb],
case
when [SurvivalProb] = 0 then null
when exp(log([SurvivalProb]) + 1.959964 * [GWStdErrLogSurvivalProb]) > 1 then 1
else exp(log([SurvivalProb]) + 1.959964 * [GWStdErrLogSurvivalProb])
end as [UCI95LogSurvivalProb],
case
when [SurvivalProb] = 0 then 0
when exp(log([SurvivalProb]) - 2.575829 * [GWStdErrLogSurvivalProb]) < 0 then 0
else exp(log([SurvivalProb]) - 2.575829 * [GWStdErrLogSurvivalProb])
end as [LCI99LogSurvivalProb],
case
when [SurvivalProb] = 0 then null
when exp(log([SurvivalProb]) + 2.575829 * [GWStdErrLogSurvivalProb]) > 1 then 1
else exp(log([SurvivalProb]) + 2.575829 * [GWStdErrLogSurvivalProb])
end as [UCI99LogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) - 0.674490 * [GWStdErrLogLogSurvivalProb])) < 0 then 0
else exp(-exp(log(-log([SurvivalProb])) - 0.674490 * [GWStdErrLogLogSurvivalProb]))
end as [LCI50LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) + 0.674490 * [GWStdErrLogLogSurvivalProb])) > 1 then 1
else exp(-exp(log(-log([SurvivalProb])) + 0.674490 * [GWStdErrLogLogSurvivalProb]))
end as [UCI50LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) - 1.281552 * [GWStdErrLogLogSurvivalProb])) < 0 then 0
else exp(-exp(log(-log([SurvivalProb])) - 1.281552 * [GWStdErrLogLogSurvivalProb]))
end as [LCI80LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) + 1.281552 * [GWStdErrLogLogSurvivalProb])) > 1 then 1
else exp(-exp(log(-log([SurvivalProb])) + 1.281552 * [GWStdErrLogLogSurvivalProb]))
end as [UCI80LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) - 1.644854 * [GWStdErrLogLogSurvivalProb])) < 0 then 0
else exp(-exp(log(-log([SurvivalProb])) - 1.644854 * [GWStdErrLogLogSurvivalProb]))
end as [LCI90LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) + 1.644854 * [GWStdErrLogLogSurvivalProb])) > 1 then 1
else exp(-exp(log(-log([SurvivalProb])) + 1.644854 * [GWStdErrLogLogSurvivalProb]))
end as [UCI90LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) - 1.959964 * [GWStdErrLogLogSurvivalProb])) < 0 then 0
else exp(-exp(log(-log([SurvivalProb])) - 1.959964 * [GWStdErrLogLogSurvivalProb]))
end as [LCI95LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) + 1.959964 * [GWStdErrLogLogSurvivalProb])) > 1 then 1
else exp(-exp(log(-log([SurvivalProb])) + 1.959964 * [GWStdErrLogLogSurvivalProb]))
end as [UCI95LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) - 2.575829 * [GWStdErrLogLogSurvivalProb])) < 0 then 0
else exp(-exp(log(-log([SurvivalProb])) - 2.575829 * [GWStdErrLogLogSurvivalProb]))
end as [LCI99LogLogSurvivalProb],
case
when [SurvivalProb] in (0, 1) then null
when exp(-exp(log(-log([SurvivalProb])) + 2.575829 * [GWStdErrLogLogSurvivalProb])) > 1 then 1
else exp(-exp(log(-log([SurvivalProb])) + 2.575829 * [GWStdErrLogLogSurvivalProb]))
end as [UCI99LogLogSurvivalProb]
into
#output
from
partial_calcs_6
where
[deaths] > 0;
