LEAIC Learning Guide (SPSS): Using the LEAIC

We will use the NIBRS Extract Files for 2013. This file is located on the National Archive of Criminal Justice Data website, meaning that you will need an account to be able to download this data. Accounts are free and you can register for one on the ICPSR website. The NIBRS file is ICPSR study #36121, and we want the DS2 Victim-Level File (data set number 2 – Victim-Level Extract File). On this study’s homepage, there are a number of datasets available to download within the Data & Documentation tab. Since we are going to load this file into SPSS using an SPSS Setup file, click ASCII + SPSS Setup in the DS2: Victim-Level File download area. Read and agree to the Terms of Use. Clicking ASCII + SPSS Setup will download a zip file called ICPSR_36121-V2.zip which has a number of folders and files inside it. Because the file is zipped, you may need software designed to open compressed, or zipped files. Within ICPSR_36121-V2.zip there is a folder called ICPSR_36121. Within that, the key folder is “DS0002” which contains the setup file (36121-0002-Setup.sps) and the data file (36121-0002-Data.txt).

Note: In this walkthrough, we will provide SPSS syntax on a step-by-step basis. The full code is available here

Read the NIBRS File into SPSS

You will need to use the Setup file to read the data file into SPSS. We will then save it as an SPSS file for use throughout this guide. Open SPSS and in the file menu, click File -> Open -> Syntax. Choose the file 36121-0002-Setup.sps and click Open. This opens up an SPSS Setup file, which is an instruction manual to SPSS for how to read the data file. There are two changes you need to make to this file. The first is to set your working directory. The working directory tells SPSS to look in a specified folder for each file that you mention. A good practice is to keep all of your needed files in a single folder and set that folder as the Working Directory. Throughout this guide, we act as if the files are within the Working Directory.

You must copy the above line (after changing the part inside the quotation marks to your own working directory) into the Setup file BEFORE line 50. Make sure that both the data file and the Setup file are inside the working directory folder.

The other change is to edit line 50. The value in the quotation marks in line 50 tells SPSS the name of the file you want it to read. Right now, that file is called “data-filename”. We need to change this to the real name of the file, 36121-0002-Data.txt.

Line 50 currently looks like this:
FILE HANDLE DATA / NAME=”data-filename” LRECL=1003.
Change this to:
FILE HANDLE DATA / NAME=”36121-0002-Data.txt” LRECL=1003.

Once you have made these changes, click Run -> All. Reading the file into SPSS may take several minutes. When the bottom of the SPSS window says “IBM SPSS Statistics Processor is Ready,” it’s done running. SPSS will open the data file. Save it as an SPSS file for use throughout this guide. In File, click Save As and name the file da36121-0002.sav.

Note: For a more detailed guide to using SPSS Setup files to read text data files, see this ICPSR guide

Select NIBRS Variables

The NIBRS files are large. Once converted to SPSS, the file is approximately 5.5 GB. We do not need all the information included in it, so we’ll just keep the variables we need. Referring to the codebook helps identify what variables are needed.

ORI ORIGINATING AGENCY IDENTIFIER
BH007 CITY NAME
BH019 CURRENT POPULATION 1
BH023 CURRENT POPULATION 2
BH027 CURRENT POPULATION 3
BH031 CURRENT POPULATION 4
BH035 CURRENT POPULATION 5
V4007 UCR OFFENSE CODE 1
V4008 UCR OFFENSE CODE 2
V4009 UCR OFFENSE CODE 3
V4010 UCR OFFENSE CODE 4
V4011 UCR OFFENSE CODE 5
V4012 UCR OFFENSE CODE 6
V4013 UCR OFFENSE CODE 7
V4014 UCR OFFENSE CODE 8
V4015 UCR OFFENSE CODE 9
V4016 UCR OFFENSE CODE 10
V4018 AGE OF VICTIM
V4019 SEX OF VICTIM

ORI is necessary to link to the LEAIC.

BH007 – CITY NAME, while technically not necessary, is a useful descriptive variable. It will also be useful to verify that we merged this data with the LEAIC data correctly.

