*Frank Baumgartner *Jan 8, 2021 *Start with Jacob Kaplan's SHR data file *Calculate by county and year the numbers of homicides that could be considered aggravated or death eligible in some states *Multiple victims, young or old victim, underlying felony, weapon other than a gun *Make a variable counting such crimes by year and by county; then cumulatively by county; and also by state x year; and cumulatively by state. *Make another variable adjusting the list of aggravators by state, since states have different lists of eligible crimes. cd G:\FB\DP\CountyMortality\Kaplan-SHR-1976-2019\AggravatedHomicideCounts clear use "G:\FB\DP\CountyMortality\Kaplan-SHR-1976-2019\Data\shr_1976_2019.dta" *Note these are all homicide "incidents" so there are multiple offenders and victims *Since we are summing all these variables to geographical units, we can take the sums of *the variables, not just indicators for presence or absence *First, eliminate all the negligent homicides drop if homicide_type == "manslaughter by negligence" *Victim race summaries gen Vic1W = 1 if victim_1_race=="white" gen Vic2W = 1 if victim_2_race=="white" gen Vic3W = 1 if victim_3_race=="white" gen Vic4W = 1 if victim_4_race=="white" gen Vic5W = 1 if victim_5_race=="white" gen Vic6W = 1 if victim_6_race=="white" gen Vic7W = 1 if victim_7_race=="white" gen Vic8W = 1 if victim_8_race=="white" gen Vic9W = 1 if victim_9_race=="white" gen Vic10W = 1 if victim_10_race=="white" gen Vic11W = 1 if victim_11_race=="white" recode Vic1W-Vic11W (.=0) gen VicsWhite = Vic1W + Vic2W + Vic3W + Vic4W +Vic5W + Vic6W + Vic7W + Vic8W + Vic9W + Vic10W + Vic11W gen Vic1B = 1 if victim_1_race=="black" gen Vic2B = 1 if victim_2_race=="black" gen Vic3B = 1 if victim_3_race=="black" gen Vic4B = 1 if victim_4_race=="black" gen Vic5B = 1 if victim_5_race=="black" gen Vic6B = 1 if victim_6_race=="black" gen Vic7B = 1 if victim_7_race=="black" gen Vic8B = 1 if victim_8_race=="black" gen Vic9B = 1 if victim_9_race=="black" gen Vic10B = 1 if victim_10_race=="black" gen Vic11B = 1 if victim_11_race=="black" recode Vic1B-Vic11B (.=0) gen VicsBlack= Vic1B + Vic2B + Vic3B + Vic4B +Vic5B + Vic6B + Vic7B + Vic8B + Vic9B + Vic10B + Vic11B gen Vic1M = 1 if victim_1_sex=="male" gen Vic2M = 1 if victim_2_sex=="male" gen Vic3M = 1 if victim_3_sex=="male" gen Vic4M = 1 if victim_4_sex=="male" gen Vic5M = 1 if victim_5_sex=="male" gen Vic6M = 1 if victim_6_sex=="male" gen Vic7M = 1 if victim_7_sex=="male" gen Vic8M = 1 if victim_8_sex=="male" gen Vic9M = 1 if victim_9_sex=="male" gen Vic10M = 1 if victim_10_sex=="male" gen Vic11M = 1 if victim_11_sex=="male" recode Vic1M-Vic11M (.=0) gen VicsMale= Vic1M + Vic2M + Vic3M + Vic4M +Vic5M + Vic6M + Vic7M + Vic8M + Vic9M + Vic10M + Vic11M gen Vic1F = 1 if victim_1_sex=="female" gen Vic2F = 1 if victim_2_sex=="female" gen Vic3F = 1 if victim_3_sex=="female" gen Vic4F = 1 if victim_4_sex=="female" gen Vic5F = 1 if victim_5_sex=="female" gen Vic6F = 1 if victim_6_sex=="female" gen Vic7F = 1 if victim_7_sex=="female" gen Vic8F = 1 if victim_8_sex=="female" gen Vic9F = 1 if victim_9_sex=="female" gen Vic10F = 1 if victim_10_sex=="female" gen Vic11F = 1 if victim_11_sex=="female" recode Vic1F-Vic11F (.=0) gen VicsFemale= Vic1F + Vic2F + Vic3F + Vic4F +Vic5F + Vic6F + Vic7F + Vic8F + Vic9F + Vic10F + Vic11F tab1 VicsWhite VicsBlack VicsMale VicsFemale, miss *Combined Race / Ethnicity: White = White, Not Hispanic, Black Black Not Hispanic, Latinx = Hispanic *xxx need to do this still *Multiple victims gen temp1 = 1 if victim_2_race=="" & victim_2_age =="" & victim_2_sex =="" & victim_2_race =="" & victim_2_ethnic_origin =="" gen VicMultiple = 1 if temp1 ==. tab situation VicMultiple, miss *Victim Ages rename victim_1_age VicAge1 rename victim_2_age VicAge2 rename victim_3_age VicAge3 rename victim_4_age VicAge4 rename victim_5_age VicAge5 rename victim_6_age VicAge6 rename victim_7_age VicAge7 rename victim_8_age VicAge8 rename victim_9_age VicAge9 rename victim_10_age VicAge10 rename victim_11_age VicAge11 replace VicAge1 ="99" if VicAge1 == "99 years or older" replace VicAge1 ="1" if VicAge1 == "birth to 6 days, including abandoned infant" replace VicAge1 ="1" if VicAge1 == "7 days to 364 days" replace VicAge1 ="" if VicAge1 == "0" replace VicAge1 ="1" if VicAge1 == "nn" replace VicAge1 ="" if VicAge1 == "unknown" replace VicAge1 ="1" if VicAge1 == "nn" replace VicAge2 ="99" if VicAge2 == "99 years or older" replace VicAge2 ="1" if VicAge2 == "birth to 6 days, including abandoned infant" replace VicAge2 ="1" if VicAge2 == "7 days to 364 days" replace VicAge2 ="" if VicAge2 == "0" replace VicAge2 ="1" if VicAge2 == "nn" replace VicAge2 ="" if VicAge2 == "unknown" replace VicAge2 ="1" if VicAge2 == "nn" replace VicAge3 ="99" if VicAge3 == "99 years or older" replace VicAge3 ="1" if VicAge3 == "birth to 6 days, including abandoned infant" replace VicAge3 ="1" if VicAge3 == "7 days to 364 days" replace VicAge3 ="" if VicAge3 == "0" replace VicAge3 ="1" if VicAge3 == "nn" replace VicAge3 ="" if VicAge3 == "unknown" replace VicAge3 ="1" if VicAge3 == "nn" replace VicAge4 ="99" if VicAge4 == "99 years or older" replace VicAge4 ="1" if VicAge4 == "birth to 6 days, including abandoned infant" replace VicAge4 ="1" if VicAge4 == "7 days to 364 days" replace VicAge4 ="" if VicAge4 == "0" replace VicAge4 ="1" if VicAge4 == "nn" replace VicAge4 ="" if VicAge4 == "unknown" replace VicAge4 ="1" if VicAge4 == "nn" replace VicAge5 ="99" if VicAge5 == "99 years or older" replace VicAge5 ="1" if VicAge5 == "birth to 6 days, including abandoned infant" replace VicAge5 ="1" if VicAge5 == "7 days to 364 days" replace VicAge5 ="" if VicAge5 == "0" replace VicAge5 ="1" if VicAge5 == "nn" replace VicAge5 ="" if VicAge5 == "unknown" replace VicAge5 ="1" if VicAge5 == "nn" replace VicAge6 ="99" if VicAge6 == "99 years or older" replace VicAge6 ="1" if VicAge6 == "birth to 6 days, including abandoned infant" replace VicAge6 ="1" if VicAge6 == "7 days to 364 days" replace VicAge6 ="" if VicAge6 == "0" replace VicAge6 ="1" if VicAge6 == "nn" replace VicAge6 ="" if VicAge6 == "unknown" replace VicAge6 ="1" if VicAge6 == "nn" replace VicAge7 ="99" if VicAge7 == "99 years or older" replace VicAge7 ="1" if VicAge7 == "birth to 6 days, including abandoned infant" replace VicAge7 ="1" if VicAge7 == "7 days to 364 days" replace VicAge7 ="" if VicAge7 == "0" replace VicAge7 ="1" if VicAge7 == "nn" replace VicAge7 ="" if VicAge7 == "unknown" replace VicAge7 ="1" if VicAge7 == "nn" replace VicAge8 ="99" if VicAge8 == "99 years or older" replace VicAge8 ="1" if VicAge8 == "birth to 6 days, including abandoned infant" replace VicAge8 ="1" if VicAge8 == "7 days to 364 days" replace VicAge8 ="" if VicAge8 == "0" replace VicAge8 ="1" if VicAge8 == "nn" replace VicAge8 ="" if VicAge8 == "unknown" replace VicAge8 ="1" if VicAge8 == "nn" replace VicAge9 ="99" if VicAge9 == "99 years or older" replace VicAge9 ="1" if VicAge9 == "birth to 6 days, including abandoned infant" replace VicAge9 ="1" if VicAge9 == "7 days to 364 days" replace VicAge9 ="" if VicAge9 == "0" replace VicAge9 ="1" if VicAge9 == "nn" replace VicAge9 ="" if VicAge9 == "unknown" replace VicAge9 ="1" if VicAge9 == "nn" replace VicAge10 ="99" if VicAge10 == "99 years or older" replace VicAge10 ="1" if VicAge10 == "birth to 6 days, including abandoned infant" replace VicAge10 ="1" if VicAge10 == "7 days to 364 days" replace VicAge10 ="" if VicAge10 == "0" replace VicAge10 ="1" if VicAge10 == "nn" replace VicAge10 ="" if VicAge10 == "unknown" replace VicAge10 ="1" if VicAge10 == "nn" replace VicAge11 ="99" if VicAge11 == "99 years or older" replace VicAge11 ="1" if VicAge11 == "birth to 6 days, including abandoned infant" replace VicAge11 ="1" if VicAge11 == "7 days to 364 days" replace VicAge11 ="" if VicAge11 == "0" replace VicAge11 ="1" if VicAge11 == "nn" replace VicAge11 ="" if VicAge11 == "unknown" replace VicAge11 ="1" if VicAge11 == "nn" destring VicAge1 - VicAge11, replace *Offender race, age, gender * gen Off1W = 1 if offender_1_race=="white" gen Off2W = 1 if offender_2_race=="white" gen Off3W = 1 if offender_3_race=="white" gen Off4W = 1 if offender_4_race=="white" gen Off5W = 1 if offender_5_race=="white" gen Off6W = 1 if offender_6_race=="white" gen Off7W = 1 if offender_7_race=="white" gen Off8W = 1 if offender_8_race=="white" gen Off9W = 1 if offender_9_race=="white" gen Off10W = 1 if offender_10_race=="white" gen Off11W = 1 if offender_11_race=="white" recode Off1W-Off11W (.=0) gen OffsWhite = Off1W + Off2W + Off3W + Off4W +Off5W + Off6W + Off7W + Off8W + Off9W + Off10W + Off11W gen Off1B = 1 if offender_1_race=="black" gen Off2B = 1 if offender_2_race=="black" gen Off3B = 1 if offender_3_race=="black" gen Off4B = 1 if offender_4_race=="black" gen Off5B = 1 if offender_5_race=="black" gen Off6B = 1 if offender_6_race=="black" gen Off7B = 1 if offender_7_race=="black" gen Off8B = 1 if offender_8_race=="black" gen Off9B = 1 if offender_9_race=="black" gen Off10B = 1 if offender_10_race=="black" gen Off11B = 1 if offender_11_race=="black" recode Off1B-Off11B (.=0) gen OffsBlack= Off1B + Off2B + Off3B + Off4B +Off5B + Off6B + Off7B + Off8B + Off9B + Off10B + Off11B *Offender - Victim Combinations gen OffVicWW = 1 if OffsWhite>0 & VicsWhite>0 gen OffVicBB = 1 if OffsBlack>0 & VicsBlack>0 gen OffVicWB = 1 if OffsWhite>0 & VicsBlack>0 gen OffVicBW = 1 if OffsBlack>0 & VicsWhite>0 *Various aggravating circumstances *Multiple victims *Child and Elderly Victims gen Vic1Child = 1 if VicAge1<12 gen Vic2Child = 1 if VicAge2<12 gen Vic3Child = 1 if VicAge3<12 gen Vic4Child = 1 if VicAge4<12 gen Vic5Child = 1 if VicAge5<12 gen Vic6Child = 1 if VicAge6<12 gen Vic7Child = 1 if VicAge7<12 gen Vic8Child = 1 if VicAge8<12 gen Vic9Child = 1 if VicAge9<12 gen Vic10Child = 1 if VicAge10<12 gen Vic11Child = 1 if VicAge11<12 recode Vic1Child - Vic11Child (.=0) gen VicsChild = Vic1Child + Vic2Child + Vic3Child + Vic4Child + Vic5Child + Vic6Child + Vic7Child + Vic8Child + Vic9Child + Vic10Child + Vic11Child gen Vic1Elderly = 1 if VicAge1>64 & VicAge1!=. gen Vic2Elderly = 1 if VicAge2>64 & VicAge2!=. gen Vic3Elderly = 1 if VicAge3>64 & VicAge3!=. gen Vic4Elderly = 1 if VicAge4>64 & VicAge4!=. gen Vic5Elderly = 1 if VicAge5>64 & VicAge5!=. gen Vic6Elderly = 1 if VicAge6>64 & VicAge6!=. gen Vic7Elderly = 1 if VicAge7>64 & VicAge7!=. gen Vic8Elderly = 1 if VicAge8>64 & VicAge8!=. gen Vic9Elderly = 1 if VicAge9>64 & VicAge9!=. gen Vic10Elderly = 1 if VicAge10>64 & VicAge10!=. gen Vic11Elderly = 1 if VicAge11>64 & VicAge11!=. recode Vic1Elderly - Vic11Elderly (.=0) gen VicsElderly = Vic1Elderly + Vic2Elderly + Vic3Elderly + Vic4Elderly + Vic5Elderly + Vic6Elderly + Vic7Elderly + Vic8Elderly + Vic9Elderly + Vic10Elderly + Vic11Elderly *Rape Felony Circumstance gen Off1Rape = 1 if offender_1_circumstance=="rape" gen Off2Rape = 1 if offender_2_circumstance=="rape" gen Off3Rape = 1 if offender_3_circumstance=="rape" gen Off4Rape = 1 if offender_4_circumstance=="rape" gen Off5Rape = 1 if offender_5_circumstance=="rape" gen Off6Rape = 1 if offender_6_circumstance=="rape" gen Off7Rape = 1 if offender_7_circumstance=="rape" gen Off8Rape = 1 if offender_8_circumstance=="rape" gen Off9Rape = 1 if offender_9_circumstance=="rape" gen Off10Rape = 1 if offender_10_circumstance=="rape" gen Off11Rape = 1 if offender_11_circumstance=="rape" recode Off1Rape - Off11Rape (.=0) gen FelonyRape = Off1Rape + Off2Rape + Off3Rape + Off4Rape + Off5Rape + Off6Rape + Off7Rape + Off8Rape + Off9Rape + Off10Rape + Off11Rape tab FelonyRape *Any Felony Circumstance gen Off1Felony = 1 if offender_1_circumstance=="all suspected felony type" | offender_1_circumstance=="burglary" | offender_1_circumstance=="gangland killings" | offender_1_circumstance=="juvenile gang killings" | offender_1_circumstance=="larceny" | offender_1_circumstance=="narcotic drug laws" |offender_1_circumstance=="other felony type - not specified" | offender_1_circumstance=="other sex offenses" | offender_1_circumstance=="rape" | offender_1_circumstance=="robbery" gen Off2Felony = 1 if offender_2_circumstance=="all suspected felony type" | offender_2_circumstance=="burglary" | offender_2_circumstance=="gangland killings" | offender_2_circumstance=="juvenile gang killings" | offender_2_circumstance=="larceny" | offender_2_circumstance=="narcotic drug laws" |offender_2_circumstance=="other felony type - not specified" | offender_2_circumstance=="other sex offenses" | offender_2_circumstance=="rape" | offender_2_circumstance=="robbery" gen Off3Felony = 1 if offender_3_circumstance=="all suspected felony type" | offender_3_circumstance=="burglary" | offender_3_circumstance=="gangland killings" | offender_3_circumstance=="juvenile gang killings" | offender_3_circumstance=="larceny" | offender_3_circumstance=="narcotic drug laws" |offender_3_circumstance=="other felony type - not specified" | offender_3_circumstance=="other sex offenses" | offender_3_circumstance=="rape" | offender_3_circumstance=="robbery" gen Off4Felony = 1 if offender_4_circumstance=="all suspected felony type" | offender_4_circumstance=="burglary" | offender_4_circumstance=="gangland killings" | offender_4_circumstance=="juvenile gang killings" | offender_4_circumstance=="larceny" | offender_4_circumstance=="narcotic drug laws" |offender_4_circumstance=="other felony type - not specified" | offender_4_circumstance=="other sex offenses" | offender_4_circumstance=="rape" | offender_4_circumstance=="robbery" gen Off5Felony = 1 if offender_5_circumstance=="all suspected felony type" | offender_5_circumstance=="burglary" | offender_5_circumstance=="gangland killings" | offender_5_circumstance=="juvenile gang killings" | offender_5_circumstance=="larceny" | offender_5_circumstance=="narcotic drug laws" |offender_5_circumstance=="other felony type - not specified" | offender_5_circumstance=="other sex offenses" | offender_5_circumstance=="rape" | offender_5_circumstance=="robbery" gen Off6Felony = 1 if offender_6_circumstance=="all suspected felony type" | offender_6_circumstance=="burglary" | offender_6_circumstance=="gangland killings" | offender_6_circumstance=="juvenile gang killings" | offender_6_circumstance=="larceny" | offender_6_circumstance=="narcotic drug laws" |offender_6_circumstance=="other felony type - not specified" | offender_6_circumstance=="other sex offenses" | offender_6_circumstance=="rape" | offender_6_circumstance=="robbery" gen Off7Felony = 1 if offender_7_circumstance=="all suspected felony type" | offender_7_circumstance=="burglary" | offender_7_circumstance=="gangland killings" | offender_7_circumstance=="juvenile gang killings" | offender_7_circumstance=="larceny" | offender_7_circumstance=="narcotic drug laws" |offender_7_circumstance=="other felony type - not specified" | offender_7_circumstance=="other sex offenses" | offender_7_circumstance=="rape" | offender_7_circumstance=="robbery" gen Off8Felony = 1 if offender_8_circumstance=="all suspected felony type" | offender_8_circumstance=="burglary" | offender_8_circumstance=="gangland killings" | offender_8_circumstance=="juvenile gang killings" | offender_8_circumstance=="larceny" | offender_8_circumstance=="narcotic drug laws" |offender_8_circumstance=="other felony type - not specified" | offender_8_circumstance=="other sex offenses" | offender_8_circumstance=="rape" | offender_8_circumstance=="robbery" gen Off9Felony = 1 if offender_9_circumstance=="all suspected felony type" | offender_9_circumstance=="burglary" | offender_9_circumstance=="gangland killings" | offender_9_circumstance=="juvenile gang killings" | offender_9_circumstance=="larceny" | offender_9_circumstance=="narcotic drug laws" |offender_9_circumstance=="other felony type - not specified" | offender_9_circumstance=="other sex offenses" | offender_9_circumstance=="rape" | offender_9_circumstance=="robbery" gen Off10Felony = 1 if offender_10_circumstance=="all suspected felony type" | offender_10_circumstance=="burglary" | offender_10_circumstance=="gangland killings" | offender_10_circumstance=="juvenile gang killings" | offender_10_circumstance=="larceny" | offender_10_circumstance=="narcotic drug laws" |offender_10_circumstance=="other felony type - not specified" | offender_10_circumstance=="other sex offenses" | offender_10_circumstance=="rape" | offender_10_circumstance=="robbery" gen Off11Felony = 1 if offender_11_circumstance=="all suspected felony type" | offender_11_circumstance=="burglary" | offender_11_circumstance=="gangland killings" | offender_11_circumstance=="juvenile gang killings" | offender_11_circumstance=="larceny" | offender_11_circumstance=="narcotic drug laws" |offender_11_circumstance=="other felony type - not specified" | offender_11_circumstance=="other sex offenses" | offender_11_circumstance=="rape" | offender_11_circumstance=="robbery" recode Off1Felony - Off11Felony (.=0) gen Felony = Off1Felony + Off2Felony + Off3Felony + Off4Felony + Off5Felony + Off6Felony + Off7Felony + Off8Felony + Off9Felony + Off10Felony + Off11Felony tab Felony *weapon is a gun gen Off1WeaponGun =1 if offender_1_weapon=="handgun" | offender_1_weapon=="other gun" | offender_1_weapon=="rifle" | offender_1_weapon=="shotgun" gen Off2WeaponGun =1 if offender_2_weapon=="handgun" | offender_2_weapon=="other gun" | offender_2_weapon=="rifle" | offender_2_weapon=="shotgun" gen Off3WeaponGun =1 if offender_3_weapon=="handgun" | offender_3_weapon=="other gun" | offender_3_weapon=="rifle" | offender_3_weapon=="shotgun" gen Off4WeaponGun =1 if offender_4_weapon=="handgun" | offender_4_weapon=="other gun" | offender_4_weapon=="rifle" | offender_4_weapon=="shotgun" gen Off5WeaponGun =1 if offender_5_weapon=="handgun" | offender_5_weapon=="other gun" | offender_5_weapon=="rifle" | offender_5_weapon=="shotgun" gen Off6WeaponGun =1 if offender_6_weapon=="handgun" | offender_6_weapon=="other gun" | offender_6_weapon=="rifle" | offender_6_weapon=="shotgun" gen Off7WeaponGun =1 if offender_7_weapon=="handgun" | offender_7_weapon=="other gun" | offender_7_weapon=="rifle" | offender_7_weapon=="shotgun" gen Off8WeaponGun =1 if offender_8_weapon=="handgun" | offender_8_weapon=="other gun" | offender_8_weapon=="rifle" | offender_8_weapon=="shotgun" gen Off9WeaponGun =1 if offender_9_weapon=="handgun" | offender_9_weapon=="other gun" | offender_9_weapon=="rifle" | offender_9_weapon=="shotgun" gen Off10WeaponGun =1 if offender_10_weapon=="handgun" | offender_10_weapon=="other gun" | offender_10_weapon=="rifle" | offender_10_weapon=="shotgun" gen Off11WeaponGun =1 if offender_11_weapon=="handgun" | offender_11_weapon=="other gun" | offender_11_weapon=="rifle" | offender_11_weapon=="shotgun" recode Off1WeaponGun - Off11WeaponGun (.=0) gen WeaponGun = Off1WeaponGun + Off2WeaponGun + Off3WeaponGun + Off4WeaponGun + Off5WeaponGun + Off6WeaponGun + Off7WeaponGun + Off8WeaponGun + Off9WeaponGun + Off10WeaponGun + Off11WeaponGun tab WeaponGun *weapon other than a gun gen Off1WeaponNotGun =1 if offender_1_weapon != "" & Off1WeaponGun==0 gen Off2WeaponNotGun =1 if offender_2_weapon != "" & Off2WeaponGun==0 gen Off3WeaponNotGun =1 if offender_3_weapon != "" & Off3WeaponGun==0 gen Off4WeaponNotGun =1 if offender_4_weapon != "" & Off4WeaponGun==0 gen Off5WeaponNotGun =1 if offender_5_weapon != "" & Off5WeaponGun==0 gen Off6WeaponNotGun =1 if offender_6_weapon != "" & Off6WeaponGun==0 gen Off7WeaponNotGun =1 if offender_7_weapon != "" & Off7WeaponGun==0 gen Off8WeaponNotGun =1 if offender_8_weapon != "" & Off8WeaponGun==0 gen Off9WeaponNotGun =1 if offender_9_weapon != "" & Off9WeaponGun==0 gen Off10WeaponNotGun =1 if offender_10_weapon != "" & Off10WeaponGun==0 gen Off11WeaponNotGun =1 if offender_11_weapon != "" & Off11WeaponGun==0 recode Off1WeaponNotGun - Off11WeaponNotGun (.=0) gen WeaponNotGun = Off1WeaponNotGun + Off2WeaponNotGun + Off3WeaponNotGun + Off4WeaponNotGun + Off5WeaponNotGun + Off6WeaponNotGun + Off7WeaponNotGun + Off8WeaponNotGun + Off9WeaponNotGun + Off10WeaponNotGun + Off11WeaponNotGun tab WeaponNotGun *Justified homicides gen Off1Justified = 1 if offender_1_circumstance== "felon killed by police" | offender_1_circumstance=="felon killed by private citizen" gen Off2Justified = 1 if offender_2_circumstance== "felon killed by police" | offender_2_circumstance=="felon killed by private citizen" gen Off3Justified = 1 if offender_3_circumstance== "felon killed by police" | offender_3_circumstance=="felon killed by private citizen" gen Off4Justified = 1 if offender_4_circumstance== "felon killed by police" | offender_4_circumstance=="felon killed by private citizen" gen Off5Justified = 1 if offender_5_circumstance== "felon killed by police" | offender_5_circumstance=="felon killed by private citizen" gen Off6Justified = 1 if offender_6_circumstance== "felon killed by police" | offender_6_circumstance=="felon killed by private citizen" gen Off7Justified = 1 if offender_7_circumstance== "felon killed by police" | offender_7_circumstance=="felon killed by private citizen" gen Off8Justified = 1 if offender_8_circumstance== "felon killed by police" | offender_8_circumstance=="felon killed by private citizen" gen Off9Justified = 1 if offender_9_circumstance== "felon killed by police" | offender_9_circumstance=="felon killed by private citizen" gen Off10Justified = 1 if offender_10_circumstance== "felon killed by police" | offender_10_circumstance=="felon killed by private citizen" gen Off11Justified = 1 if offender_11_circumstance== "felon killed by police" | offender_11_circumstance=="felon killed by private citizen" recode Off1Justified - Off11Justified (.=0) gen Justified = Off1Justified + Off2Justified + Off3Justified + Off4Justified + Off5Justified + Off6Justified + Off7Justified + Off8Justified + Off9Justified + Off10Justified + Off11Justified tab Justified, miss *Sum variable for nonjustified homicides *Total offenders *Minus offenders killed by police or felons killed by private citizens gen TotalOffenders = additional_offender_count+1 gen TotalUnjustifiedOffenders = TotalOffenders-Justified tab1 Total* gen VicsTotal = additional_victim_count+1 *Number of aggravators, multiple victims, felony circumstance, elderly or child victim, or weapon other than gun gen AggAny = 1 if VicMultiple == 1 | VicsChild > 0 | VicsElderly > 0 | Felony == 1 | WeaponNotGun > 0 gen AggNumber = VicsTotal + VicsChild + VicsElderly + WeaponNotGun *Change the missings to zeros recode WeaponNotGun WeaponGun Felony FelonyRape VicsWhite VicsBlack VicsMale VicsFemale VicMultiple VicsTotal VicsChild VicsElderly OffVic* AggAny AggNumber TotalOffenders Justified TotalUnjustifiedOffenders (.=0) *Count variable for all homicide incidents gen Incidents=1 save SHR-Aggravated-1976-2019.dta, replace clear use SHR-Aggravated-1976-2019.dta collapse (last) state_abb (sum) Incidents AggNumber AggAny Justified WeaponNotGun WeaponGun VicsWhite VicsBlack VicsMale VicsFemale VicMultiple VicsTotal VicsChild VicsElderly Felony* Total* OffsWhite OffsBlack OffVic* , by(fips_state_code year) drop if fips_state_code == "" drop if fips_state_code =="66" order fips_state_code state_abb year Incidents VicsTotal VicMultiple TotalOffenders Justified TotalUnjustifiedOffenders VicsWhite VicsBlack VicsMale VicsFemale VicsChild VicsElderly OffsWhite OffsBlack OffVicWW OffVicBB OffVicWB OffVicBW AggAny AggNumber Felony FelonyRape WeaponNotGun WeaponGun *Note: There are some clear anamolies here, from missing data. Perhaps we should change those observations to misssing if the total number of incidents indicates a failure to report. Or interpolate? save SHR-Aggravated-1976-2019-state-year.dta, replace clear use SHR-Aggravated-1976-2019.dta collapse (sum) Incidents AggNumber AggAny Justified WeaponNotGun WeaponGun VicsWhite VicsBlack VicsMale VicsFemale VicMultiple VicsTotal VicsChild VicsElderly Felony* Total* OffsWhite OffsBlack OffVic* , by( year) order year Incidents VicsTotal VicMultiple TotalOffenders Justified TotalUnjustifiedOffenders VicsWhite VicsBlack VicsMale VicsFemale VicsChild VicsElderly OffsWhite OffsBlack OffVicWW OffVicBB OffVicWB OffVicBW AggAny AggNumber Felony FelonyRape WeaponNotGun WeaponGun save SHR-Aggravated-1976-2019-yearly.dta, replace clear use SHR-Aggravated-1976-2019.dta gen fips = fips_state_code+fips_county_code collapse (last) state_abb census_name (sum) Incidents VicsTotal VicMultiple TotalOffenders Justified TotalUnjustifiedOffenders VicsWhite VicsBlack VicsMale VicsFemale VicsChild VicsElderly OffsWhite OffsBlack OffVicWW OffVicBB OffVicWB OffVicBW AggAny AggNumber Felony FelonyRape WeaponNotGun WeaponGun , by( fips) order fips Incidents VicsTotal VicMultiple TotalOffenders Justified TotalUnjustifiedOffenders VicsWhite VicsBlack VicsMale VicsFemale VicsChild VicsElderly OffsWhite OffsBlack OffVicWW OffVicBB OffVicWB OffVicBW AggAny AggNumber Felony FelonyRape WeaponNotGun WeaponGun drop if fips == "" save SHR-Aggravated-1976-2019-fips.dta, replace