Monthly Unemployment Rate Calculation for Two Groups of States

[Ekta Deshmukh]

Hi, I am working on a project in which I have data from the CPS. I also have data that tells me when a state decided to withdraw from FPUC. Each row is an individual and the variables include month, state, and a dummy variable for employment status (0=employed, 1 = unemployed, 2 = not in labor force). I am trying to calculate unemployment rate by month and by state (for instance, for all the individuals living in Maine, what is the number of people with employment status 1 divided by the number of people with employment status 0 + number of people with employment status 1) but am unsure of how to do so.

I also would like calculate monthly unemployment rates by groups of states. The two groups of states would be states that withdrew from FPUC and states that did not withdraw.

I have tried these lines of code:
by state month, sort: egen labor_force = total(inlist(empstat, 0, 1))
by state month: egen total_unemployed = total(empstat == 1)
gen unemployment_rate = total_unemployed/labor_force

Below is the output from the -dataex- command. Thank you for your help!

* Example generated by -dataex-. For more info, type help dataex
clear
input float date int year byte(month female age) int educ byte empstat float(employed unemployed) byte fpuc2_refusal int fpuc2_end float(refused not_refused fempstat)
732 2021 1 0 58 3 1 1 0 1 22450 1 . .
732 2021 1 1 54 4 1 1 0 1 22450 1 . 1
732 2021 1 0 50 3 1 1 0 1 22450 1 . 1
732 2021 1 0 46 3 1 1 0 1 22450 1 . 1
732 2021 1 0 23 3 1 1 0 1 22450 1 . 1
732 2021 1 1 58 2 3 0 . 1 22450 1 . .
732 2021 1 0 20 3 1 1 0 1 22450 1 . 1
732 2021 1 0 61 2 3 0 . 1 22450 1 . .
732 2021 1 1 17 2 3 0 . 1 22450 1 . 3
732 2021 1 0 20 2 3 0 . 1 22450 1 . .
732 2021 1 0 56 4 1 1 0 1 22450 1 . .
732 2021 1 0 48 3 1 1 0 1 22450 1 . 1
732 2021 1 0 34 3 1 1 0 1 22450 1 . 1
732 2021 1 0 23 3 1 1 0 1 22450 1 . .
732 2021 1 1 21 3 1 1 0 1 22450 1 . 1
732 2021 1 0 15 2 3 0 . 1 22450 1 . .
732 2021 1 0 53 4 1 1 0 1 22450 1 . 1
732 2021 1 1 60 3 3 0 . 1 22450 1 . .
732 2021 1 0 20 2 3 0 . 1 22450 1 . 3
732 2021 1 1 36 2 1 1 0 1 22450 1 . .
732 2021 1 1 64 3 1 1 0 1 22450 1 . 1
732 2021 1 1 63 4 1 1 0 1 22450 1 . 1
732 2021 1 1 22 3 3 0 . 1 22450 1 . 3
732 2021 1 0 59 4 1 1 0 1 22450 1 . 1
732 2021 1 0 37 5 1 1 0 1 22450 1 . 1
732 2021 1 0 20 3 3 0 . 1 22450 1 . 3
732 2021 1 1 55 2 3 0 . 1 22450 1 . 3
732 2021 1 1 37 4 1 1 0 1 22450 1 . 1
732 2021 1 1 52 2 1 1 0 1 22450 1 . 1
732 2021 1 0 48 4 1 1 0 1 22450 1 . 1
732 2021 1 1 58 3 1 1 0 1 22450 1 . .
732 2021 1 0 24 2 2 0 1 1 22450 1 . 1
732 2021 1 1 15 2 3 0 . 1 22450 1 . 3
732 2021 1 1 42 4 1 1 0 1 22450 1 . .
732 2021 1 0 16 3 2 0 1 1 22450 1 . 2
732 2021 1 1 32 3 1 1 0 1 22450 1 . 1
732 2021 1 1 57 3 1 1 0 1 22450 1 . 1
732 2021 1 0 44 3 1 1 0 1 22450 1 . 1
732 2021 1 1 32 3 1 1 0 1 22450 1 . 1
732 2021 1 1 59 4 1 1 0 1 22450 1 . 1
732 2021 1 1 58 3 1 1 0 1 22450 1 . 1
732 2021 1 0 38 3 3 0 . 1 22450 1 . 2
732 2021 1 0 39 3 3 0 . 1 22450 1 . 1
732 2021 1 1 40 4 1 1 0 1 22450 1 . 1
732 2021 1 0 53 3 1 1 0 1 22450 1 . .
732 2021 1 0 48 5 1 1 0 1 22450 1 . 1
732 2021 1 1 39 4 3 0 . 1 22450 1 . 3
732 2021 1 0 16 2 3 0 . 1 22450 1 . 3
732 2021 1 1 51 4 1 1 0 1 22450 1 . .
732 2021 1 0 20 3 1 1 0 1 22450 1 . .
732 2021 1 1 63 3 1 1 0 1 22450 1 . .
732 2021 1 0 41 4 1 1 0 1 22450 1 . .
732 2021 1 0 60 3 1 1 0 1 22450 1 . .
732 2021 1 1 45 3 3 0 . 1 22450 1 . 3
732 2021 1 1 60 3 3 0 . 1 22450 1 . 3
732 2021 1 0 41 3 1 1 0 1 22450 1 . 1
732 2021 1 1 59 3 3 0 . 1 22450 1 . 3
732 2021 1 1 42 3 1 1 0 1 22450 1 . 1
732 2021 1 0 60 3 1 1 0 1 22450 1 . 1
732 2021 1 1 63 3 3 0 . 1 22450 1 . 3
732 2021 1 0 17 2 3 0 . 1 22450 1 . 3
732 2021 1 0 21 3 1 1 0 1 22450 1 . .
732 2021 1 0 64 3 1 1 0 1 22450 1 . 3
732 2021 1 0 39 4 1 1 0 1 22450 1 . .
732 2021 1 0 17 2 3 0 . 1 22450 1 . 3
732 2021 1 1 49 4 1 1 0 1 22450 1 . 1
732 2021 1 0 34 3 1 1 0 1 22450 1 . .
732 2021 1 1 64 5 3 0 . 1 22450 1 . 3
732 2021 1 1 37 3 3 0 . 1 22450 1 . 3
732 2021 1 0 38 3 1 1 0 1 22450 1 . 1
732 2021 1 1 34 2 3 0 . 1 22450 1 . 3
732 2021 1 1 22 3 1 1 0 1 22450 1 . 1
732 2021 1 0 27 3 1 1 0 1 22450 1 . 1
732 2021 1 0 15 2 3 0 . 1 22450 1 . .
732 2021 1 0 25 4 1 1 0 1 22450 1 . .
732 2021 1 1 39 5 1 1 0 1 22450 1 . 1
732 2021 1 1 60 2 1 1 0 1 22450 1 . 1
732 2021 1 1 26 3 3 0 . 1 22450 1 . 3
732 2021 1 1 19 2 1 1 0 1 22450 1 . 1
732 2021 1 1 63 4 1 1 0 1 22450 1 . .
732 2021 1 1 32 2 1 1 0 1 22450 1 . .
732 2021 1 1 27 3 1 1 0 1 22450 1 . 1
732 2021 1 0 62 3 1 1 0 1 22450 1 . 1
732 2021 1 1 53 4 1 1 0 1 22450 1 . 1
732 2021 1 1 60 5 1 1 0 1 22450 1 . 1
732 2021 1 0 53 3 3 0 . 1 22450 1 . 3
732 2021 1 1 34 3 3 0 . 1 22450 1 . .
732 2021 1 0 17 2 3 0 . 1 22450 1 . .
732 2021 1 1 15 2 3 0 . 1 22450 1 . 3
732 2021 1 1 25 4 1 1 0 1 22450 1 . 1
732 2021 1 0 38 4 1 1 0 1 22450 1 . .
732 2021 1 0 20 3 1 1 0 1 22450 1 . 1
732 2021 1 1 15 2 3 0 . 1 22450 1 . .
732 2021 1 0 40 3 1 1 0 1 22450 1 . .
732 2021 1 0 57 3 1 1 0 1 22450 1 . 1
732 2021 1 0 36 3 1 1 0 1 22450 1 . 1
732 2021 1 0 28 4 1 1 0 1 22450 1 . .
732 2021 1 1 47 3 1 1 0 1 22450 1 . 1
732 2021 1 0 53 3 1 1 0 1 22450 1 . 1
732 2021 1 1 23 3 1 1 0 1 22450 1 . 1
end
format %tm date
format %tdnn/dd/CCYY fpuc2_end
label values month MONTH
label def MONTH 1 "january", modify
label values female yesno
label values employed yesno
label values unemployed yesno
label def yesno 0 "[0] No", modify
label def yesno 1 "[1] Yes", modify
label values age AGE
label def AGE 15 "15", modify
label def AGE 16 "16", modify
label def AGE 17 "17", modify
label def AGE 19 "19", modify
label def AGE 20 "20", modify
label def AGE 21 "21", modify
label def AGE 22 "22", modify
label def AGE 23 "23", modify
label def AGE 24 "24", modify
label def AGE 25 "25", modify
label def AGE 26 "26", modify
label def AGE 27 "27", modify
label def AGE 28 "28", modify
label def AGE 32 "32", modify
label def AGE 34 "34", modify
label def AGE 36 "36", modify
label def AGE 37 "37", modify
label def AGE 38 "38", modify
label def AGE 39 "39", modify
label def AGE 40 "40", modify
label def AGE 41 "41", modify
label def AGE 42 "42", modify
label def AGE 44 "44", modify
label def AGE 45 "45", modify
label def AGE 46 "46", modify
label def AGE 47 "47", modify
label def AGE 48 "48", modify
label def AGE 49 "49", modify
label def AGE 50 "50", modify
label def AGE 51 "51", modify
label def AGE 52 "52", modify
label def AGE 53 "53", modify
label def AGE 54 "54", modify
label def AGE 55 "55", modify
label def AGE 56 "56", modify
label def AGE 57 "57", modify
label def AGE 58 "58", modify
label def AGE 59 "59", modify
label def AGE 60 "60", modify
label def AGE 61 "61", modify
label def AGE 62 "62", modify
label def AGE 63 "63", modify
label def AGE 64 "64", modify
label values educ educcat
label def educcat 2 "[2] 5-12 grades, no diploma", modify
label def educcat 3 "[3] High school diploma", modify
label def educcat 4 "[4] College", modify
label def educcat 5 "[5] Graduate degree", modify
label values empstat empstat
label def empstat 1 "[1] Employed", modify
label def empstat 2 "[2] Unemployed", modify
label def empstat 3 "[3] Out of Labor Force", modify

