TableIDs in the ACS 2015-2019 metadata file named 2019_DataProductList.xlsx

Hello all!

This is my first post, and I am seeking some guidance

The ACS 2015-2019 metadata file named 2019_DataProductList.xlsx states that there are 57 separate TableIDs for subject "X_26" (GROUP QUARTERS)

But how do I know in advance at what geographic region this information is avaialble? I did a test with "census tracts" for the state of Hawaii (State FIPS 15)

But if one actually downloads the pre-joined TIGER/Line shapefiles in geodatabase and shapefile format with “Selected Demographic and Economic Data” for census tracts in the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates there is only ONE TableID (B26001) with only one field/variable called B26001e (and the associated measurement error B26001m)!!

https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-data.html

1. So I am so curious what happened to all other 56 TableIDs for the state of Hawaii at the census tract level in the 2015-2019 American Community Survey (ACS) 5-Year Estimates

2. How do I know in advance what TableIDs are provided for each subject area at the census tract level, census subdivision (CSD), county and state level  in Hawaii?

3 There appear to be 29 subject areas in the 2019 American Community Survey (ACS) 5-Year Estimates. Fortunately 23 of the 29 subject areas had all of the TableIDs advertised in the 2019_DataProductList.xlsx metadata

But what about the remaining six subject areas?  How does one know how many Table IDs are actually in the “Selected Demographic and Economic Data” for the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates for each Table ID at each level of analysis

I manually checked each of the table IDs in each of the 29 subject areas for the census tract data in Hawaii, but surely there is a summary list somewhere?

