CREATING AN INCIDENTLEVEL AGGREGATED FLAT FILE IN SPSS
View snapshot of the SPSS active file after
the variables have been created
View the newly created aggregate file
The following code converts the large flat file to a smaller incidentlevel file. Counts are created for variables and are aggregated to produce summarylevel data for the incidents in the original file. When using this code, be sure to insert the path and the file name of the data file to be used, as well as the directory and file name for saving. If you need any assistance working with the syntax provided, please contact us.
The assumptions for the following example of code are:

The RECODE and CONVERT first change the alphanumeric victim ages of victims under 1 year of age to a numeric value and then create a new victim age variable converting all alphanumeric representations of victim age to their numeric counterparts. 
GET FILE = 'Directory:\Path\FBI data.sav'.
RECODE vic_age ('BB' = .7),('NN' = .1),('NB' = .2)(convert) into v_age.
The following DO IF is a conditional transformation that limits the COMPUTE and IF statements to the victims with an age greater than 0 and less than 95. (In the NIBRS coding instructions, all victims over the age of 98 are to be coded as 99 years old. A review of the 1996 data files shows spikes at ages 96, 98, and 99, which indicates potential problems with the accuracy of the data.) The COMPUTE and IF statements first create victim age and gender variables and then assign a value of 1 to each valid occurrence so that a count can be made of these victim variables for each incident when the AGGREGATE command is executed. 
DO IF (v_age gt 0 and v_age le 95).
COMPUTE vage65 = 0.
COMPUTE vage1017 = 0.
COMPUTE vage1524 = 0.
COMPUTE vlt18 = 0.
COMPUTE vadult = 0.
COMPUTE vmale = 0.
COMPUTE vfemale = 0.
IF (v_age ge 65)vage65 = 1.
IF (v_age ge 10 and v_age le 17)vage1017 = 1.
IF (v_age ge 15 and v_age le 24)vage1524 = 1.
IF (v_age ge 18 and v_age le 95)vadult = 1.
IF (v_age lt 18)vlt18 = 1.
IF (vic_sex = 'M')vmale = 1.
IF (vic_sex = 'F')vfemale = 1.
END IF.
The following DO IF is a conditional transformation that limits the COMPUTE and IF statements to the offenders with an age greater than 5 and less than 99. In the NIBRS coding instructions, all offenders over the age of 98 are to be coded as 99 years old. The COMPUTE and IF statements first create offender age and gender variables and then assign a value of 1 to each valid occurrence so that a count can be made of these offender variables for each incident when the AGGREGATE command is executed. 
DO IF (off_age ge 5 and off_age lt 99).
COMPUTE off1017 = 0.
COMPUTE off1524 = 0.
COMPUTE offadult = 0.
COMPUTE off65 = 0.
COMPUTE offmale = 0.
COMPUTE offemale = 0.
IF (off_age ge 10 and off_age le 17)off1017 = 1.
IF (off_age ge 15 and off_age le 24)off1524 = 1.
IF (off_age ge 18 and off_age le 98)offadult = 1.
IF (off_age ge 65 and off_age le 98)off65 = 1.
IF (off_sex = 'M')offmale = 1.
IF (off_sex = 'F')offemale = 1.
END IF.
The following DO IF is a conditional transformation that limits the COMPUTE and IF statements to the arrestees with an age greater than 5 and less than 99. In the NIBRS coding instructions, all arrestees over the age of 98 are to be coded as 99 years old. The COMPUTE and IF statements first create arrestee age and gender variables and then assign a value of 1 to each valid occurrence so that a count can be made of these arrestee variables for each incident when the AGGREGATE command is executed. 
DO IF (arr_age ge 5 and arr_age lt 99).
COMPUTE arr65 = 0.
COMPUTE arr1017 = 0.
COMPUTE arr1524 = 0.
COMPUTE arradult = 0.
COMPUTE arr0509 = 0.
COMPUTE arrmale = 0.
COMPUTE arfemale = 0.
IF (arr_age ge 65 and arr_age le 98)arr65 = 1.
IF (arr_age ge 10 and arr_age le 17)arr1017 = 1.
IF (arr_age ge 15 and arr_age lt 25)arr1524 = 1.
IF (arr_age ge 18 and arr_age le 98)arradult = 1.
IF (arr_age ge 5 and arr_age le 9)arr0509 = 1.
IF (arr_sex = 'M')arrmale = 1.
IF (arr_sex = 'F')arfemale = 1.
END IF.
The following COMPUTE / IF transformation creates a new variable for the victim type "individual." The values for victim type in NIBRS are individual, business, financial institution, government, religious organization, society/public, and other. The variable INDIVL will equal 1 only if the victim type is individual. This will allow a count of the number of person victims in the incidents when the AGGREGATE command is executed. 
COMPUTE indivl = 0.
IF (v_type = 'I')indivl = 1.
This RECODE changes the alphanumeric variable type for arresting offense into a numeric type and assigns the new values to the new variable called ARROFF. Alphanumeric types require more disk space. The new values assigned coincide with the FBI hierarchical offense structure. In this RECODE, the lower the value the greater the seriousness of the offense. 
RECODE arr_off
('200' = 200)('13A' = 130)('13B' = 171)('13C' = 172)
('510' = 510)('220' = 140)('250' = 250)('290' = 290)
('35A' = 350)('35B' = 351)('270' = 270)('210' = 210)
('26A' = 260)('26B' = 261)('26C' = 262)('26D' = 263)
('26E' = 264)('39A' = 390)('39B' = 391)('39C' = 392)
('39D' = 393)('09A' = 90)('09B' = 198)('09C' = 199)
('100' = 190)('23A' = 160)('23B' = 161)('23C' = 162)
('23D' = 163)('23E' = 164)('23F' = 165)('23G' = 166)
('23H' = 167)('240' = 150)('370' = 370)('40A' = 400)
('40B' = 401)('120' = 120)('11A' = 110)('11B' = 180)
('11C' = 181)('11D' = 182)('36A' = 360)('36B' = 361)
('280' = 280)('520' = 520)into arroff.
Like the previous RECODE, this RECODE changes the alphanumeric variable type for incident offense into a numeric type and assigns the new values to the new variable called OFFENSE. Alphanumeric types require more disk space. The new values assigned coincide with the FBI hierarchical offense structure for Part I offenses. In this RECODE, the lower the value, the greater the seriousness of the offense. 
RECODE off_code ('200' = 200)('13A' = 130)('13B' = 171)('13C' = 172)
('510' = 510)('220' = 140)('250' = 250)('290' = 290)
('35A' = 350)('35B' = 351)('270' = 270)('210' = 210)
('26A' = 260)('26B' = 261)('26C' = 262)('26D' = 263)
('26E' = 264)('39A' = 390)('39B' = 391)('39C' = 392)
('39D' = 393)('09A' = 90)('09B' = 198)('09C' = 199)
('100' = 190)('23A' = 160)('23B' = 161)('23C' = 162)
('23D' = 163)('23E' = 164)('23F' = 165)('23G' = 166)
('23H' = 167)('240' = 150)('370' = 370)('40A' = 400)
('40B' = 401)('120' = 120)('11A' = 110)('11B' = 180)
('11C' = 181)('11D' = 182)('36A' = 360)('36B' = 361)
('280' = 280)('520' = 520)into offense.
The DO REPEAT command ensures that the RECODE is performed on all 10 of the victim offense variables. The RECODE changes the alphanumeric variable type for victim offense into a numeric type and assigns the new values to the new variables called VOFF1 through VOFF10. Alphanumeric variable types require more disk space. The new values assigned coincide with the FBI hierarchical offense structure for Part I offenses. In this RECODE, the lower the value, the greater the seriousness of the offense. 
DO REPEAT v_off = v_off1 to v_off10/
voff = voff1 to voff10.
RECODE v_off ('200' = 200)('13A' = 130)('13B' = 171)('13C' = 172)
('510' = 510)('220' = 140)('250' = 250)('290' = 290)
('35A' = 350)('35B' = 351)('270' = 270)('210' = 210)
('26A' = 260)('26B' = 261)('26C' = 262)('26D' = 263)
('26E' = 264)('39A' = 390)('39B' = 391)('39C' = 392)
('39D' = 393)('09A' = 90)('09B' = 198)('09C' = 199)
('100' = 190)('23A' = 160)('23B' = 161)('23C' = 162)
('23D' = 163)('23E' = 164)('23F' = 165)('23G' = 166)
('23H' = 167)('240' = 150)('370' = 370)('40A' = 400)
('40B' = 401)('120' = 120)('11A' = 110)('11B' = 180)
('11C' = 181)('11D' = 182)('36A' = 360)('36B' = 361)
('280' = 280)('520' = 520)into voff.
END REPEAT.
This COMPUTE identifies the most serious offense for each victim and will be used in the AGGREGATE command to identify the most serious incident offense. Offense labels are assigned to each offense code for the variables most serious offense (MSVOFF), victim offense (VOFF), arresting offense (ARROFF), and offense (OFFENSE). The IF reclassifies the rapes of males and assigns a lower seriousness value. (According to the UCR guidelines, a male cannot be a victim of rape.) 
COMPUTE msvoff=min.1(voff1 to voff10).
IF (msvoff = 110 and vic_sex = 'M')msvoff = 183.
VALUE LABELS msvoff voff1 to voff10 arroff offense
200 'Arson'
130 'Aggravated Assault'
171 'Simple Assault'
172 'Intimidation'
510 'Bribery'
140 'Burglary/Breaking and Entering'
250 'Counterfeiting/Forgery'
290 'Destruction/Damage/Vandalism of Property'
350 'Drug/Narcotics Violations'
351 'Drug Equipment Violations'
270 'Embezzlement'
210 'Extortion/Blackmail'
260 'False Pretenses/Swindle/Confidence Game'
261 'Credit Card/Automated Teller Machine Fraud'
262 'Impersonation'
263 'Welfare Fraud'
264 'Wire Fraud'
390 'Betting/Wagering'
391 'Operating/Promoting/Assisting Gambling'
392 'Gambling Equipment Violations'
393 'Sports Tampering'
90 'Murder and Nonnegligent Manslaughter'
198 'Negligent Manslaughter'
199 'Justifiable Homicide'
190 'Kidnaping/Abduction'
160 'Pocketpicking'
161 'Pursesnatching'
162 'Shoplifting'
163 'Theft From Building'
164 'Theft From CoinOperated Machine or Device'
165 'Theft From Motor Vehicle'
166 'Theft of Motor Vehicle Parts or Accessories'
167 'All Other Larceny'
150 'Motor Vehicle Theft'
370 'Pornography/Obscene Material'
400 'Prostitution'
401 'Assisting or Promoting Prostitution'
120 'Robbery'
110 'Forcible Rape'
180 'Forcible Sodomy'
181 'Sexual Assault With An Object'
182 'Forcible Fondling'
183 'Rape of a Male'
360 'Incest'
361 'Statutory Rape'
280 'Stolen Property Offenses'
520 'Weapon Law Violations'.
These COMPUTE IF transformations create a new variable for Part I Index offenses plus an "other assault" variable (OTH_ASL) so that these offenses can be counted for each incident when the AGGREGATE command is executed. 
COMPUTE mur = 0.
COMPUTE rap = 0.
COMPUTE rob = 0.
COMPUTE aggasl = 0.
COMPUTE bur = 0.
COMPUTE lar = 0.
COMPUTE mvt = 0.
COMPUTE oth_asl = 0.
IF (offense = 90)mur = 1.
IF (offense = 110)rap = 1.
IF (offense = 120)rob = 1.
IF (offense = 130)aggasl = 1.
IF (offense = 140)bur = 1.
IF (offense >= 160 and offense le 167)lar = 1.
IF (offense = 150)mvt = 1.
IF (offense = 171 or offense = 172)oth_asl = 1.
These COMPUTE IF transformations create a new variable for Part I Index offenses and "other assault" (OASL_ARR) offenses for which there was an arrest. These "arresting" offenses will be counted for each incident when the AGGREGATE command is executed. 
COMPUTE mur_arr = 0.
COMPUTE rap_arr = 0.
COMPUTE rob_arr = 0.
COMPUTE asl_arr = 0.
COMPUTE bur_arr = 0.
COMPUTE lar_arr = 0.
COMPUTE mvt_arr = 0.
COMPUTE oasl_arr = 0.
IF (arroff = 90)mur_arr = 1.
IF (arroff = 110)rap_arr = 1.
IF (arroff = 120)rob_arr = 1.
IF (arroff = 130)asl_arr = 1.
IF (arroff = 140)bur_arr = 1.
IF (arroff >= 160 and arroff le 167)lar_arr = 1.
IF (arroff = 150)mvt_arr = 1.
IF (arroff = 171 or arroff = 172)oasl_arr = 1.
The following blocks of code create new variables for firearm use (FIREARM), victim injured (VIC_INJ), and clearance [by arrest (CLR_ARR) or exceptional clearance (CLR_EXCP)]. Each occurrence is assigned a value of 1 so that a count can be obtained when the AGGREGATE command is executed. 
COMPUTE firearm = 0.
COMPUTE vic_inj = 0.
RECODE weapon1 weapon2 weapon3 (convert) into wpn1 to wpn3.
IF (wpn1 le 15 or wpn2 le 15 or wpn3 le 15)firearm = 1.
IF (v_inj1 = 'B' or v_inj1 = 'I' or v_inj1 = 'L' or
v_inj1 = 'M' or v_inj1 = 'T' or v_inj1 = 'U' or v_inj1 = 'O' or
v_inj2 = 'B' or v_inj2 = 'I' or v_inj2 = 'L' or
v_inj2 = 'M' or v_inj2 = 'T' or v_inj2 = 'U' or v_inj2 = 'O' or
v_inj3 = 'B' or v_inj3 = 'I' or v_inj3 = 'L' or
v_inj3 = 'M' or v_inj3 = 'T' or v_inj3 = 'U' or v_inj3 = 'O' or
v_inj4 = 'B' or v_inj4 = 'I' or v_inj4 = 'L' or
v_inj4 = 'M' or v_inj4 = 'T' or v_inj4 = 'U' or v_inj4 = 'O' or
v_inj5 = 'B' or v_inj5 = 'I' or v_inj5 = 'L' or
v_inj5 = 'M' or v_inj5 = 'T' or v_inj5 = 'U' or v_inj5 = 'O')vic_inj = 1.
IF (tot_aseg ge 1)clr_arr = 1.
IF (excp_clr = 'A'
or excp_clr = 'B'
or excp_clr = 'C'
or excp_clr = 'D'
or excp_clr = 'E')CLR_EXCP = 1.
The following COMPUTE/IF commands create a dummy (0,1) variable which will be used in the aggregate command for counting the number of offenders (OFF_CNT), victims (VIC_CNT), arrests (ARR_CNT), and offenses (OFNS_CNT). 
COMPUTE off_cnt = 0.
COMPUTE vic_cnt = 0.
COMPUTE arr_cnt = 0.
COMPUTE ofns_cnt = 0.
IF (ibr_rec = '02')ofns_cnt = 1.
IF (ibr_rec = '04')vic_cnt = 1.
IF (ibr_rec = '05')off_cnt = 1.
IF (ibr_rec = '06')arr_cnt = 1.
The RECODE of offense location (OFF_LOC) assigns a missing value for records with no value for that variable and converts the alphanumeric variable type to a numeric variable type called INC_LOC. Records with no value for the attempted/completed variable (ATT_COMP) are assigned a missing value. When occurrences are counted, missing values will be excluded. 
RECODE off_loc (' ' = sysmis)(convert) into inc_loc.
MISSING VALUES att_comp (' ').
The following code sorts by ORI and INC_NUM so that the AGGREGATE command can be executed. 
SORT cases by ori inc_num.
The AGGREGATE command restructures the data into an incident unit of analysis. The OUTFILE subcommand creates a new system file so that the original file remains unchanged. Each unique incident record (ORI+ incident number) [see /BREAK=ori inc_num], will consist of variables that describe the incident and variables that allow counts. Some of these variables existed in the original FBI ASCII file and others were created by the user. 
AGGREGATE
/OUTFILE= 'Directory:\Path\Incidentlevel file.sav'
/PRESORTED
/BREAK=ori inc_num
/inc_yr = FIRST(inc_yr)
/inc_mo = FIRST(inc_mo)
/inc_dy = FIRST(inc_dy)
/inc_hr = FIRST(inc_hr)
/inc_loc 'Incident location' = first(inc_loc)
/CLR_EXCP 'Incident was cleared exceptionally' = first(CLR_EXCP)
/CLR_ARR 'Incident was cleared by an arrest' = first(CLR_ARR)
/ofns_cnt 'Count of offenses in incident' = n(tot_oseg)
/off_cnt 'Count of offenders in incident' = n(tot_dseg)
/vic_cnt 'Count of victims in incident' = n(tot_vseg)
/arr_cnt 'Count of arrests for incident' = n(tot_aseg)
/att_comp 'Completed or attempted' = first(att_comp)
/msioff 'Most serious incident offense' = min(msvoff)
/indivl 'Count of individual victims in incident' sum(indivl)
/vage65 'Count of victims age 65 and older in incident' = sum(vage65)
/vage1017 'Count of victims between the ages of 10 through 17' =
sum(vage1017)
/vage1524 'Count of victims between the ages of 15 through 24' =
sum(vage1524)
/vlt18 'Count of victims under age 18' = sum(vlt18)
/vadult 'Count of victims age 18 and older' = sum(vadult)
/vmale 'Count of male victims in incident' = sum(vmale)
/vfemale 'Count of female victims in incident' = sum(vfemale)
/off65 'Count of offenders age 65 and older' = sum(off65)
/off1017 'Count of offenders between the ages of 10 through 17' =
sum(off1017)
/off1524 'Count of offenders between the ages of 15 through 24' =
sum(off1524)
/offadult 'Count of offenders age 18 and older' = sum(offadult)
/offmale 'Count of male offenders in incident' = sum(offmale)
/offemale 'Count of female offenders in incident' = sum(offemale)
/arr65 'Count of arrestees age 65 and older' = sum(arr65)
/arr1017 'Count of arrestees between the ages of 10 through 17' =
sum(arr1017)
/arr1524 'Count of arrestees between the ages of 15 through 24' =
sum(arr1524)
/arr0509 'Count of arrestees between the ages of 5 through 9' = sum(arr0509)
/arradult 'Count of arrestees age 18 and older' = sum(arradult)
/arrmale 'Count of male arrestees in incident' = sum(arrmale)
/arfemale 'Count of female arrestees in incident' = sum(arfemale)
/mur 'Murder offense in incident' = sum(mur)
/rap 'Rape offense in incident' = sum(rap)
/rob 'Robbery offense in incident' = sum(rob)
/aggasl 'Aggravated assault offense in incident' = sum(aggasl)
/bur 'Burglary offense in incident' = sum(bur)
/lar 'Larceny offense in incident' = sum(lar)
/mvt 'Motor vehicle theft offense in incident' = sum(mvt)
/oth_asl 'Other assault offense in incident' = sum(oth_asl)
/mur_arr 'Count of arrests for murder in incident' = sum(mur_arr)
/rap_arr 'Count of arrests for rape in incident' = sum(rap_arr)
/rob_arr 'Count of arrests for robbery in incident' = sum(rob_arr)
/asl_arr 'Count of arrests for aggravated assault in incident' = sum(asl_arr)
/bur_arr 'Count of arrests for burglary in incident' = sum(bur_arr)
/lar_arr 'Count of arrests for larceny in incident' = sum(lar_arr)
/mvt_arr 'Count of arrests for motor vehicle theft in incident' =
sum(mvt_arr)
/Firearm 'Firearm used in incident' = sum(firearm)
/vic_inj 'Number of victims injured in incident' = sum(vic_inj)
/oasl_arr 'Count of arrests for other assaults in incident' = sum(oasl_arr).