ACS Data Users Group

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!

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.

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

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.acsdatacommunity.prb.org/.../ACSDT1Y2021.B01001_5F00_Processed.xlsx

[quote userid="3056" url="~/discussion-forum/f/forum/1248/share-of-people-living-in-metro-areas/3347#3347"]split the difference equally when a metro covers two or more states[/quote]

Don't do that!

It was waayyy off (but just a first pass test)

Derek,
If you'd like, I can send you my current file of all counties, tagged as metro, micro or non-metro.
Then you can do a simple Excel pivot to get the total populations for each, and then calc the percent of the state pop which live in metro, micro or rural (non-metro) counties.
bert@bestplaces.net

Bert

Heck, here it is.

State - Metro - Micro - (blank) Grand Total

AK	499391	46179	192995	738565
AL	3700785	704785	445201	4850771
AR	1836700	601159	540085	2977944
AZ	6463394	245105	101447	6809946
CA	38150785	561978	270084	38982847
CO	4742932	388541	305046	5436519
CT	3410024	184454		3594478
DC	672391			672391
DE	943732			943732
FL	19572572	359957	345918	20278447
GA	8425832	989672	786131	10201635
HI	1154240	267418		1421658
IA	1839602	548110	730390	3118102
ID	1210435	308583	138357	1657375
IL	11368650	878447	607429	12854526
IN	5148335	1006401	459682	6614418
KS	1964162	548031	391627	2903820
KY	2590620	879387	954369	4424376
LA	3899951	435604	327906	4663461
MA	6690206	88201	10912	6789319
MD	5844265	69847	81967	5996079
ME	785377	121289	423492	1330158
MI	8127926	1133056	664586	9925568
MN	4254185	724839	511702	5490726
MO	4527793	716743	830764	6075300
MS	1371209	971721	643290	2986220
MT	363592	309309	356961	1029862
NC	7847998	1570477	634089	10052564
ND	369423	178745	197307	745475
NE	1226066	327642	340213	1893921
NH	835402	448922	47524	1331848
NJ	8960161			8960161
NM	1394633	617072	73123	2084828
NV	2616097	238446	33182	2887725
NY	18408445	995374	394409	19798228
OH	9245993	1912818	450945	11609756
OK	2614084	739881	542286	3896251
OR	3367883	558682	98562	4025127
PA	11312047	1099819	378639	12790505
RI	1056138			1056138
SC	4143953	442844	306647	4893444
SD	409204	229129	217111	855444
TN	5101201	867237	628943	6597381
TX	24378998	1662800	1377814	27419612
UT	2675386	174396	144159	2993941
VA	7315717	270545	779690	8365952
VT	216751	245010	162875	624636
WA	6448492	566998	154477	7169967
WI	4265553	776858	720806	5763217
WV	1132018	302103	402722	1836843
WY	178054	244586	160560	583200

Thank you, Bert! I will give this a look in a bit.

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 I have a minute tomorrow I'll see if I can find a link in my notes. If you can program using the API, then you loop over the list (I use R) download B01003, take the number from the table and throw it into a dataset. You can then reaggregate any way you want.

Dave

PS

GEOCORR has everything so when the mosquitos come out and Glenn comes in from the patio he should be able to find something.

HiBert Sperling This looks pretty good, and the comparison to 2019 data seems right. Can you tell me the source of this data please?

It's the ACS 2021 5yr populations, and the CBSA definitions are from the latest delineation files from the OMB (Mar2020) .
I find it easiest and most flexible to download the tables and maintain my own files to slice and dice as needed.
Shoot me an email and I'll hook you up.

The "Detailed Tables" from the ACS (i.e., the ACS Summary Files) include "geographic components" that allow you to breakdown data for certain levels further by metro / non-metro / micropolitan / urban / rural and a few other geographic subcategories. The set of available level-component combinations is listed in the "Appendix B" sheet in the Summary File Appendices, accessible through this page. That shows that you can get data for people "in metropolitan statistical areas" by state for the 1-year tables.

You could extract these data from the raw summary files, but they're a bit bulky. And unfortunately, as far as I can tell, there's no easy way via data.census.gov to get data for a single geographic component for all states at once. It seems you'd have to click on a separate box for the component of interest once for each of the states. (Ugh.) (EDIT: I now see that Glenn Rice pointed out the same problem in another response... so it's not just me that can't find another way to do this!)

Fortunately, we make it pretty easy (IMO) to get this type of data via IPUMS NHGIS. Through the NHGIS Data Finder, I'd select a "Geographic Levels" filter for "State", and then select a Years or Datasets filter for the ACS year I'm looking for. Then find and select the table of interest (in this case, B01003. Total Population), continue to the Options page, and there I can select which "spatial breakdowns" I'd like to include in my data request. I'd select the breakdowns for "Total area" and "In metropolitan statistical area" and continue on to request and download the data. Then I can get state-level total population and population in metro areas in one file, and just divide one by the other to compute the share.

Good solution, Jonathan. NHGIS is an endless resource!
But how can one get an accurate answer using only 1-year tables?
By my reckoning, 461 of the 1180 major metro component counties are below the 65,000 population threshold to have 1-year data. Wouldn't those counties be excluded from the calculations?
Or one could use Population Estimates for 2022, which would include all counties.

That's not a problem with the "geographic component" data because it break downs by state, not by metropolitan area parts within each state. While many of the individual parts have less than 65,000 residents, the total metropolitan population by state is greater than 65,000 for every state. I just downloaded the 2021 1-year data, and the smallest single total is 180,418 for Wyoming's metro population. All of the required data are there.

I'm quite sure they don't omit populations in smaller-population metro parts from these totals... That's the sort of thing one might do to protect privacy, but as I understand, the 65,000 limit is not about protecting privacy; rather, it's what's deemed necessary for an adequate sampling size. Adding up all metropolitan parts within each state gets there.

Very interesting. Thanks for the insight, Jonathan.
I'll have to wrap my head around this. What especially confounds me are the multi-state metros, and how they are able to apportion the population between states when some of the counties have pops below the 1-yr threshold of 65,000.
For example, Jasper (33k) and Newton (14k) are Indiana counties which comprise part of the Chicago-Naperville-Elgin, IL-IN-Wi metro. I would think their population is too small to be included if we're only looking at 1-year data, since there are no 1-year pops for these counties.
Maybe the Census maintains 1-year data at a smaller level for internal analysis and aggregation.

Yes, exactly. The ACS collects data all over the place every year, including in small-population counties. It's a complete nationwide sample. Over the course of 5 years, this adds up to a large enough sample to report estimates even for areas with small populations (though often still with large margins of error). The reason they don't report 1-year estimates for small-population areas is not because there's no sample in those areas; it's because it's not an adequately large sample to produce generally useful estimates. So yes, they almost certainly collected many 2021 ACS responses in those Indiana counties you mention. The 1-year sample isn't big enough to publish 2021 1-year estimates for those counties, but they do publish 2017-2021 5-year estimates for those counties, which include the 2021 responses, and they publish 2021 1-year estimates of Indiana's metropolitan population, which also includes the 2021 responses in those smaller counties.

Thanks very much! I see what you mean, i.e. the collection of the 1-year data and how it's used even though it's not reported.
Very insightful.

Bert