Variables BH019, BH023, BH027, BH031, and BH035 are five population variables. NIBRS recognizes that some cities may span more than one county. The five CURRENT POPULATION variables record the portion of the city population that is in each county. The vast majority of cities have all of their population in one county. However, there are a few hundred that have populations in multiple counties. There is no one total population variable in NIBRS. We’ll create that later.

NIBRS records up to 10 offenses per victim. The vast majority of victims experience one offense. However, to be complete we need to look at all possible offense variables. Variables V4007 to V4016 record the offenses that the victim experienced. As an aside, for single-victim crime incidents, what is recorded in variables V4007 to V4016 will be the same as what is recorded on the NIBRS offense records. For multiple-victim crime incidents, what is recorded in V4007 to V4016 is specific to an individual victim.

V4018 – AGE OF VICTIM and V4019 – SEX OF VICTIM are the demographic variables we are interested in.

You will notice that we did not choose a state variable. That may seem odd given we intend to aggregate to state level. We did not bother with a state variable at this point because we will pick up the state when we merge these data with the LEAIC.

Get Numerator Data

Below is the SPSS code to read in the NIBRS Victim Extract File and select the variables we need. To open a new syntax file (where you will paste and run the following code) click File -> New -> Syntax. Then copy the following code and run it by highlighting the code and clicking Run -> Selection.

After running the above code, we have a file with 5,588,281 records – a record for each victim of crime in the NIBRS data. A window will open with the above table in Data View. The table may appear behind your syntax window, so try minimizing open windows if you don’t immediately see it. This table includes all crimes covered in NIBRS. Homicide is relatively rare, so we can greatly reduce the file by selecting just victims of homicide. The NIBRS data indicates which crime occurred by a code rather than the name of the crime (for a complete list of which codes correspond to which crimes, please see the codebook). The code for ‘homicide’ is ’91’. Remember that each victim may have up to 10 offenses recorded, so we will need to check all 10 of these offenses for homicide. It is convenient to create a flag variable to indicate that the record is one we are interested in.

By creating a frequency table for the OFFENSE variable, we find that there are 3,551 victims of homicide in the NIBRS data. Remember, NIBRS does not include all of the United States, so this is just homicide victims recorded in NIBRS. If you look in the SPSS data editor you will see that each record has a code of 91 in one of the variables V4007 through V4016. You’ll also see that there are 3,551 records.

Now let’s create a total population variable. For our state-level analysis, that is all we need.

At this point we have a lot of variables that we do not need. Let’s sort the data by ORI and save a victim subset file keeping fewer variables.

We now have a much smaller file than we started with. It’s much easier to work with and contains only what we are interested in. We sorted by ORI because that is what we will use to merge with the LEAIC. As you look at the individual records using the data editor notice that TOTPOP is repeated on each record having the same ORI code.

Get LEAIC Data

Now let’s get the data that we need from the LEAIC file. The LEAIC file is available to download in SPSS format from the study’s homepage, ICPSR study #35158. Under the Data & Documentation tab, click the button under Download and select SPSS (not “ASCII + SPSS Setup”). Agree to the Terms of Use and the file will begin to download. This file is a .sav file already so we do not need to use a Setup file to read it. Clicking SPSS will download a zipped folder called ICPSR_35158-V2.zip which contains a folder called ICPSR_35158. Within this folder is yet another folder called DS0001 which contains the file we want, 35158-0001-Data.sav (remember to move this file to your working directory).

We need the LEAIC file because the geography codes in the FBI’s UCR/NIBRS data are not the same as the codes used by the Census Bureau. The LEAIC has both sets of geography codes for each law enforcement agency. As with the NIBRS data, we don’t need all of the LEAIC variables or cases, so we’ll keep just what we need.

FSTATE FIPS STATE CODE
ORI9 ORIGINATING AGENCY IDENTIFIER (9 CHARACTERS)
NAME AGENCY NAME