[Clyde Schechter]

The example data you provide does not contain a state variable, or at least not one that can be recognized from its name. Also, all of the observations shown are from a single month: January 2021.

I will also guess that the variable fpuc2_end is the date at which the state referred to in the observation (in the full data set, though not in your example) withdrew from FPUC. This seems reasonable to me, but as you do not explain the data (and who knows what FPUC is anyway) it may be incorrect.

Your code for calculating the employment rates per state by month is probably incorrect. The variable month in your data set would be more properly thought of as "month of the year" so that when you use it in the code it looks at all observations from January of any year as being the same "month" which is not normally what is wanted. Normally one would want to get separate rates for each year. This requires creating a variable that distinguishes months and years, not just month of the year. (That said, if what you are interested in is cyclic monthly variation that recurs annually, then what you did would be correct.)

So, overall, I would do this:

gen int mdate = ym(year, month)
format mdate %tm


by state mdate, sort: egen monthly_state_labor_force = total(inlist(empstat, 0, 1))
by state mdate: egen monthly_state_total_unemployed = total(empstat == 1)
gen monthly_state_unemployment_rate = monthly_state_total_unemployed/monthly_state_labor_force

gen byte has_withdrawn_from_FPUC = (date > fpuc2_end) if !missing(date)

by has_withdrawn_from_FPUC mdate, sort: egen monthly_group_labor_force = total(inlist(empstat, 0, 1))
by has_withdrawn_from_FPUC mdate: egen monthly_group_total_unemployed = total(empstat == 1)
gen monthly_group_unemployment_rate = monthly_group_total_unemployed/monthly_group_labor_force

Due to the limitations of the example data shown, this code is not truly tested. If it does not do what you are looking for, please post back with a better example data set and a clear explanation of the variables that are relevant to the calculations desired.