cheers, Jason

  • Hi Jason - the pre-joined do not have all the tables in them. that's what they mean by "Selected data."  You can see what level the tables are available at using Appendix A in the 5-year appendices documentation found on the Census website here: https://www.census.gov/programs-surveys/acs/data/summary-file.2019.html  I created an updated version of that with an added Appendix C that includes the table structure here: https://gisdiva.com/downloads/acs2019-5yr/

  • Dear JamieRae

    Thanks for providing such rapid and valuable feedback. That clears up a lot!

    1. I am curious about whether there is a simple CSV file that has information about all the Census Designated Places (CDP) in a given state. 

    For Hawaii  I can only find ACS information on blocks/block groups, census tracts, county subdivisions, counties, and the state

    U.S. Census Bureau QuickFacts: Nanakuli CDP, Hawaii

    https://www.census.gov/quickfacts/nanakulicdphawaii

    It seems that some CDPs variables cannot be directly compared to the above geographies:

    "Estimates are not comparable to other geographic levels due to methodology differences that may exist between different data sources. Some estimates presented here come from sample data, and thus have sampling errors that may render some apparent differences between geographies statistically indistinguishable."

    I am hoping there is a way to compare  CDPs over time? Say compare 2015-2019 5 year ACS with 2010-2014 5 year ACS (or 2019 1 year ACS with 2018 1 year ACS). Is there a file that has the data for different years for the CDPs?

    2. I am unclear about the "Summary File Starting and Ending Positions" mentioned in Appendix A  ("Sequence Tables (SEQ) in the 5-Year Summary Files").

    For example the first TableID is B01001 with "7 to 55" followed by  B01001A from "56 to 86"

    There are indeed 49 variables/fields associated with B01001 (55-7+1).

    But I am not sure which file I should use to find row 7? I have been using the table shells and table lists from below (and the excel files associated with B01001 begin at row 2 or 3, not row 7)

    www.census.gov/.../table-shells.2019.html

    Table Shells and Table List (census.gov)

  • Apologies, but the documentation is for the Summary Files, so you need a better understanding of how the summary files work in order to completely understand all the information in the Appendices document I mentioned. You'll need to review the handbook on how to work with the ACS files to get a full understanding. See here:

    www.census.gov/.../acs_summary-file_handbook_2019.pdf

    1) Since all places in Hawaii are CDPs you would use Summary level 160 " State-Place" (see Appendix B).  If you're using SQL you could do a simple query on the Geography table for al records with sumlevel = 160. 

    2) the starting and ending positions refer to the summary sequence files which are raw text files. This is explained in the handbook I linked above.

    I don't know your skill level, but if you're not familiar working with raw text files and importing them into relational databases and linking them together, I would suggest you use the Excel file as a guide to look up the table numbers at data.census.gov and then export the data from there. then at data.census.gov you can modify the geography for the table by clicking the "Geos" icon at the top. then select by geography or summary level. 

    here's a link to the table B01001 for all CDPs in Hawaii (summary level 160):
    data.census.gov/.../table

  • Other folks on here have experience with many other Census data retrieval tools, so they may be able to point you to other useful resources. I'm "old school" and have been working with the raw summary files since the 1990 Census so I always load them into SQL and do my queries that way. hopefully I didn't confuse you! 

  • I have ArcGIS Pro/ArGIS Online and Excel and SPSS (but no SAS). Therefore I would like to learn how to use read the ACS Summary File into Excel for beginners. I would like to consider the simplest possible case for now 

    Therefore, I am using the 2019 1 year data for sequence 1 in Hawaii (and later ideally sequence 1 in Hawaii for all geographies). I followed these instructions 

    Instructions on How to Read the ACS Summary File into Excel (census.gov)

    I now have the following downloaded:

    two excel files:

    ACS_2019_SF_1YR_Appendices.xls

    1-year_Mini_Geo.xls

    two folders:

    2019hi01000

    Hawaii_All_Geographies

    It is not clear to me what to do next from these instructions

    Instructions on How to Read the ACS Summary File into Excel (census.gov)

  • Under the Excel Import Tool Instructions (on page 2 of 10) it states:

    "Unzip the files to a single local directory. Open the template “Seq1” (in Excel) and follow the steps below

     I am stuck here. I don't know how to Open the template “Seq1”. I don't even see Seq1? 

  • sounds like you downloaded the wrong file? Try getting the data directly from the FTP site:

    www2.census.gov/.../

    Also, FYI - you will need the 5 year data if you want CDP level data. 

    I'm afraid I can't help you much with the Excel/SPSS route. You should dig into the documentation provided by the Census Bureau to get a thorough understanding of the files and how they work together. Or perhaps someone else here has a script already developed to read the files into SPSS and can help you out. Sorry I'm not much help. If you were using SQL I could provide files for that. 

  • OK.  i am not familiar with SQL. Do I need to buy an SQL product ?

    I went to the FTP site you listed above and found this. I previously downloaded

    1_year_seq_by_state >> Hawaii >> 

    20191hi0001000.zip 21-Aug-2020 10:36 17K  

    Is that correct, or do I need other folders from here as well?

     1_year_by_state/01-Sep-2020 20:24-  1_year_comparison_profiles/08-Sep-2020 09:52-  1_year_entire_sf/01-Sep-2020 20:29-  1_year_geographic_comparison_tables/08-Sep-2020 09:47-  1_year_ranking/08-Sep-2020 10:15-  1_year_seq_by_state/01-Sep-2020 22:48-  5_year_by_state/18-Nov-2020 14:50-  5_year_entire_sf/18-Nov-2020 17:25-  5_year_seq_by_state/18-Nov-2020 19:42-  2019_1yr_Summary_FileTemplates.zip20-Aug-2020 17:452.0M  2019_5yr_Summary_FileTemplates.zip03-Dec-2020 07:541.6M 


  • Dear Jami, I appreciate your offer to help with SQL. Accordingly I installed  the Micrsoft 2019 SQL Server Express edition. Will this work with the American Community Survey data?

  • Yes that should work. You can download the SQL files here: https://gisdiva.com/downloads/acs2019-5yr/

    Sorry I dont have Hawaii pulled out separately, and don't have time to pull that out and upload it as I'll be on traveling the next 2 days. So you'll need to download the big file along with the Readme file and the Excel file. You should only need Hawaii - look for the file ACS2019_5yr_hi.7z in the large zipfile. read the README file for more details.

    Good luck. If all else fails, I suggest you get the tables you need form data.census.gov  - easy to query and download then link to your shapefile via the GEOID. I kind of feel like you're getting more than you really need - but I am not sure what your project involves.    

  • Hi JamieRae

    thanks so much for providing some help! I think I am overcomplicating things

    Yes, downloading a specific tableID from data.census.gov and using an ArcGIS Pro join "via the GEOID" is very trivial. The US Census even provides a video tutorial and it is something our undergraduate students can do.

    But I am trying understand how to load (any) raw summary file into SQL and do a query . So I unzipped your maine folder with 7zip (ACS2019_5yrs_me.zip) which contains  ACS2019_5yr_me.bak

    We spent the past few hours with our IT department trying to understand your instructions on how to restore the file

    Using the free SQL Server (Express) 2019 together with SQL Server Management Studio (SSMS) 18.10 there is an option to restore BAK files I believe. The GUI steps are 

    1. database

    2. restore database

    3. devices 

    4. search for folder (at the three dots) ... and add the folder

    But here we cannot navigate to your folder. After multiple hours of trying the university help desk just said I should go back and ask you why about the solution. Maybe this is the problem and solution?

    Cannot navigate to some folders when attempting to attach .MDF files to database in Management Studio (microsoft.com)

    But our university really is unfamiliar with Microsoft SQL and system level/registry permissions so we are stuck. It is easier it seems to just click in the SQL Server Management Studio (SSMS) to backup a file but the below command also doesn't work for us

    We also tried entering this directly:

    RESTORE DATABASE @ACS2019_5yr_me FROM
    @C:\Users\jlevy\Desktop\jlevy\ + @ACS2019_5yr_me.bak

    This was the error message:

    Msg 137, Level 15, State 2, Line 2
    Must declare the scalar variable "@ACS2019_5yr_me".

    Completion time: 2021-12-05T11:50:12.2076973-10:00

    We are open to any suggestions. Maybe it is simpler to access a summary file that does not have a bak extension? Maybe there is just a simple video to show how to access the summary files and do a query with SQL Server Managemetn Studio 18.10 and SQL Server 2019?

    best, jason

  • JamieRae

    I probably forgot to say we are so happy you reached out and offered a sample file:

    ACS2019_5yr_me.bak

    A typo (augh) in the above

    I wanted to say "the SQL Server Management Studio (SSMS)" seems to have a simple GUI to "restore" bak files. But when trying to restore the file we are unable to access the folders we download from your website

    maybe this is the issue and solution (but our help desk thinks there may be permissions challenges associated with your folders and asked me to reach out again)

    Cannot navigate to some folders when attempting to attach .MDF files to database in Management Studio (microsoft.com)

  • The sequence files need to be combined "sideways" first. One table may require multiple sequence files, or a single sequence file can contain several tables. If you need row 7 of some table, you will need the sequence file that contains that table and row, and count across (not down) to the 7th value after the start of the data for the table. The "rows" in the sequence file are geographies.

    Working with summary data sequence files is tricky. I would never use them for direct analysis. They almost always need substantial preprocessing to turn them back into usable tables.

  • Hi Glenn

    Thanks for your reply. I don't need a specific row of a specific table

    I am just trying to find step by step instructions to US Census data into SQL and do a query. Ideally with the free SQL Server (Express) 2019 together with SQL Server Management Studio (SSMS)

    However I would be curious about the terminology of Table numbers/IDs now that you mentioned it.

    Consider Subject/Topic Area Age and Sex (X01). From the metadata there are

    • 21 table IDs for the 5 year ACS 2015-2019 data (also called "1,5" year period) and
    • 9 table IDs for the 1 year ACS 2019 data (also called "1" year period)

    So do I express this as follows:

    • there are 21 Table IDs that are associated with both the 5 year  ACS 2015-2019 census and the 1 year ACS 2019 and
    • 9 table different ID's exclusively associated with the 1 year ACS 2019 data
    • Overall the 1 year ACS 2019 data has 30 Table IDs associated with the Subject/Topic Area Age and Sex.

    I want to start documenting Table IDs since I was unable to find which "select Table IDs" are actually available in the twenty-nine topic/subject areas found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro so I had to document this myself

    For X01 for example all of the 30 Table IDs found below were also found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro

     cheers, jason

    Table ID Table Title Table Universe Year Fields
    5-yr data
    B01001 SEX BY AGE Universe: Total population 1,5 49
    B01001A SEX BY AGE (WHITE ALONE) Universe: People who are White alone 1,5 31
    B01001B SEX BY AGE (BLACK OR AFRICAN AMERICAN ALONE) Universe: People who are Black or African American alone 1,5 31
    B01001C SEX BY AGE (AMERICAN INDIAN AND ALASKA NATIVE ALONE) Universe: People who are American Indian and Alaska Native alone 1,5 31
    B01001D SEX BY AGE (ASIAN ALONE) Universe: People who are Asian alone 1,5 31
    B01001E SEX BY AGE (NATIVE HAWAIIAN AND OTHER PACIFIC ISLANDER ALONE) Universe: People who are Native Hawaiian and Other Pacific Islander alone 1,5 31
    B01001F SEX BY AGE (SOME OTHER RACE ALONE) Universe: People who are Some Other Race alone 1,5 31
    B01001G SEX BY AGE (TWO OR MORE RACES) Universe: People who are Two or More Races 1,5 31
    B01001H SEX BY AGE (WHITE ALONE, NOT HISPANIC OR LATINO) Universe: White alone, not Hispanic or Latino population 1,5 31
    B01001I SEX BY AGE (HISPANIC OR LATINO) Universe: People who are Hispanic or Latino 1,5 31
    B01002 MEDIAN AGE BY SEX Universe: Total population 1,5 3
    B01002A MEDIAN AGE BY SEX (WHITE ALONE) Universe: People who are White alone 1,5 3
    B01002B MEDIAN AGE BY SEX (BLACK OR AFRICAN AMERICAN ALONE) Universe: People who are Black or African American alone 1,5 3
    B01002C MEDIAN AGE BY SEX (AMERICAN INDIAN AND ALASKA NATIVE ALONE) Universe: People who are American Indian and Alaska Native alone 1,5 3
    B01002D MEDIAN AGE BY SEX (ASIAN ALONE) Universe: People who are Asian alone 1,5 3
    B01002E MEDIAN AGE BY SEX (NATIVE HAWAIIAN AND OTHER PACIFIC ISLANDER ALONE) Universe: People who are Native Hawaiian and Other Pacific Islander alone 1,5 3
    B01002F MEDIAN AGE BY SEX (SOME OTHER RACE ALONE) Universe: People who are Some Other Race alone 1,5 3
    B01002G MEDIAN AGE BY SEX (TWO OR MORE RACES) Universe: People who are Two or More Races 1,5 3
    B01002H MEDIAN AGE BY SEX (WHITE ALONE, NOT HISPANIC OR LATINO) Universe: White alone, not Hispanic or Latino population 1,5 3
    B01002I MEDIAN AGE BY SEX (HISPANIC OR LATINO) Universe: People who are Hispanic or Latino 1,5 3
    B01003 TOTAL POPULATION Universe: Total population 1,5 1
    total Table IDs (5 year) 21 359
    1 yr data
    C01001A SEX BY AGE (WHITE ALONE) Universe: People who are White alone 1 9
    C01001B SEX BY AGE (BLACK OR AFRICAN AMERICAN ALONE) Universe: People who are Black or African American alone 1 9
    C01001C SEX BY AGE (AMERICAN INDIAN AND ALASKA NATIVE ALONE) Universe: People who are American Indian and Alaska Native alone 1 9
    C01001D SEX BY AGE (ASIAN ALONE) Universe: People who are Asian alone 1 9
    C01001E SEX BY AGE (NATIVE HAWAIIAN AND OTHER PACIFIC ISLANDER ALONE) Universe: People who are Native Hawaiian and Other Pacific Islander alone 1 9
    C01001F SEX BY AGE (SOME OTHER RACE ALONE) Universe: People who are Some Other Race alone 1 9
    C01001G SEX BY AGE (TWO OR MORE RACES) Universe: People who are Two or More Races 1 9
    C01001H SEX BY AGE (WHITE ALONE, NOT HISPANIC OR LATINO) Universe: White alone, not Hispanic or Latino population 1 9
    C01001I SEX BY AGE (HISPANIC OR LATINO) Universe: People who are Hispanic or Latino 1 9
    total table IDs (1 yr) 9 81
  • I'm not sure i understand your question. The table shows availability of data tables for the 5-year and 1-year ACS releases. "1, 5" means that table is available in both 1- and 5-year ACS releases. "1" means that table is only available in the 1-year release. "Fields" is just the number of fields in each table.