Training and Technical Assistance Webinar Series Use of Administrative Records to Analyze Drug Abuse and Enforcement May 21, 2015 Justice Research and Statistics Association 720 7th Street, NW, Third Floor Washington, DC 20001 Training and Technical Assistance Webinar Series This webinar is being audio cast via the speakers on your computer and via teleconference. To access the audio stream via your computer speakers, select Communicate then audio stream. Once the audio broadcast window appears, press play (arrow button) to start the audio stream. Justice Research and Statistics Association 720 7th Street, NW, Third Floor Washington, DC 20001 Training and Technical Assistance Webinar Series If you do not have speakers or would prefer to use your phone, the call-in number can be found in the following places: - At the end of your registration email - On the “Event Info” tab on the top left side of your screen. Justice Research and Statistics Association 720 7th Street, NW, Third Floor Washington, DC 20001 Training and Technical Assistance Webinar Series All telephones have been muted. If you dialed *6 to unmuted your phone, please dial *6 to re-mute your phone If you would like to ask a question please use the chat feature. Please remember to select Host, Presenter & Panelists Justice Research and Statistics Association 720 7th Street, NW, Third Floor Washington, DC 20001 Administrative Data: Challenges and Techniques For Use to Assess Drug Crime P R E PA RED BY SA M U E L G O N ZA L ES O P E R ATI ONS A N A LYST W I T H T H E STAT ISTIC A L A N A LYS IS C E N T E R AT T HE CR I M I N A L J U ST I C E CO O R D I N AT I NG CO U N C I L Administrative Data: Challenges and Techniques Goals •To introduce participants to 3 administrative data sets used for the Georgia Statewide Drug Enforcement Strategy •Highlight challenges in our analysis and the technical solutions we use to overcome those challenges •Provide insight for the need for data surveillance •Spark a conversation on how to tackle administrative data Administrative Data: Challenges and Techniques Data Sets The Georgia Department of Corrections Administrative Data • We focused on Intake data collected by the Georgia Department of Corrections from 2009 to 2013. Drug Overdose Deaths Data • The drug overdose data was collected from the Medical Examiner’s Offices at the Georgia Bureau of Investigation and Cobb, DeKalb, Fulton and Gwinnett counties from 2010 to 2013. • The data does not include all individuals from County Coroner’s Offices. Only those deaths referred to the Medical Examiner’s Offices. • The data did not include toxicity levels, so if multiple drugs were identified, we cannot attribute death to one drug. Treatment Episode Data Set • The data is collected by the Georgia Department of Behavioral Health and Developmental Disabilities (DBHDD) for the Substance Abuse and Mental Health Services Administration (SAMHSA). • The data is for only criminal justice initiated treatment episodes Georgia Department of Corrections Data •Had a variable that indicated Drug Possession or Drug Sales •Had another variable that better described the crime and included the drug •Problem was that we wanted to do a cross-tabulation of drug by crime type but the information was in the same variable. Georgia Department of Corrections Data •Solved by Cleaning the data in Excel •Filtered to see common phrases •Searched and removed unwanted words like “of” •Searched the common phrases and replaced them with an added comma •Then text to columns and we have separated the drug from primary offense Georgia Department of Corrections Data Drug by Primary Offense from 2009-2013 Drug Type MANUFACTURE POSSESSION POSSESSION WITH INTENT TO DISTRIBUTE COCAINE 0 3964 1615 2753 1004 0 9336 MARIJUANA 0 449 2909 1349 258 0 4965 666 1922 935 473 675 0 4671 0 305 0 99 45 0 449 0 68 0 23 70 0 161 0 56 0 0 0 0 56 EPHEDRINE 0 36 0 0 0 0 36 AMPHETAMINE 0 0 0 0 14 0 14 LSD 0 4 0 1 0 0 5 0 0 0 1 0 0 1 0 0 362 0 0 0 362 UNKNOWN 81 380 0 562 75 1567 2665 Totals 747 7184 5821 5261 2141 1567 22721 METH AMPHETAMINE NARCOTICS MDMA/ EXTACY PARAPHENALIA COUTERFIT DRUGS OTHER SALE AND DISTRIBUTION TRAFFICING OTHER Totals Overdose Deaths Data • We wanted to get an idea of the drugs used most in combination Overdose Deaths Data Top 20 Drugs Found in Toxicology Reports from 2010-2013 1400 1200 1000 800 600 400 200 0 1168 962 752 673 623 513 388 312 307 274 259 200 193 190 186 168 155 155 148 145 Overdose Deaths Data •Needed to convert all drug names, which were “string” variables, to numeric variables •The catch was that you have to recode all drug variables the same and not use auto recode so you can do a multiple response set analysis in SPSS •Once we had each individual drug found in toxicology reports as separate variables with associated analysis values, we could run a frequency using the multiple response commands in SPSS •Separating each drug into its own variable also allowed us to determine which drugs were most frequently found in combination Overdose Deaths Data Overdose Deaths Data Syntax for Recoding a Drug RECODE DrugA ('1,1-Difluoroethane'=1) ('1,3-Dimethylamylamine'=2) ('25I-NBOMe'=3) ('3,4-Methylenedioxyamphetamine'=4) ('10-Monoacetyl Morphine'=5) ('7-Amino'=10) ('Acetaminophen'=7) ('Adderall'=8) ('Alpha-Hydroxyalprazolam'=9) ('Alprazolam'=10) ('Amitriptyline'=11) ('Amlodipine'=12) ('Amoxatine'=13) ('Amphetamine'=14) ('Anaphylaxis'=15) ('Aripiprazole'=16) ('Aspirin'=17) ('Atomoxetine'=18) ('Baclofen'=19) ('Barbiturate'=20) ('Benzodiazepine'=21) ('Benzonatate'=22) ('Benzoylecgonine'=23) ('Benztropine'=24) ('Brompheniramine'=25) ('Bupivacaine'=26) ('Buprenorphine'=27) ('Bupropion'=28) ('Buspirone'=29) ('Butalbital'=30) ('Butalnotal'=31) ('Caffeine'=32) ('Carbamazepine'=33) ('Carboxyhemoglobin'=34) ('Carisoprodol'=35) ('Chloral Hydrate'=36) ('Chlorazepine'=37) ('Chlordiazepoxide'=38) ('Chlorethan'=39) ('Chloroquine'=40) ('Chlorpheniramine'=41) ('Chlorpromazine'=42) ('Citalopram'=43) ('Clomipramine'=44) ('Clonazepam'=45) ('Clonidine'=46) ('Clozapine'=47) ('Cocaethylene'=48) ('Cocaine'=49) ('Codeine'=50) ('Cotinine'=51) ('Cyclobenzaprine'=52) ('Desipramine'=53) ('Desmethldoxepin'=54) ('Destromethorphan'=55) ('Desvenlafaxine'=56) ('Detromethophan'=57) ('Dextromethorphan'=58) ('Diazepam'=59) ('Difluoroethane'=60) ('Diltiazem'=61) ('Diphenhydramine'=62) ('Donepezil'=63) ('Doxepin'=64) ('Doxylamine'=65) ('Duloxetine'=66) ('Ecstasy'=67) ('EDDP'=68) ('Ephedrine'=69) ('Estazolam'=70) ('Ethylene Glycol'=71) ('Fentanyl'=72) ('Flecainide'=73) ('Fluoxetine'=74) ('Fluphenazine'=75) ('Fluvoxamine'=76) ('Gabapentin'=77) ('GHB'=78) ('Guetiapine'=79) ('Haloperidol'=80) ('Helium'=81) ('Heroin'=82) ('Hydrocodone'=83) ('Hydromorphone'=84) ('Hydroxychloroquine'=85) ('Hydroxyzine'=86) ('Imipramine'=87) ('Insulin'=88) ('Isobutyl Nitrite'=89) ('Isopropanol'=90) ('Ketamine'=91) ('Kratom'=92) ('Lamotrigine'=93) ('Levetiracetam'=94) ('Lidocaine'=95) ('Lidoderm'=96) ('Lithium'=97) ('Lorazepam'=98) ('Loxapine'=99) ('Meclizine'=100) ('Meperidine'=101) ('Meprobamate'=102) ('Mesoridazine'=103) ('Metabolite'=104) ('Metaclopramide'=105) ('Metaxalone'=106) ('Metclopramide'=107) ('Methadone'=108) ('Methamphetamine'=109) ('Methocarbamol'=110) ('Methodone'=111) ('Methorphan'=112) ('Methotrimeprazine'=113) ('Methylone'=114) ('Metoclopramide'=115) ('Metoprolol'=116) ('Midazolam'=117) ('Mirtazapine'=118) ('Morphine'=119) ('Multiple Drug'=120) ('Naloxone'=121) ('Nicotine'=122) ('Nifedipine'=123) ('Nonvenlafaxine'=124) ('Norbuprenorphine'=125) ('Nordiazepam'=126) ('Nordiazpam'=127)('Norfentanyl'=128) ('Norketamine'=129) ('Normeperidine'=130) ('Norpropoxyphene'=131) ('Nortriptyline'=132) ('Norvenlafaxine'=133) ('Olanzapine'=134) ('Opiate'=135) ('Opiates'=135) ('Orphenadrine'=137) ('Oxazepam'=138) ('Oxycodone'=139) ('Oxymorphone'=140) ('Paroxetine'=141) ('Perphenazine'=142) ('Phenazepam'=143) ('Phenobarbital'=144) ('Phentermine'=145) ('Phentobarbital'=146) ('Phenytoin'=147) ('Piroxicam'=148) ('Promethazine'=149) ('Propofol'=150) ('Propoxyphene'=151) ('Propranolol'=152) ('Pseudoephedrine'=153) ('Quetiapine'=154) ('Ranitidine'=155) ('Risperidone'=156) ('Rocuronium Bromide'=157) ('Salicylate'=158) ('Scopolamine'=159) ('Sertraline'=160) ('Synthetic Cannabinoid'=161) ('Synthetic Cannabinoids'=162) ('Tapentado'=163) ('Tapentadol'=164) ('Temazepam'=165) ('THC Metabolite'=166) ('Theobromine'=167) ('Thiordazine'=168) ('Topiramate'=169) ('Tramadol'=170) ('Trazodone'=171) ('Triazolam'=172) ('Trihexyphenidyl'=173) ('Unknown'=174) ('Valproic acid'=175) ('Venlafaxine'=176) ('Verapamil'=177) ('Vicodin'=178) ('Warfarin'=179) ('Zaleplon'=180) ('Ziprasidone'=181) ('Zolpidem'=182) ('Zopiclone'=183) INTO DrugA_Recode. VARIABLE LABELS DrugA_Recode 'DrugA_Recode'. EXECUTE. Overdose Deaths Data Syntax for Labeling a Variable VALUE LABELS DrugA_Recode 1'1,1-Difluoroethane' 2'1,3-Dimethylamylamine' 3'25I-NBOMe' 4'3,4-Methylenedioxyamphetamine' 5'10-Monoacetyl Morphine' 6'7-Amino' 7'Acetaminophen' 8'Adderall' 9'Alpha-Hydroxyalprazolam' 10'Alprazolam' 11'Amitriptyline' 12'Amlodipine' 13'Amoxatine' 14'Amphetamine' 15'Anaphylaxis' 16'Aripiprazole' 17'Aspirin' 18'Atomoxetine' 19'Baclofen' 20'Barbiturate' 21'Benzodiazepine' 22'Benzonatate' 23'Benzoylecgonine' 24'Benztropine' 25'Brompheniramine' 26'Bupivacaine' 27'Buprenorphine' 28'Bupropion' 29'Buspirone' 30'Butalbital' 31'Butalnotal' 32'Caffeine' 33'Carbamazepine' 34'Carboxyhemoglobin' 35'Carisoprodol' 36'Chloral Hydrate' 37'Chlorazepine' 38'Chlordiazepoxide' 39'Chlorethan' 40'Chloroquine' 41'Chlorpheniramine' 42'Chlorpromazine' 43'Citalopram' 44'Clomipramine' 45'Clonazepam' 46'Clonidine' 47'Clozapine' 48'Cocaethylene' 49'Cocaine' 50'Codeine' 51'Cotinine' 52'Cyclobenzaprine' 53'Desipramine' 54'Desmethldoxepin' 55'Destromethorphan' 56'Desvenlafaxine' 57'Detromethophan' 58'Dextromethorphan' 59'Diazepam' 60'Difluoroethane' 61'Diltiazem' 62'Diphenhydramine' 63'Donepezil' 64'Doxepin' 65'Doxylamine' 66'Duloxetine' 67'Ecstasy' 68'EDDP' 69'Ephedrine' 70'Estazolam' 71'Ethylene Glycol' 72'Fentanyl' 73'Flecainide' 74'Fluoxetine' 75'Fluphenazine' 76'Fluvoxamine' 77'Gabapentin' 78'GHB' 79'Guetiapine' 80'Haloperidol' 81'Helium' 82'Heroin' 83'Hydrocodone' 84'Hydromorphone' 85'Hydroxychloroquine' 86'Hydroxyzine' 87'Imipramine' 88'Insulin' 89'Isobutyl Nitrite' 90'Isopropanol' 91'Ketamine' 92'Kratom' 93'Lamotrigine' 94'Levetiracetam' 95'Lidocaine' 96'Lidoderm' 97'Lithium' 98'Lorazepam' 99'Loxapine' 100'Meclizine' 101'Meperidine' 102'Meprobamate' 103'Mesoridazine' 104'Metabolite' 105'Metaclopramide' 106'Metaxalone' 107'Metclopramide' 108'Methadone' 109'Methamphetamine' 110'Methocarbamol' 111'Methodone' 112'Methorphan' 113'Methotrimeprazine' 114'Methylone' 115'Metoclopramide' 116'Metoprolol' 117'Midazolam' 118'Mirtazapine' 119'Morphine' 120'Multiple Drug' 121'Naloxone' 122'Nicotine' 123'Nifedipine' 124'Nonvenlafaxine' 125'Norbuprenorphine' 1210'Nordiazepam' 127'Nordiazpam' 128'Norfentanyl' 129'Norketamine' 130'Normeperidine' 131'Norpropoxyphene' 132'Nortriptyline' 133'Norvenlafaxine' 134'Olanzapine' 135'Opiate' 1310'Opiates' 137'Orphenadrine' 138'Oxazepam' 139'Oxycodone' 140'Oxymorphone' 141'Paroxetine' 142'Perphenazine' 143'Phenazepam' 144'Phenobarbital' 145'Phentermine' 146'Phentobarbital' 147'Phenytoin' 148'Piroxicam' 149'Promethazine' 150'Propofol' 151'Propoxyphene' 152'Propranolol' 153'Pseudoephedrine' 154'Quetiapine' 155'Ranitidine' 156'Risperidone' 157'Rocuronium Bromide' 158'Salicylate' 159'Scopolamine' 160'Sertraline' 161'Synthetic Cannabinoid' 162'Synthetic Cannabinoids' 163'Tapentado' 164'Tapentadol' 165'Temazepam' 166'THC Metabolite' 167'Theobromine' 168'Thiordazine' 169'Topiramate' 170'Tramadol' 171'Trazodone' 172'Triazolam' 173'Trihexyphenidyl' 174'Unknown' 175'Valproic acid' 177Venlafaxine' 177'Verapamil' 178'Vicodin' 179'Warfarin' 180'Zaleplon' 181'Ziprasidone' 182'Zolpidem' 183'Zopiclone'. EXECUTE. Overdose Deaths Data Syntax for Data Selection by Drug 10=Alprazolam *Most Frequent Drugs used with Alprazolam #1 USE ALL. COMPUTE filter_$=((DrugA_Recode=10 or DrugB_Recode=10 or DrugC_Recode=10 or DrugD_Recode=10 or DrugE_Recode=10 or DrugF_Recode=10 or DrugG_Recode=10 or DrugH_Recode=10 or DrugI_Recode=10 or DrugJ_Recode=10 or DrugK_Recode=10 or DrugL_Recode=10) and (Total_Drugs >= 2)). VARIABLE LABELS filter_$ 'DrugA_Recode=10 or DrugB_Recode=10 or DrugC_Recode=10 or DrugD_Recode=10 ‘+ 'or DrugE_Recode=10 or DrugF_Recode=10 or DrugG_Recode=10 or DrugH_Recode=10 or DrugI_Recode=10 or 'DrugJ_Recode=10 or DrugK_Recode=10 or DrugL_Recode=10 and Total_Drugs >= 2 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. MULT RESPONSE GROUPS=$Total_Drug_Frequency 'MR Variable for Total Drug Frequency' (druga_recode drugb_recode drugcrecode drugd_recode druge_recode drugf_recode drugg_recode drugh_recode drugi_recode drugj_recode drugk_recode drugl_recode (1,183)) /FREQUENCIES=$Total_Drug_Frequency. Overdose Deaths Data Drug with 3 Most Used Combination Total Total Combo % Combo 1st Combo 2nd Combo 3rd Combo Alprazolam (Benzodiazepine) 1168 1143 98% Oxycodone (466) Methadone (308) Hydrocodone (304) Oxycodone (Semisynthetic Opioid) 962 823 86% Alprazolam (466) Hydrocodone (158) Methadone (111) Hydrocodone (Semisynthetic Opioid) 673 599 89% Alprazolam (304) Oxycodone (158) Methadone (90) Methadone (Synthetic Opioid) 752 532 71% Alprazolam (308) Oxycodone (111) Hydrocodone (90) Morphine (Opioid) 513 401 78% Alprazolam (162) Oxycodone (85) Hydrocodone (76) Cocaine 623 307 49% Alprazolam (91) Oxycodone (75) Morphine (55) Diphenhydramine (Antihistamine) 307 280 91% Alprazolam (102) Oxycodone (84) Hydrocodone (72) Citalopram (Antidepressant) 274 259 95% Alprazolam (108) Oxycodone (83) Hydrocodone (69) Diazepam (Benzodiazepine) 259 259 100% Oxycodone (95) Alprazolam (92) Hydrocodone (71) Fentanyl (Synthetic Opioid) 312 235 75% Alprazolam (87) Oxycodone (65) Hydrocodone (50) Heroin (Opioid) 142 67 47% Cocaine (34) Alprazolam (18) Methamphetam ine (10) Methamphetamine 388 199 51% Alprazolam (69) Oxycodone (46) Amphetamine (41) Drug Treatment Episode Data Set •The data was at the individual level and each person was given a unique identifier •This allowed us to analyze multiple treatment episodes for an individual •The problem was that each individual was added separately (multiple rows or separate cases) instead of each treatment episode recorded in separate a variable •We solved this problem by restructuring the data set from “Cases to Variables” Treatment Episode Data Set Syntax for Cases to Variables SORT CASES BY list variables and include a space between them. These will be the ID variable/s or variable/s used for the match. CASESTOVARS /ID = list variables and include a space between them. These are your matching variable/s. /AUTOFIX=YES/NO /COUNT= Name variable to include a count of the cases. EXECUTE. Subcommand Definitions • ID subcommand specifies variables that identify the rows from the original data that should be grouped together in the new data file • INDEX subcommand names the variables in the original data that should be used to create the new columns. INDEX variables are also used to name the new columns • FIXED subcommand names the variables that should be copied from the original data that do not vary within row groups in the original data. • AUTOFIX subcommand evaluates candidate variables and classifies them as either fixed or as the source of a variable group. Will override the FIXED command. • Label AUTOFIX as YES and it will evaluate all candidate variables and classifies them as variable or fixed and NO will evaluate all candidate variables and compare to the FIXED command and if there are differences it will issue a warning • DROP subcommand specifies the subset of variables to exclude from the new data file. • COUNT subcommand creates a new variable that contains the number of rows in the original data that were used to generate the row in the new data file. *There are more subcommands in the SPSS Help menu if you have more needs than what are listed above Treatment Episode Data Set Treatment Episodes by Drug When the Initial Treatment was for Heroin Heroin Treatment 1 Treatment 2 Treatment 3 Treatment 4 Treatment 5 Treatment 6 100.00% 63.33% 80.00% 0.00% 0.00% 0.00% Marijuana/Hashish - 3.33% 0.00% 0.00% 0.00% 0.00% Alcohol - 10.00% 20.00% 0.00% 0.00% 0.00% Cocaine/Crack - 3.33% 0.00% 0.00% 0.00% 0.00% Methamphetamine - 6.66% 0.00% 0.00% 0.00% 0.00% Other Opiates and Synthetics - 10.00% 0.00% 0.00% 0.00% 0.00% Benzodiazepines - 3.33% 0.00% 0.00% 0.00% 0.00% 30 5 0 0 0 Total of Individuals Treated (n) 186 % Receiving Additional Treatment - 16.13% 16.67% 0.00% 0.00% 0.00% % Of Total Receiving Additional Treatment - 16.13% 2.69% 0.00% 0.00% 0.00% Treatment Episode Data Set Treatment Episodes by Drug When the Initial Treatment was for Opiates or Synthetics Treatment 1 Other Opiates and Synthetics 100.00% Treatment 2 Treatment 3 Treatment 4 Treatment 5 Treatment 6 59.52% 50.00% 50.00% 0.00% 0.00% Marijuana/Hashish - 5.55% 5.55% 0.00% 0.00% 0.00% Alcohol - 11.11% 22.22% 0.00% 0.00% 0.00% Cocaine/Crack - 0.79% 0.00% 0.00% 0.00% 0.00% Methamphetamine - 2.38% 0.00% 0.00% 0.00% 0.00% Benzodiazepines - 7.14% 0.00% 0.00% 0.00% 0.00% Heroin - 3.97% 5.55% 0.00% 0.00% 0.00% 126 18 2 0 0 Total of Individuals Treated (n) 951 % Receiving Additional Treatment - 13.25% 14.29% 11.11% 0.00% 0.00% % Of Total Receiving Additional Treatment - 13.25% 1.89% 0.21% 0.00% 0.00% Administrative Data: Challenges and Techniques Conclusion •Sometimes administrative data sets need a lot of work to pull out important information, but they can be great resources •Data mining and trend analysis can help identify problems, anticipate future needs or help quantify what people say in the field •Using many different data sets can help paint a broad picture and show how one issue can effect different areas Administrative Data: Challenges and Techniques Questions Samuel Gonzales, Operations Analyst [email protected] Let us know if you want any of our syntax files!
© Copyright 2024