Standardised Mortality/Morbidity Ratios!
andy...@gmail.com
I need to calculate standardised mortality ratio and standardised
morbidity ratios (for nine conditions!) for my study group over a ten-
year period, using indirect standardisation.
For the study group, each case has sex/age/date of entry/date of death
(if diseased); and for each condition, there are yes/no and date of
event;
for the reference population, I have yearly age and sex matched rates
(in excel tables).
As I am only new to this SYNTAX world, I have been pasting over the
SPSS crosstab into excel and mannually calculated each outcome. It
takes me ages to compute and not efficient at all!
I wonder if there is any syntax/macro would help me to get the SMRs
and 95% CIs. Because, now I am adjusting for more factors, doing
everything by excel formular simply not wise and sometimes goes wrong
easily.
I have searched in this group and in google advance, but nothing
really pertinent to the soluction came out.
Thanks.
Ng
mcap
There should be a way to do this in SPSS (someone else probably can
think of one off the top of their heads). Can you post a sample chart
with a little of your data (change the numbers if it's
confidential)?
You may have to aggregate or use the OMS but I need to see an example
of what you have.
Also, if you have STATA you may want to look at their tables for
epidemiologists options. Those are a bit easier. SPSS is not so
great with epidemiological data.
andy...@gmail.com
reflect my data:
SPSS file with individual leve data:
raniomid sex dt_birth dt_death entry_dt cond1 dt_cond1 con2 dt_con2
thres_dt cat
354 1 01.09.1904 01.08.2002 18.11.1998 1 01.07.1993 0 1
936 1 01.02.1905 01.08.2004 04.12.2003 0 0 1
855 2 01.01.1908 01.08.2006 06.08.2001 1 01.06.1999 0 1
688 1 01.04.1908 01.08.2006 06.04.2005 1 01.10.1997 0 1
74 2 01.08.1908 01.08.2004 05.01.1998 1 01.07.1996 1 01.07.1993 0
748 2 01.04.1910 01.08.2005 18.04.1996 1 01.05.1995 0 0
737 1 01.08.1910 14.10.2002 1 01.08.2000 0 1
984 2 01.08.1910 21.05.2001 1 01.01.1992 0 1
986 1 01.09.1911 01.08.2006 28.07.1999 0 0 0
492 2 01.01.1912 01.08.2005 15.09.1997 1 01.01.1999 0 1
81 2 01.02.1912 01.08.2004 14.09.2000 1 01.08.1994 0 1
450 2 01.09.1912 01.08.2006 22.08.2000 1 01.11.1998 0 1
123 2 01.04.1913 01.08.2006 22.04.1999 1 01.01.2000 0 0
781 2 01.06.1913 01.08.2003 20.05.1996 0 0 0
359 1 01.08.1913 21.09.2005 1 01.01.1997 0 1
207 2 01.01.1914 11.09.2006 1 01.08.2001 0 1
308 2 01.02.1914 13.01.2003 1 01.01.2001 0 1
201 2 01.05.1914 03.09.2002 1 01.03.2001 0 0
excel with population reference (male example only)
reference population
male
age\year 1996 1997 1998 1999 2000 2001
0-19 5800 5900 6100 6304 6500 6800
20-39 6000 6100 6340 6601 6890 7120
40-59 8000 8300 8569 8600 8900 9020
60-79 7600 7800 7989 8209 8320 8302
80+ 4000 4030 4203 4250 4450 4320
total 31400 32130 33201 33964 35060 35562
observed condition 1 in reference population
male
age\year 1996 1997 1998 1999 2000 2001
0-19 3 4 6 2 1 6
20-39 9 7 7 10 11 14
40-59 18 12 17 15 16 20
60-79 30 27 36 30 42 23
80+ 33 36 29 20 45 32
total 93 86 95 77 115 95
observed condition 1 in reference population
male
age\year 1996 1997 1998 1999 2000 2001
0-19 2 3 1 4 6 4
20-39 7 5 6 7 9 12
40-59 12 11 13 9 8 13
60-79 16 15 17 20 19 20
80+ 21 23 24 26 25 27
total 58 57 61 66 67 76
I used to create dozens of variables in SPSS (e.g. agegroup at death/
cond1/2,
year of cond1/2) in order to get crosstab and then use excel to
calculate the result (e.g. sumproduct etc.).
Now I want to know more about the underlying differences by further
adjusting the data (e.g. using thres date to exclude pre-exsiting
cond, or create subgroup using cat1). My ancient methods really give
me an heartattack.
I have access to SPSS and SAS only (but no experience in using macro).
My sincere apology of my inability, the investment/health economics
background could not ba any help on using syntax/macros.
Hope this is clear, i am happy to email you with more detailed (but
artificial) data in spss if it helps.
Ng
mcap
Give me a really specific example of one thing you would like to look
and I will give you an example of syntax. It isn't totally clear what
you want to do. You have a lot of data here. Are you looking at time
to event? Or are you just looking at number of events per year by
age and by sex? For example give me a specific calculation you are
performing in excel that you would like to automate via syntax. You
could look at this data a lot of different ways.
Marc
dundee
I want to calculate the SMR over the ten years period, SMR=Obs/Exp.
When using indirect standardization:
Obs=total number of events1 in study population=∑n
Exp=∑(〖t_ijk×(N_ijk 〗/T_ijk)
i=age group (1-5), j=sex (1,2), k=year (1992,2001), N=number of event1
in reference population, T=person years in reference population,
n=number of event1 in study group, t=person years in study group
Nijk, Tijk (agegroup, sex, year break down figures) are provided in
excel files;
For tijk, I created ten age variables (by year) => recode into ten age
groups (by year) => produce crosstab tables for t (same format as the
population data)
Then, in excel, I used ‘sumproduct’ function to derive the Exp => SMR
If I want to compare among different groups (e.g. by using cat1), then
direct standardization is applied:
Obs=total number of events1 in reference population=∑N
Exp=∑〖T_ijk×(n_ijk 〗/t_ijk)
This time to obtain nijk, I extracted year of the event (i.e.
yr_cond1, using xdate.year), then produce the tables in spss, then
similar calculation again in excel for result.
Hope it is clear in explaining what I want to simplify the procedure
in syntax.
Thanks.
Ng
dundee
of going back to excel formula.
mcap
> and also, i would love to use syntax produce the 95% CI too, instead
> of going back to excel formula.
OK...I must be missing something. Or....perhaps I have successfully
purged all epi content from my skull until I have to teach it in
summer.....
For Indirect standardization, you need a rate from your reference
population. Where is it??
SMR = Obser/Expected as you say....but your expected comes from
multiplying your cohort pop by the rate from some reference pop.
Where do you have that? If your data are arranged, by year, like
this, then it may be easy to think of something...
Age group Population Obs # events reference rate per
100,000 expected # events
1
2
3
4
5
Bruce Weaver
> reflect my data:
>
> SPSS file with individual leve data:
>
> raniomid sex dt_birth dt_death entry_dt cond1 dt_cond1 con2 dt_con2
> thres_dt cat
> 354 1 01.09.1904 01.08.2002 18.11.1998 1 01.07.1993 0 1
> 936 1 01.02.1905 01.08.2004 04.12.2003 0 0 1
> 855 2 01.01.1908 01.08.2006 06.08.2001 1 01.06.1999 0 1
> 688 1 01.04.1908 01.08.2006 06.04.2005 1 01.10.1997 0 1
> 74 2 01.08.1908 01.08.2004 05.01.1998 1 01.07.1996 1 01.07.1993 0
> 748 2 01.04.1910 01.08.2005 18.04.1996 1 01.05.1995 0 0
> 737 1 01.08.1910 14.10.2002 1 01.08.2000 0 1
> 984 2 01.08.1910 21.05.2001 1 01.01.1992 0 1
> 986 1 01.09.1911 01.08.2006 28.07.1999 0 0 0
> 492 2 01.01.1912 01.08.2005 15.09.1997 1 01.01.1999 0 1
> 81 2 01.02.1912 01.08.2004 14.09.2000 1 01.08.1994 0 1
> 450 2 01.09.1912 01.08.2006 22.08.2000 1 01.11.1998 0 1
> 123 2 01.04.1913 01.08.2006 22.04.1999 1 01.01.2000 0 0
> 781 2 01.06.1913 01.08.2003 20.05.1996 0 0 0
> 359 1 01.08.1913 21.09.2005 1 01.01.1997 0 1
> 207 2 01.01.1914 11.09.2006 1 01.08.2001 0 1
> 308 2 01.02.1914 13.01.2003 1 01.01.2001 0 1
> 201 2 01.05.1914 03.09.2002 1 01.03.2001 0 0
If I follow, this data is from the group for which you want to compute
the SMR. If so, you need to reduce it to the same format as the
tables below. Specifically, for each age stratum, you need to know
the total number of people, and the number who died due to conditions
1 and 2. Use AGGREGATE for this. If you are not familiar with
AGGREGATE, you can consult the tutorial on the UCLA website. See the
Data Management link on this page:
http://www.ats.ucla.edu/stat/spss/
Also, you might find things a bit easier if you restructure the data
like this:
year agegrp N Cond1 Cond2
1996 0-19 ??? ??? ???
1996 20-39 ??? ??? ???
etc
2001 80+ ??? ??? ???
This will reduce the number of COMPUTE statements you need to write.
E.g., you'll need to compute the Cond1 and Cond2 proportions.
>
> excel with population reference (male example only)
>
> reference population
> male
> age\year 1996 1997 1998 1999 2000 2001
> 0-19 5800 5900 6100 6304 6500 6800
> 20-39 6000 6100 6340 6601 6890 7120
> 40-59 8000 8300 8569 8600 8900 9020
> 60-79 7600 7800 7989 8209 8320 8302
> 80+ 4000 4030 4203 4250 4450 4320
> total 31400 32130 33201 33964 35060 35562
If I follow, this table gives the population size in the various age-
group x year combinations, right? As mentioned above, it might be
easier if you restructure it to one row for each age-group x year
combination, like this:
year agegrp StPopN
1996 0-19 5800
1996 20-39 5900
etc
2001 80+ 4320
>
> observed condition 1 in reference population
> male
> age\year 1996 1997 1998 1999 2000 2001
> 0-19 3 4 6 2 1 6
> 20-39 9 7 7 10 11 14
> 40-59 18 12 17 15 16 20
> 60-79 30 27 36 30 42 23
> 80+ 33 36 29 20 45 32
> total 93 86 95 77 115 95
And this gives the number of deaths due to condition 1 (or number of
cases of condition 1), right? So to get the expected proportion (at
the standard rate), you need to divide the number in this table by the
corresponding number in the previous table.
And then you'll need a similar table that shows the number of people
and the number of deaths in each age-group x year stratum in the group
(or "population") for which you wish to compute the SMR. I assume
that is the "individual level" data shown earlier.
>
> observed condition 1 in reference population
Did you mean condition 2 for this table?
> male
> age\year 1996 1997 1998 1999 2000 2001
> 0-19 2 3 1 4 6 4
> 20-39 7 5 6 7 9 12
> 40-59 12 11 13 9 8 13
> 60-79 16 15 17 20 19 20
> 80+ 21 23 24 26 25 27
> total 58 57 61 66 67 76
>
> I used to create dozens of variables in SPSS (e.g. agegroup at death/
> cond1/2,
> year of cond1/2) in order to get crosstab and then use excel to
> calculate the result (e.g. sumproduct etc.).
> Now I want to know more about the underlying differences by further
> adjusting the data (e.g. using thres date to exclude pre-exsiting
> cond, or create subgroup using cat1). My ancient methods really give
> me an heartattack.
>
> I have access to SPSS and SAS only (but no experience in using macro).
> My sincere apology of my inability, the investment/health economics
> background could not ba any help on using syntax/macros.
>
> Hope this is clear, i am happy to email you with more detailed (but
> artificial) data in spss if it helps.
>
> Ng
In addition to AGGREGATE, you'll need to use MATCH FILES to combine
the various bits of data (i.e., standard population N, standard
population cond1 and cond2 info, and info from your population of
interest) into a single file. The link given above also has a nice
tutorial on MATCH FILES.
Once you have computed expected and observed deaths in each age-group
x year combination, then you can use AGGREGATE once again to get the
sums that you need to compute SMR = O/E.
HTH.
--
Bruce Weaver
bwe...@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/
"When all else fails, RTFM."
Bruce Weaver
Marc, I think that info was present in the tables shown in an earlier
post. I.e., I think one of the tables showed the total N in each age-
group x year combination in the standard population, while the next
table showed the number of deaths due to condition 1, and the final
table deaths due to condition 2. So it's a matter of merging those 3
files (MATCH FILES) and then working out the needed proportions. I
think the first table in that post (individual level data) has the
folks for whom the OP wants to compute the SMR. So it needs to be
reduced (via AGGREGATE) to the same structure as the later tables, and
must show the number of people per stratum and the observed deaths
per stratum. (See my post earlier in the thread).
Cheers,
Bruce
mcap
> "When all else fails, RTFM."- Hide quoted text -
>
> - Show quoted text -
Thanks Bruce. You save the day once again. Just one thing that
confuses me....using a standard population is not an indirect way to
calculate an SMR. It's direct. Only if he takes the rates from other
sources and then uses his pop is it indirect.
Perhaps I should just wait until summer :-)
Marc
Bruce Weaver
> > - Show quoted text -
>
> Thanks Bruce. You save the day once again. Just one thing that
> confuses me....using a standard population is not an indirect way to
> calculate an SMR. It's direct. Only if he takes the rates from other
> sources and then uses his pop is it indirect.
>
> Perhaps I should just wait until summer :-)
>
> Marc
I'm not an epidemiologist, but according to everything I've read on
the subject, only the indirect method of rate adjustment results in a
standardized mortality (or incidence) ratio.
Direct adjustment: Stratum-specific rates from the population or
populations of interest are applied to a standard population to
determine the expected number of deaths in the standard population.
Indirect adjustment: Stratum-specific rates from a standard
population are applied to the populations of interest to calculate the
expected number of deaths (at the standard rate). O=observed deaths,
E = expected deaths (at the standard rate); SMR = O/E.
Here are two examples:
http://www.health.state.pa.us/hpa/stats/techassist/ageadjusted.htm
http://www.health.state.pa.us/hpa/stats/techassist/stdmortratio.htm
--
Bruce Weaver
bwe...@lakeheadu.ca
mcap
> "When all else fails, RTFM."
Hi Bruce:
I am still missing something. It looks like in the set of tables
presented, that he has number of events in the STUDY population by
age. Obersved events over the total pop would give him a crude rate.
Taking his age specifc rates and then weighting them according to the
standard population would be direct age adjustment. It looks like he
can do that with his numbers. For indirect, I think we would need the
number of events in his population by age, the number of people in his
population by age and then the RATE not the number in the standard
population by age. The rate in his standard pop x his pop gives you
the expected. That is, of course, unless he wants to do an SMR
between two groups within his own sample. I
You might be saying the same thing and perhaps I am not
following......so he should go ahead with your rec.
Marc
Bruce Weaver
I think we are in agreement, Marc. The OP gave these two tables
in an earlier post:
excel with population reference (male example only)
reference population
male
age\year 1996 1997 1998 1999 2000 2001
0-19 5800 5900 6100 6304 6500 6800
20-39 6000 6100 6340 6601 6890 7120
40-59 8000 8300 8569 8600 8900 9020
60-79 7600 7800 7989 8209 8320 8302
80+ 4000 4030 4203 4250 4450 4320
total 31400 32130 33201 33964 35060 35562
observed condition 1 in reference population
male
age\year 1996 1997 1998 1999 2000 2001
0-19 3 4 6 2 1 6
20-39 9 7 7 10 11 14
40-59 18 12 17 15 16 20
60-79 30 27 36 30 42 23
80+ 33 36 29 20 45 32
total 93 86 95 77 115 95
The way I interpreted these is that the number in the second table
divided by the corresponding number in the first table gives the
rate in the standard population (as a proportion rather than as
per 1,000 or 100,000). That proportion must be multiplied by the
*number* of people in the same stratum in the population of
interest (you called it the STUDY population) to get the expected
number of deaths (at the standard rate). Then expected and
observed numbers are summed across all strata, and Sum(O)/Sum(E) =
SMR.
--
Bruce Weaver
bwe...@lakeheadu.ca
dundee
sample data:
>Marc
> For Indirect standardization, you need a rate from your reference
>population. Where is it??
All data at the reference population level are structured in excel
tables.
In the example, reference rate can be derived using the first two
excel tables for cond1.
N.B. the last table should be observed number of cases for
condition2.
The formula of Exp num was changed by the webpage default settings, it
should be corrected bracketed as Exp=∑[t_ijk×(N_ijk /T_ijk)]= ∑st
pop×ref rate
>Bruce
> reference population
> male
> age\year 1996 1997 1998 1999 2000 2001
> 0-19 5800 5900 6100 6304 6500 6800
> 20-39 6000 6100 6340 6601 6890 7120
> 40-59 8000 8300 8569 8600 8900 9020
> 60-79 7600 7800 7989 8209 8320 8302
> 80+ 4000 4030 4203 4250 4450 4320
> total 31400 32130 33201 33964 35060 35562
>If I follow, this table gives the population size in the various age-
>group x year combinations, right? As mentioned above, it might be
>easier if you restructure it to one row for each age-group x year
>combination, like this:
>year agegrp StPopN
>1996 0-19 5800
>1996 20-39 5900
>etc
>2001 80+ 4320
Yes, these are the population size and the following two tables are
observed numbers in the population for cond1 and 2. They will give me
the standard rate. Thank you for the suggestion in changing the table
template, I can visualise it reduce the size of my calculations.
dundee
>
> Direct adjustment: Stratum-specific rates from the population or
> populations of interest are applied to a standard population to
> determine the expected number of deaths in the standard population.
>
> Indirect adjustment: Stratum-specific rates from a standard
> population are applied to the populations of interest to calculate the
> expected number of deaths (at the standard rate). O=observed deaths,
> E = expected deaths (at the standard rate); SMR = O/E.
>
dundee
> mcap wrote:
> > On Jan 12, 3:34 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
> >> On Jan 12, 2:46 pm, mcap <mca...@yahoo.com> wrote:
>
> >>>> - Show quoted text -
> >>> Thanks Bruce. You save the day once again. Just one thing that
> >>> confuses me....using a standard population is not an indirect way to
> >>> calculate an SMR. It's direct. Only if he takes the rates from other
> >>> sources and then uses his pop is it indirect.
> >>> Perhaps I should just wait until summer :-)
> >>> Marc
> >> I'm not an epidemiologist, but according to everything I've read on
> >> the subject, only the indirect method of rate adjustment results in a
> >> standardized mortality (or incidence) ratio.
>
> >> Direct adjustment: Stratum-specific rates from the population or
> >> populations of interest are applied to a standard population to
> >> determine the expected number of deaths in the standard population.
>
> >> Indirect adjustment: Stratum-specific rates from a standard
> >> population are applied to the populations of interest to calculate the
> >> expected number of deaths (at the standard rate). O=observed deaths,
> >> E = expected deaths (at the standard rate); SMR = O/E.
>
> >> Here are two examples:
>
>
thanks.
That is exactly what I mean and I have done in EXCEL.
To summarize here, I don’t need macro, just have to re-organized and
combine all my data (Study and Reference population) into a file like
the following, using aggregate and match files.
Year, agegrp, PopN, Pop_con1, Pop_con2, StudyPop, Study_Cond1,
Study_con2
1999, 0-19, 5800, 3, 2,
1999, 20-39, 5900, 9, 7
…
2001 80+,4320, 32, 27
The figures from study population will be derived step-by-step in
aggregates too, and compute Sum(O)/Sum(E) = SMR in spss? It is great
that only simple syntax is required, but how about the 95% CIs?
One addition question is that I also want to split my study cohort
into subgroups adjusting for pre-existing conditions (cat & thres_dt
variables) and to perform the same calculations.
e.g. for cond1:
if cat=1 & thres_dt prior to con1_dt, then pt=cat1; else pt=2.
This means the study population will differ from condition to
condition (as it depends on the comparison between thres_dt and
event_dt). In this scenario, I still have to repeat the above method
and create StudyPop by condition one by one.? (in my actual study,
this means 9 condtions!).
Ng
Bruce Weaver
--- snip ---
> The figures from study population will be derived step-by-step in
> aggregates too, and compute Sum(O)/Sum(E) = SMR in spss? It is great
> that only simple syntax is required, but how about the 95% CIs?
--- snip ---
I had to look up the SE for the SMR. Here is one formula for it (from
http://www.health.state.pa.us/hpa/stats/techassist/ciratio.htm):
SE(SMR) = SR / square root of d, where d = number of observed events
I think "events" here means deaths, so d = sum of observed deaths,
which you'll already have from your AGGREGATE.
The limits of the 95% CI are SMR - and + 1.96*SE. Just use COMPUTE
statements.
--
Bruce Weaver
bwe...@lakeheadu.ca
mcap
> On Jan 13, 7:20 am, dundee <andyim...@gmail.com> wrote:
>
> --- snip ---
>
> > The figures from study population will be derived step-by-step in
> > aggregates too, and compute Sum(O)/Sum(E) = SMR in spss? It is great
> > that only simple syntax is required, but how about the 95% CIs?
>
> --- snip ---
>
>
> SE(SMR) = SR / square root of d, where d = number of observed events
>
> I think "events" here means deaths, so d = sum of observed deaths,
> which you'll already have from your AGGREGATE.
>
> The limits of the 95% CI are SMR - and + 1.96*SE. Just use COMPUTE
> statements.
>
> --
> Bruce Weaver
> "When all else fails, RTFM."
Hi Guys:
Didn't see observed events in reference population. I assumed it
was the sample population. This should be fine to do with syntax.
dundee
dundee
calculations, from a person who used macro succesfully for his case.
To share with future possible users.
For a general idea about SMRs :
http://www.statistics.gov.uk/downloads/theme_health/Ward_SMR_Metadata.pdf
The following Excel file gives an example about how to compute SMR
ratios.
http://www.statistics.gov.uk/downloads/theme_health/SMR_Templates.xls
The following page contained the links towards the previous files.
http://www.statistics.gov.uk/statbase/Product.asp?vlnk=14359