Data Analysis Tool For All

U.S. Census Bureau’s American Community Survey (ACS) Public Use Microdata Sample (PUMS) via the Microdata Access Tool:

• Benefits: With this data source and this tool users have access to more topic detail 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: but only for States, Public Use Microdata Areas (PUMAs), and national regions and divisions at
• To learn more about Public Use Microdata Sample (PUMS): American Community Survey Microdata
• To learn more about the Microdata Access Tool: How-to Materials for Using the Microdata Access

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, rejected without service, or remain open.
  • 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.

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

1. Open Microdata - Census Bureau Datasets (MDAT).
2. Select a Dataset & Select a Vintage & click Next. (ACS 1-Year Estimates Public Use Microdata Sample for 2023).
   1. For information about choosing 5-year or 1-year estimates, refer to Using 1-Year or 5-Year American Community Survey Data.
      
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).
   1. 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.
   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 in the next step.
      

   4. If any of your variables need to be grouped, 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. Note that custom grouped fields you create will have “_RC1” at the end of their field names.
   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 example 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. 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. Click on View Table at the bottom right side of the screen to be taken to the Table screen in which you can rearrange which fields appear in which 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.
       

7. Click on Download/Share in the bottom right side of the screen and select Excel and Export Table Data.

   

8. 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 on left and formatted/edited data on right:

9. 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, and create a pie chart using this data.
10. Format the chart:
    1. Change the Chart Type: Click on Design along the top ribbon and then click on Change Chart Type (in this example, Pie Chart was selected).
    2. Change the color palette: 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
    3. Edit the Title: Click on the existing title and type over it. You may change the font size and make it bold.
    4. Under Chart Elements, remove or move the Legend.
    5. 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.
       
       

Part 2: 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.
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 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(AP2="Hispanic",AQ2="Latino/Hispanic"),"Hispanic/Latino", IF(OR(AP2="Non-Hispanic",AQ2="Non-Latino/Non-Hispanic"),"Non-Hispanic/Non-Latino", IF(OR(AND(AP2="",AQ2=""),AQ2="Chose not to respond"),"Unknown", IF(AQ2<>"",AQ2,AP2))))
      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.
         
         
3. Create Charts
   1. Click Insert along the top ribbon, then click on PivotTable.
      1. Place the first PivotChart in a New Worksheet and rename it Charts.
   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 Axis (Categories) window and notice how the PivotTable updates. 

      

   6. Copy the Non-Hispanic/Non-Latino race numbers and the total Hispanic/Latino numbers into a calculation table to the right of the data, sort the data in the same order as the MDAT PUMS data calculation table, and create a pie chart using this data. 

      

   7. Create a chart using this data and format the chart to match the formatting on the MDAT PUMS data chart.
   8. Findings: Compare the two pie charts and where the intake/prescreen slices are smaller than the corresponding eligible population pie slices. For example, the share of intakes/prescreens from Hispanic/Latino clients (32.8%) 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.