FSTATE contains information on which state each agency is in. We will use this first to only select states where every agency reports to NIBRS and aggregate the crimes by state. Then we will use it to merge with the Census file. ORI9 is a nine-character long alphanumeric identifier that the FBI uses to identify each agency. Because NIBRS has an identical column, we will use it to merge the two data sets. NAME is the name of the agency and can be used to check that the crosswalk merged with the NIBRS file correctly. If the name of the agency is the same in NAME and BH007 (NIBRS column for the city name), we can be confident the merge was successful.

Merge NIBRS with LEAIC

Now it is time to merge our two subset files together. We merge the files using the ORI variable. In the NIBRS data the variable is simply named ORI. In the LEAIC file there are two ORI variables: a 7-character version and a 9-character version. We have to rename ORI in the NIBRS data to be compatible with the LEAIC. In the match files statement, the NIBRS data is referred to as “file” and the LEAIC data is referred to as “table”. What this means is that all the records from the file will be used, but only the data in the table that matches to the file will be included. Thus, our merged file will have all the records and variables from the victim subset file, and when there is a match on ORI9 with the LEAIC, that data will be included. We run a cross tabulation to get a summary of homicides by state.

When we look at the cross tabulation output, we notice a few things: Data are not perfect. Remember that not all states participate in NIBRS, and of the ones that do, not all law enforcement agencies are included. However, some states, such as Michigan, are 100% NIBRS. There are also some missing data; the sex of the victim is unknown for 14 victims.

Looking at the individual records in the data editor we see what appears to be duplicate information. The names of the law enforcement agency and the populations are listed twice. For all the previous talk about keeping only what is necessary, why include this information twice? The reason is to help verify that the merge was done correctly. If we saw that variable BH007 (from NIBRS) contains DETROIT, but variable NAME (from LEAIC) contains ANN ARBOR, then something is wrong. This is simply a check to make sure that the merge worked correctly. If the names are the same, we can be confident our merge was successful. Visually inspecting the data shows us that the merge worked as expected. The population variable TOTPOP (from NIBRS) differs from the variable U_TPOP (from LEAIC) in most cases. This is because our NIBRS data is from 2013 while the LEAIC is from 2012. We will use population data from the Census, so don’t worry about this.

Calculate Numerators

The file that we just saved has all we need to calculate the numerator portion of our homicide rates. We need to create state totals from the individual law enforcement records that we currently have. In SPSS we make use of the “aggregate” command. However, the first thing we need to do is make age category variables.

The NIBRS victim data includes the sex and age (in years) of the victims. We could calculate a victimization rate for every age, but people usually find rates for specific important ages and age categories more useful. We also need to be aware that our denominator data may not be in the same form as the numerator data. For instance, the ACS data we will get from the Census’ data table tool are not in the same form as the NIBRS data. Most age groups are categorized into 5-year categories (25-29, 30-34, etc.), but finer categories are used for ages 15-24, as those are the prime ages of criminal offending. The youngest and the oldest categories are grouped as ‘under 5 years’ and ’85 years and older’, respectively. We’ll make our numerator data – sex and victim age categories – match the denominator data from the ACS.

The ACS data has 18 age categories for both females and males. The code below creates and labels 36 variables.

That is a lot of code, but remember we are interested in taking an overall rate – the U.S. homicide victimization rate – and looking at it in finer detail. That requires extra effort. Generating a frequency table for one variable – FSTATE – is sufficient to see that our code worked without errors. Working without error messages is not the same as working correctly. The frequency on FSTATE (FIPS STATE) already starts to tell us a more interesting story about homicide victims. We see a lot of variation among the states. That is to be expected given that states vary greatly in population, and we adjust for that later. We also see some numbers that, at first, make no sense. California is not listed at all, and Illinois shows 19 homicides. Remember that NIBRS does not currently include all states; California does not participate. Also, while the State of Illinois participates, the City of Chicago does not. We will select for states that are 100% NIBRS reporting soon.

