Data Analysis Tool For All

U.S. Census Bureau’s American Community Survey (ACS) Public Use Microdata Sample (PUMS) via the Microdata Access Tool Microdata - Census Bureau Datasets (https://data.census.gov/app/mdat/). 

• Benefits: With this data source and the MDAT tool, users have access to more topic details and can create custom tables, including access to several characteristic fields at custom poverty levels (i.e., demographic details of the less than 200% Poverty Level population), which is not available via standard American Community Survey data. 
• Limitations: Limited geographic levels available: States, Public Use Microdata Areas (PUMAs), and national regions and divisions.
• To learn more about Public Use Microdata Sample (PUMS): American Community Survey Microdata (https://www.census.gov/programs-surveys/acs/microdata.html). 
• To learn more about the Microdata Access Tool: How-to Materials for Using the Microdata Access (https://www.census.gov/data/what-is-data-census-gov/guidance-for-data-users/how-to-materials-for-using-the-microdata-access.html)

Intake data from your case management system:

• Fields:
  • Demographics about which you are curious, such as Age at Intake, Gender, Race, Ethnicity, With Disabilities, Veteran, Percentage of Poverty, Number in Household, Citizenship Status, Language, Living Arrangement, County, etc.
  • Dates: Date of Earliest, Intake Date, Prescreen Date, Open Date, Close Date, Date of Rejection, and Date of Birth (for use in an age-related formula).
  • Case Information: Legal Problem Code, Problem Code Categories, Close Reason, Rejection Reason, Disposition, Intake Type, etc.
• Filters/Report Structure:
  • Date Filters: Date of Earliest in last year, or Intake Date or Prescreen Date in last year.
  • Exclude/Filter Out: Test or fake cases and clients and duplicate cases using whichever fields your organizations uses to identify these cases and clients, such as Rejection Reason, Client Name, Case Status, Funding Code, etc.
  • Include: Cases that were closed with service (either in the year they were opened or later) and cases rejected without service.
  • One Row/Record Per Case: Ensure that downloaded data includes just one record (one row) per case. If necessary, text join fields that cause more than one row/record per case.
• Export Format: 
  • If downloading from LegalServer, it is recommended that report results are exported as CSV files (an option that may be set up under Additional Display Format – remember to check Headers in First Row) and saved as Excel files to avoid formatting issues, particularly related to date fields

Collecting External Data Via the Microdata Access Tool (MDAT):

1. Open the MDAT (Microdata - Census Bureau Datasets (https://data.census.gov/app/mdat/).
2. Select a Dataset, Select a Vintage, and click Next. (ACS 1-Year Estimates Public Use Microdata Sample for 2023).
   1. For information about choosing 5-year or 1-year estimates, click here: Using 1-Year or 5-Year American Community Survey Data (http://census.gov/programs-surveys/acs/guidance/estimates.html)
      
3. On the Variables screen, click on Select Geographies and either pick a State or multiple PUMA regions depending on the boundaries of your service area (in this example, a state is selected, but for confidentiality reasons, the state will be referred to as State in the following steps). X out of the screen when you have made your selection(s).
   
4. To add variables for analysis, enter search term(s) in the Label box or review the Data Dictionaries for PUMS Files from the PUMS Documentation webpage PUMS Documentation (https://www.census.gov/programs-surveys/acs/microdata/documentation.html).
   1. Race: Select the RAC1P field (or if you need more detailed race data, you may select from among the other race-related fields that appear when you enter “race” in the Label box).
   2. Hispanic: There is no ethnicity field so if you are interested in Hispanic ethnicity, you need to enter “Hispanic” in the Label box. Select the HISP field.
   3. Poverty: Select POVPIP, Income-to-poverty ratio. A message will appear explaining that this field is continuous and that you will have to create groups (i.e., <100% Poverty, <125% Poverty, etc.). Click on X to close that message. See instructions regarding creating groups below.
      
   4. If any of your variables need to be grouped (like POVPIP), click on the Cart link near the top of the screen. If none of your variables need to be grouped, click on View Table in the bottom right of the screen when you have selected all variables.
5. On the Cart screen, you can create groups and relabel categories.
   1. RAC1P field:
      1. Suggested Groups: White (White alone), Black (Black or African American alone), AIAN (American Indian alone, Alaska Native alone, & American Indian and Alaska Native tribes specified; or American Indian or Alaska Native, not specified and no other races), Asian (Asian alone), Other (Native Hawaiian and Other Pacific Islander alone (because this is a very small group in this area) and Some other race alone), and Two or More Races (Two or More Races).
      2. Click on Create Custom Group, enter a new label in the Group Label line (type over “Not Elsewhere Grouped”) and select all the race categories that should be included in the new group. Click on Save Group.
         
      3. Click on the pencil icon next to Not Elsewhere Grouped and repeat the last step until all custom Race groups are created.
         
      4. Rename the Field by clicking on its current title (Recode for Recoded detailed race code (RAC1P_RC1). Enter Race as the new label. Click Save.
         
   2. HISP field:
      1. Suggested Groups: Not Hispanic/Latino (Not Spanish/Hispanic/Latino), Hispanic/Latino (all other options).
      2. Click on Create Custom Group, enter a new label in the Group Label line (type over “Not Elsewhere Grouped”) and select all the categories that should be included in the new group. Click on Save Group.
         
      3. Click on the pencil icon next to Not Elsewhere Grouped and repeat the last step until all custom Race groups are created.
         
      4. iv.Rename the Field by clicking on its current title (Recode for Recoded detailed Hispanic origin (HISP_RC1). Enter Ethnicity as the new label. Click Save.
         
   3. POVPIP field:
      1. Suggested Groups: Different poverty levels could be defined, but for this example, because this organization can serve clients up to the Less than 200% Poverty Level, two groups are created: Less than 200% Poverty and 200% Poverty & Above.
      2. Click on Create Custom Group:
         1. Enter Less than 200% Poverty in the Group Label line (type over “Not Elsewhere Grouped”)
         2. Uncheck the category that begins with N/A and the 501 percent or more category.
         3. Click on the pencil icon next to the Below 501 percent category.
         4. Enter Minimum=0 and Maximum=199 in the pop-up window. Click Save. Click Save Group.
            
      3. Click on the pencil icon next to Not Elsewhere Grouped:
         1. Enter 200% Poverty & Above in the Group Label line (type over “Not Elsewhere Grouped”)
         2. Uncheck the category that begins with N/A.
         3. Leave Between 200 and 500 and 501 percent or more checked. Click Save. Click Save Group.
            
      4. Remove Not Elsewhere Grouped: Uncheck Not Elsewhere Grouped because all that remains in this group is the N/A category for whom poverty could not be measured. 
         
      5. Rename the Field by clicking on its current title (Recode for Recoded detailed Income-to poverty ratio recode (POVPIP_RC1). Enter Poverty as the new label. Click Save.
         
6. The custom grouped fields you just created will all have “_RC1” at the end of the field names.
7. Click on View Table at the bottom right side of the screen to be taken to the Table screen in which you can rearrange fields by rows and columns.
   1. Drag POVPIP_RC1 to Columns. Drag HISP_RC1 & RAC1P_RC1 to Rows next to Selected Geographies.
   2. Drag the non-grouped fields (RAC1P & HISP) into the section called Not on Table.
   3. Leave POVPIP in the Cell Value Options section.
   4. Under Select Cell Value Option, select Count.
   5. Under Select Weight, select PUMS person weight.
8. Click on Download/Share in the bottom right side of the screen and select Excel and Export Table Data.
   
9. Open the Excel file. Review the columns and rows and make formatting and text changes that will help you read the data, like boldening totals, changing the number format to show thousand separators, deleting subtotals if not needed, adding borders. See unformatted/unedited data on the left and formatted/edited data on the right:
   
10. Copy the Non-Hispanic race numbers and the total Hispanic/Latino number from the Less than 200% column into a calculation table to the right of the data, sort the data from largest to smallest.
    

Collecting Internal Case Management System Data:

1. Download data from your case management system (CMS), use an API link to connect to data in your CMS, or connect to your CMS data directly via an ODBC connection or other method.
   1. Make sure to include intakes that were closed with service (either in the year they were opened or later) and cases rejected without service.
2. Create Formulas in the downloaded data (using Excel in this example).
   1. The following formulas create categories that are helpful for analysis.
      1. Copy these formulas into cells in the far-right columns of your downloaded data (make sure to update the formula cell references so that they are pointing to the correct fields in your data).
      2. Add these formulas to all rows of data before creating pivot charts.
   2. Poverty Ranges: =IF(AND(AE2=0,ISBLANK(Z2)),"Unknown", IF(AND(AE2=0,NOT(ISBLANK(Z2))),"a. Below 100% FPL", IF(AE2<100,"a. Below 100% FPL", IF(AE2<125,"b. Below 125% FPL", IF(AE2<200,"c. Below 200% FPL", "d. 200%+ FPL"))))).
      1. Note that AE2=Percentage of Poverty & Z2=Types of Income.
      2. Because some databases enter Poverty=0% when Income is blank, this formula ensures that you do not overcount those who actually have $0 income and 0% Poverty Level.
   3. Race Formula: =IF(AO2="Black or African American","Black", IF(AO2="American Indian or Alaska Native","AIAN", IF(OR(AO2="Other",AO2="Native Hawaiian or other Pacific Islander"),"Other", IF(OR(AO2="",AO2="Prefer not to disclose"),"Unknown", AO2))))
      1. Note that AO2=Race.
      2. This formula is written so that the race categories match the MDAT PUMS data downloaded earlier.
   4. Ethnicity Formula: =IF(OR(AP14="Hispanic",AQ14="Latino/Hispanic"),"Hispanic/Latino", IF(OR(AP14="Non-Hispanic",AQ14="Non-Latino/Non-Hispanic"),"Non-Hispanic/Non-Latino", IF(OR(AND(AP14="",AQ14=""),AQ14="Chose not to respond"),"Unknown", IF(AQ14<>"",AQ14,AP14))))
      1. Note that AP2=Ethnicity & AQ2=HUD 9902 Ethnicity.
      2. This formula takes entries in both ethnicity-related fields and defines ethnicity as Hispanic/Latino, Non-Hispanic/Non-Latino, or Unknown based on both fields.
   5. Served or Not: =IF(O3<>"","Not Served", IF(ISBLANK(N3),"Remaining Open", IF(OR(LEFT(N3,1)="A",LEFT(N3,1)="B",LEFT(N3,1)="F",LEFT(N3,1)="G",LEFT(N3,1)="H",LEFT(N3,1)="I",LEFT(N3,1)="K",LEFT(N3,1)="L"),"Served", IF(OR(LEFT(N3,1)="R",LEFT(N3,1)="X"),"Not Served",""))))
      1. Note that O2=Rejection Reason & N2=Close Reason.
      2. This formula divides cases up into Served, Not Served, & Remaining Open categories.
         
3. Create Charts
   1. Click Insert along the top ribbon, then click on PivotTable.
   2. You will see a blank PivotTable.
   3. Sort the fields in the PivotTable Fields window, by clicking on the Gear Icon, and selecting Sort A to Z.
      
   4. Add the Matter/Case ID (or Case Number) field to the Values window and make sure the calculation is Count.
      1. If you see Sum of Matter/Case ID, click on the drop down, select Value Field Settings, and change the calculation type to Count.
         
   5. Drag the newly created Ethnicity Formula and Race Formula fields into the Rows window and Served or Not Formula into the Columns window and notice how the PivotTable updates.
      1. Click on the Row Labels drop-down in the PivotTable and uncheck Unknown Ethnicity and Unknown Race. There may be significant blank data when working with intakes and prescreens. You may want to leave the blank data in your charts, but hiding it provides the viewers insight into the demographic characteristics among those for whom that characteristic is known. These known data can serve as a good representation, or proxy estimate, for all those who requested and received or did not receive assistance.
      2. In this example, we are interested in the intakes that were Served and Not Served, so hide the intakes that remain open, by unchecking Remaining Open in the PivotTable Column Labels drop-down.
      3. Note you may also filter the fields included in the PivotTable by clicking on the field names in the list of fields available for the PivotTable and clicking on the drop-down to the right of each field name. Fields that have filters applied will show a funnel icon in the field list.
         
   6. Copy the Non-Hispanic/Non-Latino race numbers and the total Hispanic/Latino numbers for both Served and Not Served intakes into calculation tables to the right of the data,
   7. Sort the data in the same order as the MDAT PUMS Eligible Population data calculation table.
      
   8. Create 3 charts using the MDAT data, the Served Intakes data, and Not Served Intakes data.
      1. Format the charts identically.
      2. Change the Chart Type: With any chart selected, click on Design along the top ribbon and then click on Change Chart Type (in this example, Pie Chart was selected).
      3. Change the color palette: With any chart selected, click on Design along the top ribbon and then click on Change Colors. Or change the fill color for each pie slice by right clicking within the slice and selecting your preferred colors from the Fill option
      4. Edit the Title: Click on the existing titles and type over them. You may change the font size and make it bold.
      5. Under Chart Elements, remove or move the Legend.
      6. Add Data Labels, and Under Format Data Labels edit them to show Percentage and Category Name, change the Number Category, and adjust the number of decimals places that appear.
   9. Note Findings:
      1. Compare the Served Intakes pie slices to the eligible population Less than 200% Poverty Level pie slices. Notice for example, the share of Served Intakes from Hispanic/Latino clients (32.4%) is smaller than their share of the eligible population (42.3%). This might indicate the need for more outreach among the Hispanic/Latino population in the service area. Other comparisons across pie charts may uncover other data relationships.