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!