ACS Data Users Group

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 >>

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

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.

I teach GIS and my students use the twenty-nine topic/subject areas found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro

It is easy to find the availability of data tables for the 5-year and 1-year ACS releases as you mentioned

However, my students are only using the prejoined "Selected Demographic and Economic Data” (Arcgis Pro geodatabase files with 29 standalone subject tables)i for the 2015-2019 American Community Survey (ACS) 5-Year Estimate

How would we find the metadata for that?

Consider subject/topic area 26 (Group Quarters): there is only ONE TableID (B26001) found in the Selected Demographic and Economic Data” but there are 57 TableIDs found in the regular census

So I had to create my own metadata tables for this so that our students would know what they are downloading

Hey Jason - here is the Census Bureau documentation on the pre-joined geodatabases: https://www.census.gov/programs-surveys/geography/technical-documentation/records-layout/tiger-line-demo-record-layouts.html

As for the SQL files - that could be a permission issue. I had a research assistant use these files and he said he had to save them to his root C drive to access them and run SSMS as admin to import them. Also please note that the data only includes the estimates and does not have the margin of error (MOE) tables.

I have to ask, though, is it possible for you to modify your curriculum to work with only the selected demographic data provided by the Census Bureau?

Also - I assume you are in Hawaii - if so, have you reached out to someone in the DBEDT office for assistance? Perhaps they can better guide you to what you are looking for. I feel I've only added confusion.

Here is some information from them:

https://census.hawaii.gov/home/geography/

Statewide GIS office:

http://planning.hawaii.gov/gis/

Also - a lot of folks connect to the census api to get data, and that may be better, I'm just not adept at using those. You might want to check out https://github.com/hrecht/censusapi

Dear JamiRae

Yes, in Hawaii and we are aware of those sites and agencies.

1. You have already provided valuable help!

2. Our assignment deals with the 151 Census Designated places in Hawaii. On this site I could not find the 5 year ACS 2015-2019 place tables broken down into state TIGER/Line with Selected Demographic and Economic Data (census.gov) (although there was an option to select state for Block Group, Census Tract and County Subdivision)

Therefore, I downloaded the Place geodatabase for the entire country and am extracting out the Hawaii records

Index of /geo/tiger/TIGER_DP/2019ACS (census.gov)

3. All of the above links deal seem to contain 5 year ACS 2015-2019 data (exclusively). Is there an area to download the 1 year ACS 2019 data as a zip file, rather than use data.census.gov and download them one at table at a time?

thank you so much again, Jason

p.s. Since I am using the latest Microsoft SQL tool and your dataset has been tested, theoretically there should be a solution?

I would be very interested in the future if anyone would be able to help me for a few moments by zoom? to help me access your files in Microsoft SQL (restore your BAK file, etc). We spent time with our tech folks but have the issues you mentioned Even just opening the tiny Maine dataset would be great. I am trying to learn the SQL approach

I also did check the documentation on the 5 year ACS 2015-2019 pre-joined geodatabases. I believe there are 29 main subject tables for the following geographies: Tract, Place, Block, County SubDivision etc

Also when I downloaded each of the 29 subject tables as CSV files for the 5 year ACS 2015-2019 they seem to have EXACTLY the same number of columns in excel (and exactly the same fields) in the Tract, Place, Block, County SubDivision data

Is that what you expected?

cheers, jason

Hey Jason - here is the Census Bureau documentation on the pre-joined geodatabases: https://www.census.gov/programs-surveys/geography/technical-documentation/records-layout/tiger-line-demo-record-layouts.html