I'm having trouble calculating unadjusted hospitalization rates (per 100,000). I have unit record data for episodes of care at a hospital to count up for the numerator. These unit records include information on year of admission, age at admission, sex and area of residence as well as a bunch of clinical variables. I have populations for each year, area of residence (about 500 of them), age and sex to sum up for the denominator. Here is part of my load script:
ADMISSIONS:
LOAD
ADM_YEAR AS YEAR,
AGE,
AREA_4DIGITCODE,
SEX,
CONDITION,
UNIQUE_ID
FROM
QVDdatafile(qvd);
POPULATIONS:
LOAD
YEAR,
AGE,
AREA_4DIGITCODE,
SEX,
Population
FROM
PopData.xlsx (ooxml, embedded labels, table is Sheet1);
Initially I was using the following expressions to calculate (1) number of hospitalizations, (2) population and (3) crude rate of hospitalization per 100,000 population:
(1) COUNT(UNIQUE_ID)
(2) SUM(Population)
(3) 100000*COUNT(UNIQUE_ID)/SUM(Population)
The results are fine if I make NO selections, such as for CONDITION or a specific YEAR. The Sum(population) correctly takes every population
However, when I want the rate of hospitalization rate for women who had a stroke in 2007, select 'Female' is a SEX item box, select 'Stroke' in a CONDITION item box and '2007' in a YEAR item box, only the populations in areas of residence where there was at least 1 stroke are summed. I understand and accept why this is the case.
I suspected i need a Set Analysis and tried this: COUNT(UNIQUE_ID)/SUM( {$<SA2_4DIGITCODE=> } Population) without success.
So i summary I want to calculate hospitalization rates for specific conditions/diagnoses within certain populations, such as Males in 2010, or Females in 2009. I would also like to get rates for groups of the AREA_4DIGITCODE, that we are calling REGIONS.
Can someone please direct me? I'm also open to suggestions on a better data model, though I can't give too much information about the data itself due to sensitivity of the data.
