Hi. I am looking for the ACS 1-year table that shows the share of people by state living in a metro area. I have collected this data before, but cannot seem to locate it. Thanks in advance!
Just a quick note. If all you need is total population B01003 has only one cell with the total population. It is easier to read with a program or on the screen.
Dave
As a note you might look for a relationship file on the census website that has census the tracts and/or county fips codes for the metro areas in the US I think that I may have one on my machine. When…
This would absolutely work, Bernie, but it seems even more tedious. The main impediment here is that data.census.gov does not include an option to select all metro areas within states in the US. TBH, it…
If you mean Metropolitan Statistical Areas, you could use something like: https://data.census.gov/table?t=Age+and+Sex&g=010XX00US$3100000&d=ACS+1-Year+Estimates+Detailed+Tables&tid=ACSDT1Y2021.B01001 which would give population by age and sex for MSAs within each state. You'll have to add them up by state and then compare to the state totals.
I take that back, looks like data.census.gov doesn't do within-state portions of MSAs that cross state lines. Or I'm doing it wrong.
EDIT: You'd need to do that one state at a time, unfortunately. Seems like a tedious job
Thanks, Glenn! I'll give this a shot. But very strange. I have a record of collecting the data last year, and my source note says ACS 1 Year Estimates, 2021, Table S1701. That must be the wrong citation, but I definitely recall collecting this data much more easily in the past.
Table S1701 is POVERTY STATUS IN THE PAST 12 MONTHS, which doesn't have much to do with population (assuming that's what you mean by "share").
Getting total populations for MSAs would be trivial. It's the "by state" part that will trip you up. Many MSAs cross state lines.
Thanks, David. In the absence of the data set I recall using last year (simply percent pop living in a metro area by state), is there a common approach that you know of to splitting metros that lie across two or more states?
Since metro areas are all made up of counties, it's not particularly hard to just aggregate the data for each county in a metro area (and you could subdivide by state as well). You might use these CBSA delineation files: https://www.census.gov/geographies/reference-files/time-series/demo/metro-micro/delineation-files.html. Be sure to differentiate between metropolitan statistical areas and micropolitan (which you'd presumably want to exclude).
This would absolutely work, Bernie, but it seems even more tedious. The main impediment here is that data.census.gov does not include an option to select all metro areas within states in the US. TBH, it seems like a programming bug, since it is possible to select all metro area state parts for any individual state.
I'm sure we have something at MCDC that would make this easier, but right now I'm enjoying cocktail hour on my patio...
Please continue to enjoy!! For later, I have attached a file:The file includes one set of columns in which I used B10001 (as David Dorer suggested). I created a program to split the difference equally when a metro covers two or more states.The other set of columns includes the results of last year's exercise, using the "Mystery Data". I definitely do not recall this being so tedious, so it's sort of driving me crazy ;) I have also emailed Census's help line.You'll see the share of people living in a metro area by state are similar in many cases, but the program to split the difference is clearly not the right approach here.ACSDT1Y2021.B01001_Processed.xlsx
Derek Thomas said:split the difference equally when a metro covers two or more states
Don't do that!
It was waayyy off (but just a first pass test)