Now it’s time to aggregate our sex-age category variables to get total counts. The aggregate command creates new variables that correspond to the total homicide victims by sex-age category. We also create variables for the total female, male and overall victim counts, and make labels for these new variables. We then select only the states that are 100% NIBRS reporting, which can be found on the FBI website. Finally, we recode system missing values to zero and save a file of victim counts. That file has all we need for our numerators.

Note that sometimes after running code (for example, the above code), the data set will not visually change but will say “transformations pending” at the bottom of the window. This means that the code is working.

We’ve accomplished a lot. The file victim_counts.sav includes the homicide victim counts by sex-age category for each state that reports 100% NIBRS. We have our numerators. We are more than halfway to calculating disaggregated homicide victimization rates. The ACS data are already sex-age categorized, so we won’t have to repeat that work.

Calculate Denominators

We now have all the numerators needed for understanding homicide rates for different age and gender categories in NIBRS states. Now we must get the denominators. Since we want to look at crime rates in different population groups, we will use the Census’ ACS data to get the total number of people in each group. This will be our denominator.

There are a few ways to get Census data (e.g. using IPUMS which is an organization at the University of Minnesota that provides data from government surveys, including the Census), but we will find data using the Census’ data table tool. While the IPUMS gives raw data (or as close to raw as privacy concerns permit), the Census data table tool does some pre-processing for us. In our case that means that the data we retrieve will already be aggregated into the groups we want.

To get the necessary data from the Census data table tool, follow these steps:

1. Go to https://data.census.gov/
2. In the search bar, search for “American Community Survey”
3. Choose the first table that appears in the search results: “Age and Sex”
4. After you click on the “Age and Sex” table, click the “Filter” button in the top left
   
   • In the filter view, you will need to select the relevant data years and geography. Make sure you enter all of the following into the search bar: american community survey, 2013, Arkansas, Colorado, Delaware, Idaho, Iowa, Michigan, Montana, New Hampshire, North Dakota, South Carolina, South Dakota, Tennessee, Vermont, Virginia, West Virginia. This selection allows us to get only the 2013 data that we are interested in (the same year as our NIBRS data), as well as the 15 states that had 100% of agencies reporting to NIBRS in 2013.
   • The 100% NIBRS states in 2013 are Arkansas, Colorado, Delaware, Idaho, Iowa, Michigan, Montana, New Hampshire, North Dakota, South Carolina, South Dakota, Tennessee, Vermont, Virginia, and West Virginia, the states we selected in our filter.
5. Click Search.
6. Finally, we need to select the data set to get the above variables from. When you are in your table view, you will find a “Product” dropdown in the top right corner. In the dropdown menu, select “2013: ACS 5-Year Estimates Subject Tables”.
   
   • 1-year estimates give data from the survey given that year. 3- and 5-year estimates use data from the selected year and the year before and after (or two years before and after for 5-year estimates). These two data sets have far more information than the 1-year estimate (due to covering more people in their combined surveys) but are less precise for the given year.
   • As mentioned above, there are trade-offs with any data set. 1-year estimates only have data from the year in question, so are better able to answer questions about what is going on in that year. However, they do have fewer respondents than 3- or 5-year estimates. None of the data sets are objectively better than another (and this is true with nearly all data sets). You must first understand what information the data have available and determine if it can answer the questions you want to ask.
7. We are done specifying what data we want. By default, our table has states as columns and age categories as rows. For easier analysis, let’s swap them. Click Customize Table and then Transpose:
   
8. To download the data, click Download, select CSV format, and then uncheck the checkboxes that emerge. Click the Download button to download a zipped file containing the data.
   • Downloading the data will open a folder with multiple files. The .csv file that you want includes “ACSST5Y2013.S0101_data_with_overlays” in the title. Move it to your working directory folder.

For simplicity we will read this Excel file into SPSS and save it as a SPSS file (file extension .sav). Open SPSS and in the File menu, go to Import Data and click CSV Data. From here you can navigate to the .csv file in your working directory and click Open. We need to do a bit of data prep with the ACS file. A Read CSV File window will open. Click Advanced Options(Text Wizard).

