PPP loans in Arizona

This markdown gives you instructions for importing and understanding the PPP data for your mini-memo. There are other background documents and example stories in the Canvas module that you can also review.

Download the data

To load the data into your Global Environment add this line to a code chunk:

    data_url <- "https://github.com/cronkitedata/rstudyguide/blob/master/data/az_ppp.Rda?raw=true"

    load (url ( data_url) )
    

That will create a data fram called az_ppp in your RStudio session.

Source

The original data was published by the SBA here https://sba.app.box.com/s/5myd1nxutoq8wxecx2562baruz774si6 on Feb. 1, 2021. I downloaded and unzipped the PPP portion, and read it into R using a different program.

The SBA has never released a record layout or data dictionary for this data that I can find. I worked on a project over the Christmas break using an earlier version of the data, so I think I understand much of what it contains.

I selected about half of the variables that are included in the original data, and simplified a few as shown below. I also converted all character fields to upper case to ease filtering, and converted the Zip Codes to 5-digits .

This dataset includes all loans that were made to companies in Arizona. A handful (6) of these are going toward projects in other states.

Here are the columns I kept, and what I understand them to mean. Any changes other than those above are indicated in the record layout.

The loan application form . There’s one important thing I don’t know the answer to: There are no rows with a loan statust that suggests that it was forgiven, though we know from reports that more than $100 billion have already been forgiven. There is a “loan status” field in the data, but that doesn’t have anything related to forgiveness, and the “PAID IN FULL” indicator doesn’t add up to nearly enough money. I’m trying to find out what the answer is there, but I wanted to warn you not to use that column until we get some clarity on it.

column name	type	description
id	n	A unique identifier supplied by the SBA
date_approved	d	Date the loan was initially approved
status_date	d	When the status was reported (NA if unreported)
borrower	c	Name of the borrower, all upper case
address	c	Address of the borrower
city	c
state	c
zip	c	5-digit Zip code of the borrower
loan_status	c	Blank if redacted because of EXEMPTION 4 - VERY unclear what this means!
term	n	Months to pay back
initial_amt	n	Initial amount approved by SBA
payroll_amt	n	Amount used for payroll
nonpayroll_amt	n	Total amount used for other costs
franchise	c	Name of a franchisee
lender	c	Name of originating lender (not servicing lender)
lender_state	c
rural_urban	c
business_age	c	How long the business had been open before the loan -
project_city	c	City where the money will be used
project_county	c	County where the money will be used
project_state	c	State where the county will be used
jobs_reported	n	Number of jobs “saved” by the loan
naics_code	c	Standard industry code used in the federal government
naics_sector	c	Sector of that standard industry code, where it was a valid one
naics_descript	c	2017 description of the industry, blank if a previous year’s version used
race_ethnicity	c	Race and ethnicity of the business owner, but usually not filled out
gender	c	Same with gender
veteran	c	Same with veteran
non_profit	c	Only filled out if the borrower is a non-profit organization