******************************************************************************** ******************************************************************************** *** TASK: Recommended ICD-10-CA Codes: Potentially Avoidable Mortality *** *** Indicators - Stata Syntax *** *** DATE: May 30, 2014 *** *** EDIT: Feb 06, 2015 *** *** AUTHOR: Andrew Harris, Epidemiologist, HKPR District Health Unit *** *** *** *** DEFINITIONS: From the 2012 CIHI Health Indicators Report *** *** https://secure.cihi.ca/free_products/health_indicators_2012_en.pdf) *** ******************************************************************************** ******************************************************************************** *** This document is intended for use with "Recommended ICD-10-CA Codes *** *** for the Potentially Avoidable Mortality Indicators" that can be found *** *** in the Core Indicators Resource section of the APHEO website at *** *** www.apheo.ca *** ******************************************************************************** ******************************************************************************** *** Data Source: Intellihealth -- Maps >> 01 Vital Stats >> Death *** *** This file requires the dataset you are using to contain the following: *** *** (AND THEY MUST BE IN THIS ORDER, saved as a ".xlsx" file) *** *** - Calendar Year (cyear) *** *** - #Dths (ON res) *** *** - #PYLL_2 (ON res) *** *** - Age-group (5yr) *** *** - Sex *** *** - PHU of Deceased *** *** - ICD10 Code (3 Char) Primary Cause *** *** - ICD10 Code Primary Cause *** ******************************************************************************** ******************************************************************************** *** When the ENTIRE FILE is run as a whole, there will be two datasets *** *** created. These files can then be used as a starting block to obtian *** *** age-standardized rates, age-specific rates, or used for reporting *** *** counts. The files retained contain the Numerator only. *** *** ALL OTHER FILES ARE DELETED upon a sucsessful run of this file. *** ******************************************************************************** ******************************************************************************** *** Use this output dataset to start age-standardized or age-specific rates *** *** (add weights, denominator). *** *** Contains Avoidable, Preventable, and Treatable Mortality Counts by *** *** PHU, A-P-T Mortality Cause, Sex, and 5-yr age-group (0-4,...,70-74) *** *** 1) "`source'/Avoidable-Prev-Treat Mort by PHU-Cause-Sex-Age.dta" *** *** *** *** Use this output dataset for Crude Rates *** *** 2) "`source'/Avoidable-Prev-Treat Mort by PHU-Cause-Sex.dta" *** *** *** ******************************************************************************** *** This file was desigend for use with aggregate data but can be easily *** *** modified to be used with line-level data. *** *** *** *** IF line-level data, then set deaths == 1 (see lines 89-91) and comment *** *** out the renaming of the death variable on line 92. *** *** *** *** The Required variables are indicated below. *** *** Enter the corresponding variable name in your data into the respective *** *** local macro below (i.e., replace cyear or dthsonres, etc. with your *** *** variable names. *** *local var1 sex // Sex (Don't rename if already in data) local var2 cyear // Calendar Year local var3 dthsonres // Count of deaths local var4 pyll_2onres // PYLL local var5 phuofdeceased // PHU local var6 agegroup5yr // Age group (5 year groups) local var7 icd10code3charprimarycaus // ICD-10-CA code (4 Digit) local var8 icd10codeprimarycause // ICD-10-CA Description // The physical path where the source file is located // The output file will also go here. // (Change "H:/EPI/APHEO Working Groups/Preventable Mortality" to ....) local source "H:/EPI/APHEO Working Groups/Preventable Mortality" // Change the directory to work in the above location cd "`source'" // The name of the file containing the data (including the extension) // Change ("Mortality 2009 00-74.xlsx" to ....) local icdfile "Mortality 2009 00-74.xlsx" // The name of the work sheet in your file // (Change sasExport4NE7 to ....) local sheet "sasExport4NE7" ******************************************************************************** ******************************************************************************** **** Code Begins **** *************************** // Import an excel file (in this case, from IntelliHealth) clear import excel "`source'/`icdfile'", sheet("`sheet'") firstrow case(lower) **browse // Only uncomment this field if you have line level data that does not have a // indicator of death Y/N. *gen deaths = 1 // Note: the variable sex was named sex already so it does not get renamed here *rename `var1' = sex // Sex rename `var2' year // Calendar Year rename `var3' deaths // The Count rename `var4' pyll // PYLL rename `var5' phu // PHU rename `var6' age // Age group (5 year groups) rename `var7' icd10_2 // ICD-10-CA code (4 character) rename `var8' icd10_3 // ICD-10-CA Description format phu %-24s format sex %-9s format icd10_2 %-9s format icd10_3 %-75s browse // Review and repalce Sex (& drop if missing) table sex replace sex = "FEMALE" if sex =="F" replace sex = "MALE" if sex =="M" replace sex = "OTHER" if sex =="O" drop if missing(sex) // Obtain the ICD-10 4 character valuse from the ICD-10-CA description) // because intellihealth only offers the 3 characer value by itself. // ICD-10 (1alpha-3numeric) capture drop cause gen cause = substr(icd10_3, 2, 5) order cause icd10_3 format cause %9s replace cause = substr(cause, 1, 4) replace cause = substr(cause, 1, 3) if substr(cause, 4, 1) == ")" // ICD-10 Description // Remove code from the front so it's just the description gen description = substr(icd10_3, 6, 244) format description %-244s replace description = substr(description, 2, 243) if substr(description, 1, 1) == ")" replace description = trim(description) order cause description format description %-100s drop icd10_2 icd10_3 **browse // Review age-groups in the data table age // Remove age groups for those 75 years and up. drop if inlist(age, "75-79", "80-84", "85-89", "90+") // Create consistent age-groups (e.g., ##-##) replace age = "00-04" if inlist(age, "00-<01","01-04","00","0","<1") // Marker for those less than 45 yrs of age capture drop age45 gen age45 = 0 replace age45 = 1 if inlist(age,"00-04","05-09","10-14","15-19") replace age45 = 1 if inlist(age,"20-24","25-29","30-34","35-39", "40-44") // Remove Males with Breast Cancer (C50.XX) & Remove persons 45 yrs or older // with specfic leukaemia's (C910, C911, C921) drop if regexm(cause,"C50") & sex == "MALE" drop if inlist(cause,"C910", "C911", "C921") & age45 == 0 // Review table age **browse *save "`source'/Avoidable Mortality File 1.dta", replace ******************************************************************************** ******************************************************************************** **** CREATE THE BY-CAUSE GROUPINGS FOR PREVETNABLE AND TREATABLE CAUSES **** **use "`source'/Avoidable Mortality File 1.dta", clear set more off // Create an empty sting variable gen Cause = "" ******************************************************************************** ******************************************************************************** /*** Cardiovascular Causes ***/ local cardio /// I01 I02 I05 I06 I07 I08 I09 I10 /// I11 I12 I13 I15 /// I20 I21 I22 I23 I24 I25 I26 /// I60 I61 I62 I63 I64 I67 I69 /// I70 I739 I71 /// I80 I829 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local cardio { quietly replace Cause = "Cardiovascular" if regexm(cause, "`x'") } order Cause cause *table Cause ******************************************************************************** ******************************************************************************** /*** Cancer Causes ***/ local cancer /// C00 C01 C02 C03 C04 C05 C06 C07 C08 C09 /// C10 C11 C12 C13 C14 C15 C16 C18 C19 /// C20 C21 C22 /// C33 C34 /// C43 C44 /// C50 C53 C54 C55 /// C62 C67 /// C73 /// C81 /// C910 C911 C921 /// D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 /// D20 D21 D22 D23 D24 D25 D26 D27 D28 D29 /// D30 D31 D32 D33 D34 D35 D36 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local cancer { quietly replace Cause = "Cancer" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** /*** Respiratory Causes ***/ /*** Case Selection Part 1 ***/ local respir /// C45 /// J00 J01 J02 J03 J04 J05 J06 /// J20 J22 /// J30 J31 J32 J33 J34 J35 J36 J37 J38 J39 /// J40 J41 J42 J43 J44 J45 J47 /// J60 J61 J62 J63 J64 J66 J67 J68 J69 /// J70 J71 /// J80 J81 J82 J85 J86 /// J90 J92 J93 J94 J98 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local respir { quietly replace Cause = "Respiratory" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** /*** Injuries Unintentional ***/ local unintent /// V /// W /// X00 X01 X02 X03 X04 X05 X06 X07 X08 X09 /// X10 X11 X12 X13 X14 X15 X16 X17 X18 X19 /// X20 X21 X22 X23 X24 X25 X26 X27 X28 X29 /// X30 X31 X32 X33 X34 X35 X36 X37 X38 X39 /// X40 X41 X42 X43 X44 X45 X46 X47 X48 X49 /// X50 X51 X52 X53 X54 X55 X56 X57 X58 X59 /// Y0 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local unintent { quietly replace Cause = "Injuries Unintentional" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** /*** Injuries Intentional ***/ local intent /// X60 X61 X62 X63 X64 X65 X66 X67 X68 X69 /// X70 X71 X72 X73 X74 X75 X76 X77 X78 X79 /// X80 X81 X82 X83 X84 X85 X86 X87 X88 X89 /// X90 X91 X92 X93 X94 X95 X96 X97 X98 X99 /// Y0 /// Y870 Y871 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local intent { quietly replace Cause = "Injuries Intentional" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** /*** Injuries Undetermined Intent ***/ local undeter /// Y1 /// Y2 /// Y30 Y31 Y32 Y34 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local undeter { quietly replace Cause = "Injuries Undetermined" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** /*** Injuries Adverse Effects ***/ local adverse /// Y4 /// Y5 /// Y60 Y61 Y62 Y63 Y64 Y65 Y66 /// Y7 /// Y80 Y81 Y82 Y83 Y84 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local adverse { quietly replace Cause = "Injuries Adverse" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** DIGESTIVE **** local digestive /// K25 K26 K27 K28 K35 K36 K37 K38 K40 K41 K42 K43 K44 K45 K46 /// K80 K81 K82 K83 K850 K851 K853 K858 K859 K861 K862 K863 K868 K869 /// K73 K740 K741 K742 K746 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local digestive { quietly replace Cause = "Digestive" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** GENITOURINARY **** local genito /// N00 N01 N02 N03 N04 N05 N06 N07 N17 N18 N19 N13 N20 N21 N23 /// N35 N40 N341 N70 N71 N72 N73 N750 N751 N764 N766 N25 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local genito { quietly replace Cause = "Genitourinary" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** INFANTMATERNAL **** local infant /// P /// Q /// O /// A33 H311 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local infant { quietly replace Cause = "Infant" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** INFECTIONS **** local infection /// A00 A01 A02 A03 A04 A05 A06 A07 A08 A09 /// A16 A17 A18 A19 B90 J65 A35 A36 A37 A39 /// A403 A413 A492 A80 B01 B05 B06 J09 J10 J11 J13 J14 G000 G001 /// A38 A481 A491 /// A400 A401 A402 A404 A405 A406 A407 A408 A409 /// A410 A411 A412 A414 A415 A416 A417 A418 A419 /// B50 B51 B52 B53 B54 /// G002 G003 G008 G009 /// A46 /// L03 /// J12 J15 J16 J18 /// A50 A51 A52 A53 A54 A55 A56 A57 A58 A59 A60 A63 A64 /// B15 B16 B17 B18 B19 B20 B21 B22 B23 B24 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local infection { quietly replace Cause = "Infection" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** NEUROLOGICAL **** local neuro /// G40 G41 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local neuro { quietly replace Cause = "Neurological" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** NUTRITIONAL **** local nutrit /// D50 D51 D52 D53 E00 E01 E02 E03 E04 E05 E06 E07 /// E10 E11 E12 E13 E14 E24 E25 E27 E740 E742 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local nutrit { quietly replace Cause = "Nutrition" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** Alcohol & Drugs **** local drugs /// F10 G312 G621 I426 K292 K70 K852 K860 /// F11 F12 F13 F14 F15 F16 F18 F19 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local drugs { quietly replace Cause = "Alcohol & Drugs" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** **** Muscoloskeletal **** local muscl /// M86 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local muscl { quietly replace Cause = "Muscoloskeletal" if regexm(cause, "`x'") } *table Cause ******************************************************************************** ******************************************************************************** ******************************************************************************** ******************************************************************************** ******************************************************************************** ******************************************************************************** // Quality Check // Review of ICD-10 Codes not assigned by code above. tabulate cause if missing(Cause) // Records not in the case definition drop if missing(Cause) *save "`source'/Avoidable Mortality File 2.dta", replace ******************************************************************************** ******************************************************************************** ******************************************************************************** *use "`source'/Avoidable Mortality File 2.dta", clear // The Preventable Casue of Death (ICD-10-CA) gen preventable = . // ICD-10 codes are listed in the comments as the range; however, // in the selection code the range is typed as less than the upper rnage + 1. // FOR EXAMPLE: the range of A00-A09 is selected as >=A00 & <=A10 /*** Preventable Mortality: Selection Part 1 ***/ quietly replace preventable = 1 if /// (cause >= "A00" & cause < "A10") /* A00-A09 */ | /// (cause >= "A35" & cause < "A38") /* A35-A37 */ | /// (cause >= "A50" & cause < "A61") /* A50-A60 */ | /// (cause >= "B15" & cause < "B20") /* B15-B19 */ | /// (cause >= "B20" & cause < "B25") /* B20-B24 */ | /// (cause >= "C00" & cause < "C15") /* C00-C14 */ | /// (cause >= "D50" & cause < "D54") /* D50-D53 */ | /// (cause >= "E10" & cause < "E15") /* E10-E14 */ | /// (cause >= "F11" & cause < "F17") /* F11-F16 */ | /// (cause >= "I05" & cause < "I10") /* I05-I09 */ | /// (cause >= "I20" & cause < "I26") /* I20-I25 */ | /// (cause >= "I60" & cause < "I63") /* I60-I62 */ | /// (cause >= "I63" & cause < "I65") /* I63-I64 */ | /// (cause >= "J09" & cause < "J12") /* J09-J11 */ | /// (cause >= "J40" & cause < "J45") /* J40-J44 */ | /// (cause >= "J60" & cause < "J65") /* J60-J64 */ | /// (cause >= "J66" & cause < "J71") /* J66-J70 */ | /// (cause >= "V01" & cause < "W") /* V01-V99 */ | /// (cause >= "W00" & cause < "W20") /* W00-W19 */ | /// (cause >= "W20" & cause < "W65") /* W20-W64 */ | /// (cause >= "W65" & cause < "W75") /* W65-W74 */ | /// (cause >= "W75" & cause < "X") /* W75-W99 */ | /// (cause >= "X00" & cause < "X10") /* X00-X09 */ | /// (cause >= "X10" & cause < "X40") /* X10-X39 */ | /// (cause >= "X40" & cause < "X50") /* X40-X49 */ | /// (cause >= "X50" & cause < "X60") /* X50-X59 */ | /// (cause >= "X60" & cause < "X85") /* X60-X84 */ | /// (cause >= "X85" & cause < "Y") /* X85-X99 */ | /// (cause >= "Y00" & cause < "Y10") /* Y00-Y09 */ | /// (cause >= "Y10" & cause < "Y35") /* Y10-Y34 */ | /// (cause >= "Y40" & cause < "Y60") /* Y40-Y59 */ | /// (cause >= "Y60" & cause < "Y67") /* Y60-Y66 */ | /// (cause >= "Y70" & cause < "Y83") /* Y70-Y82 */ /*** Preventable Mortality: Selection Part 2 ***/ local prevent /// A39 A403 A413 A492 A80 B01 B05 B06 J13 J14 G000 G001 /// A63 A64 /// C15 C16 C22 C33 C34 C43 C44 /// I01 I02 I26 I426 I67 I69 I70 I71 I80 I829 /// C45 J82 J92 /// K73 K740 K741 K742 K746 /// H311 /// Y870 /// Y871 /// F10 G312 G621 /// K292 K70 K852 K860 /// F18 F19 /// Y69 Y83 Y84 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local prevent { quietly replace preventable = 1 if regexm(cause, "`x'") } order cause preventable table preventable *browse ******************************************************************************** ******************************************************************************** ******************************************************************************** // The Treatable Casue of Death (ICD-10-CA) capture drop treatable gen treatable = . /*** Treatable Mortality: Selection Part 1 ***/ quietly replace treatable = 1 if /// (cause >= "A16" & cause < "A20") /* A16-A19 */ | /// (cause >= "B50" & cause < "B55") /* B50-B54 */ | /// (cause >= "C18" & cause < "C22") /* C18-C21 */ | /// (cause >= "D10" & cause < "D37") /* D10-D36 */ | /// (cause >= "E00" & cause < "E08") /* E00-E07 */ | /// (cause >= "E10" & cause < "E15") /* E10-E14 */ | /// (cause >= "I11" & cause < "I14") /* I11-I13 */ | /// (cause >= "I20" & cause < "I26") /* I20-I25 */ | /// (cause >= "I60" & cause < "I63") /* I60-I62 */ | /// (cause >= "I63" & cause < "I65") /* I63-I64 */ | /// (cause >= "J00" & cause < "J07") /* J00-J06 */ | /// (cause >= "J30" & cause < "J40") /* J30-J39 */ | /// (cause >= "K25" & cause < "K29") /* K25-K28 */ | /// (cause >= "K35" & cause < "K39") /* K35-K38 */ | /// (cause >= "K40" & cause < "K47") /* K40-K46 */ | /// (cause >= "K80" & cause < "K84") /* K80-K83 */ | /// (cause >= "N00" & cause < "N08") /* N00-N07 */ | /// (cause >= "N17" & cause < "N20") /* N17-N19 */ | /// (cause >= "N70" & cause < "N74") /* N70-N73 */ | /// (cause >= "O00" & cause < "P") /* O00-O99 */ | /// (cause >= "P00" & cause < "P97") /* P00-P96 */ | /// (cause >= "Q00" & cause < "R") /* Q00-Q99 */ /*** Treatable Mortality: Selection Part 2 ***/ local treat /// /*A33*/ A38 /// A400 A401 A402 A404 A405 A406 A407 A408 A409 /// A410 A411 A412 A414 A415 A416 A417 A418 A419 /// A46 A481 A491 /// B90 /// C53 C54 C55 C62 C67 C73 C81 /// E24 E25 E27 E740 E742 /// G40 G41 /// G002 G003 G008 G009 /// I10 I15 I67 I69 I70 I739 /// J12 J15 J16 J18 J20 J22 J45 J47 J65 J80 J81 J85 J86 J90 J93 J94 J98 /// K850 K851 K853 K858 K859 /// K861 K862 K863 K868 K869 /// L03 /// M86 /// N13 N20 N21 N23 N35 N40 N25 N341 N750 N751 N764 N766 /*** Returns an indicator for any cell beginning with the listed ICD-10 CODE ***/ foreach x of local treat { quietly replace treatable = 1 if regexm(cause, "`x'") } * /*** Treatable Mortality: Selection Part 3 ***/ quietly replace treatable = 1 if regexm(cause,"C50") & sex == "FEMALE" quietly replace treatable = 1 if /// inlist(cause,"C910", "C911", "C921") & age45 == 1 order cause Cause preventable treatable sort cause *browse replace preventable = 0 if missing(preventable) replace treatable = 0 if missing(treatable) // Quality check table preventable treatable // This file has an indicator for the treatable mortality *save "`source'/Avoidable Mortality File 3.dta", replace ******************************************************************************** ******************************************************************************** *use "`source'/Avoidable Mortality File 3.dta", clear // The section below is the 50/50 case assignment for conditions that // are considered 50% preventable and 50% treatable, from the CIHI definition. // Run a loop for the ICD-10 codes listed in the local macro to mark as splits // Set indicator to missing capture drop rdmhalf gen rdmhalf = . // Marks the Cerebrovascular Diseases local splits1 I60 I61 I62 I63 I64 I67 I69 foreach v of local splits1 { quietly replace rdmhalf = 1 if regexm(cause,"`v'") // Keeps ICD10.xx } // Marks the Ischaemic Heat Diseases local splits2 I20 I21 I22 I23 I24 I25 foreach v of local splits2 { quietly replace rdmhalf = 2 if regexm(cause,"`v'") // Keeps ICD10.xx } // Marks the Other Atherosclerosis replace rdmhalf = 3 if regexm(cause, "I70") // Keeps I70.xx replace rdmhalf = 3 if regexm(cause, "I739") // Keeps I73.9 // Marks the Diabetes Mellitus local splits3 E10 E11 E12 E13 E14 foreach v of local splits3 { quietly replace rdmhalf = 4 if regexm(cause,"`v'") // Keeps ICD10.xx } * // This summation would take line-level data and sum for aggregate counts, by // grouping. If your data is already aggregate counts, then this step still // summ the data based on the groups in the by statement. // // With line-level data ICD-10 codes are no longer available in the data after // the following -collapse-. collapse (sum) deaths pyll, by(Cause cause preventable treatable year sex phu age rdmhalf) sort cause preventable treatable *browse save "`source'/Prev vs Treat.dta", replace // Run a loop to create datasets with both Preventable and Treatable counts // Assign 50% to each group foreach var of varlist preventable treatable { use "`source'/Prev vs Treat.dta", clear // Creates Preventable and Treatable groupings, in respective files for causes // that are to be categories as 50% Preventable and 50% Treatable. gen type = "`var'" keep if !missing(rdmhalf) // This is where the Deaths and PYLL are splt 50/50 (the above was just a marker) // Preventable.dta and Treatable.dta are created from this step replace deaths = deaths * 0.5 replace pyll = pyll * 0.5 save "`source'/`var'.dta", replace } ******************************************************************************** ******************************************************************************** **************************************** **** Preventable & Treatable ***** **************************************** // Append the overall file and then the 50% halves use "`source'/Prev vs Treat.dta", clear // Remove the 50/50 cases so this is either treatable or preventable, not both drop if !missing(rdmhalf) // Creates Preventable and Treatable groupings, in the file for records that // are not split 50/50. gen type = "" replace type = "preventable" if preventable==1 replace type = "treatable" if treatable==1 // Appends the files that contain the 50% split for treatable and preventable // to the file that contaisn the reaminder of the treatable and preventable deaths. append using "`source'/treatable.dta" /// "`source'/preventable.dta" // Removes counts that are not MALE and not FEMALE drop if !inlist(sex,"MALE","FEMALE") // Additional summation due to removal of previous bygroups. collapse (sum) deaths pyll, by(age sex year phu Cause type) sort type year phu sex Cause *browse save "`source'/square1.dta", replace // Create an all possible combination data set (rectangularize) // This fills in variable combinations if they don't exist // (Creates all age-groups by sex, year,cause, type, phu) set more off levelsof type, local (lvls) foreach dataset of local lvls { *display "`var'" use "`source'/square1.dta", clear keep if type == "`dataset'" keep age sex year phu Cause type deaths pyll fillin age sex year phu Cause type foreach var of varlist age sex year phu Cause type { table `var' save "`source'/square1_`dataset'.dta", replace } } * set more on // Appends the squared files for treatable and preventable clear append using "`source'/square1_treatable.dta" /// "`source'/square1_preventable.dta" // Each table should contain the same number of observations per response // If not there are multiple rows for the same bygroups! foreach var of varlist deaths pyll { replace `var' = 0 if missing(`var') } * sort phu sex type Cause age order phu year sex type Cause format phu %-42s format type %-11s format Cause %-22s *browse drop _fillin replace type = upper(type) // This is the Prevetnable, Treatable, and 50% split file (Does not include Overall Avoidable) save "`source'/Assinged Halves.dta", replace ******************************************************************************** ******************************************************************************** *************************** **** Both Sexes ***** *************************** // Summs the treatable and preventable counts for males and female // to obtain a Both sexes total for potentially avoidable mortality // by Cause & PHU & Age-group. use "`source'/Assinged Halves.dta", clear // Create the Both Sexes file collapse (sum) deaths pyll, by(Cause year phu age type) gen str6 sex = "BOTH" // appends the Male and Female files append using "`source'/Assinged Halves.dta" sort phu sex type Cause age table type save "`source'/Prev-Treat Mort by PHU-Cause-Sex-Age.dta", replace ******************************************************************************** ******************************************************************************** ************************************** **** Avoidable Mortality ***** ************************************** // Summs the treatable and preventable counts for males, female, & both sexes // to obtain the Avoidable Mortality Counts by Cause, PHU, Sex, and Age-group. use "`source'/Prev-Treat Mort by PHU-Cause-Sex-Age.dta", clear // Create the Both Sexes file collapse (sum) deaths pyll, by(Cause year phu age sex) gen str11 type = "AVOIDABLE" // appends the Male and Female files append using "`source'/Prev-Treat Mort by PHU-Cause-Sex-Age.dta" sort phu sex type Cause age order type Cause phu year sex age deaths pyll save "`source'/Avoidable-Prev-Treat Mort by PHU-Cause-Sex-Age.dta", replace ******************************************************************************** ******************************************************************************** ******************************* **** Sum Age-Groups ***** ******************************* // Sums the treatable and preventable counts for age-specific groups // to obtain the total count of avoidable, prevetnable, and treatable mortality // by Cause, PHU, and Sex. use "`source'/Avoidable-Prev-Treat Mort by PHU-Cause-Sex-Age.dta", clear // Create the Both Sexes file collapse (sum) deaths pyll, by(Cause year sex type phu) sort phu year sex type Cause order type Cause phu year sex deaths pyll save "`source'/Avoidable-Prev-Treat Mort by PHU-Cause-Sex.dta", replace browse ******************************************************************************** ******************************************************************************** ***************************************** **** Erase extra files if created *** ***************************************** *erase "`source'/Avoidable Mortality File 1.dta" *erase "`source'/Avoidable Mortality File 2.dta" *erase "`source'/Avoidable Mortality File 3.dta" erase "`source'/preventable.dta" erase "`source'/treatable.dta" erase "`source'/square1_preventable.dta" erase "`source'/square1_treatable.dta" erase "`source'/Assinged Halves.dta" erase "`source'/Prev-Treat Mort by PHU-Cause-Sex-Age.dta" *********************************** ******** END OF FILE ******** ***********************************