A Text Import Wizard – Step 1 of 6 window will open. Click Next for Steps 1 and 2 without making any changes. At Step 3, change the first dropdown box to 3. The CSV file contains label information in the first 2 rows, so the data first appear in row 3. Click Next.

Click Next for Step 4 without making any changes. Several changes are needed in Step 5. First, under Data preview, highlight the NAME column. In the Variable name box, change NAME to GEO.displaylabel.

Go back under Data preview, highlight the third column, scroll all the way to the right, press SHIFT and highlight the last column to select all the variables in between. Under Data format, use the dropdown bar to select Numeric. These variables will be imported as strings otherwise. Click Next. (It’s okay that some variables have (X) values; they won’t be used.)

Click Finish at Step 6. The data should now be in an SPSS Data Editor window. Now save it as an SPSS file by clicking File -> Save As. The default file type is an SPSS file so simply name it ACS_13_5YR_B01001.sav and click Save. Move this file to your working directory.

Calculate Rates

Finally, we can combine our numerator and denominator files into one file and calculate disaggregated homicide rates. In the SPSS match files statement the victim counts are in the “file” statement. All records from that file will be included in the merged (matched) file. The ACS populations are referred to as a “table”. The ACS table includes population data for all states. Referring to it as a table means that only the records that match the “file” will be included in the resulting combined data file.

After the merge is done we calculate the homicide victimization rates by sex and age-category. The rate calculation is the number of homicide victims by sex and age category multiplied by 100,000 and divided by the number of persons in that sex and age category.

Our last data management task is to save our data. We save two files. One file has both rates and counts, which is useful for verification. The second has just the rates.

1. Open these files to see your results. Click File -> Open -> Data, select the file you want, and click Open

You have now created files with the calculated homicide rates by sex and age category for the states participating 100% in NIBRS for the year 2013. Along the way we have used NIBRS, ACS, and LEAIC data; we’ve created subsets, merged files, computed new variables and aggregated variables; we tested the results of our work at key points; we calculated the disaggregated rates and saved our final files. We are now able to see how different the individual rates are from the overall U.S. rate that we started with.

Examine the Results

If we use the data editor to examine individual values in the file just_rates.sav, we immediately see results that tell us much more than the overall U.S. homicide victimization rate. Total victimization rates by state vary from 1.4 (Iowa) to 6.4 (South Carolina). In each state, the homicide victimization rate for males is higher than for females, except in Vermont, where they are equal. These results are easy to see just by looking at the individual records.

Find the Highest Rates per State

The file just_rates.sav is not large. We could simply read across each row to find the age category with the largest rate in each state.

Are the results surprising? For the most part we see relatively young males as victims. But what is going on with Idaho, New Hampshire, and Vermont? In Vermont, Females 70 to 74 years old have the highest homicide victimization rate in the state. Can this be correct? The answer is yes, but it is worth looking into why it is correct.

Vermont is a relatively low population state. Consequently, a single homicide can substantially affect their homicide rate. In addition, since we are looking at disaggregated rates, the denominator we use is small, which magnifies the effect. Looking at the file we just created – rates_and_counts.sav – we see that the population of females aged 70 to 74 in Vermont is only 11,425 (POPF70_74), and there are two female victims of homicide in this age group. The rate of 17.5 (RTF70_74) is simply ( 2 * 100,000 ) /11,425. The rate is correct, but we should be careful given how sensitive it is to change. One less homicide cuts the rate in half, and one more increases it by 50%.

Programmatically Find the Highest Rates per State

Visually inspecting a file this size is not too burdensome. However, when all states are 100% NIBRS, visual inspection could be tedious and prone to error. Below is SPSS code to produce a table identifying the sex-age categories with the highest rates.

Wrap Up

Congratulations! You have learned how to use the LEAIC file to link diverse data sources in order to calculate homicide victimization rates. We’ve covered a lot of topics, and hopefully we’ve shown that using the LEAIC is not too difficult and opens up a world of research possibilities. NIBRS is the format of future FBI crime data, but it only gives the numerator for crime rate calculations; Census data is a great source for denominators. The LEAIC file brings them together.