Can I define the cell contents of a table (or matrix)?

[JR Funk]

New here, new to Stata (v17)

I'm trying to create a table to later be exported to Excel. I would like the contents of each cell to be the value of a variable I have created called lfp_rate. Each row would represent one month, and the columns are category variables such as gender and age. Each record in my (input) dataset represents one person so I am calculating the lfp_rate for various groups each month. So far, it seems that Stata can only display its own statistics in the table contents, such as frequency or percentages. Is there a way for me to create a table or matrix where I am able to define the contents of the cells? Advice is much appreciated!

[Mike Lacy]

Welcome to StataList. Per the StataList FAQ (button at the upper left of the page) recommended to new members, providing an example of your data (real or fictionalized) using the -dataex- command described in the FAQ will greatly increase your chance of getting a good answer quickly. I'd encourage you to provide an example like that here.

[JR Funk]

Thank you for your response!

Dataex of the full dataset resulted in an error due to the number of variables (388) so I have limited to the ones I am using:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte HRMONTH int HRYEAR4 byte(prtage pesex peeduca) long pwcmpwgt
6 2022 80 1 34 34535536
6 2022 28 1 39 62898977
6 2022 36 1 37 42138911
6 2022 21 1 39 36426625
6 2022 54 1 39  5915492
6 2022 24 1 40 69548063
6 2022 45 1 44 35343361
6 2022 54 1 36 15933610
6 2022 68 1 39  6696708
6 2022 27 1 39 38134438
end

I am creating a number of variables to use in calculating lfp_rate:

//combine month and year into one variable
egen mth_yr = concat(HRMONTH HRYEAR4)


//create variables to identify the civilian prime age population
gen primeage=(prtage<=54 & prtage>=25)
gen civprimeage=(primeage==1 & prpertyp==2)

//create variables to identify population participating in the labor force
gen plf=(pemlr<=4 & pemlr>=1)
gen civprimeplf=(civprimeage==1 & plf==1)

//create variable to segment civilian prime age population by age group
gen age_range=0
replace age_range=1 if prtage>=25 & prtage<35
replace age_range=2 if prtage>=35 & prtage<45
replace age_range=3 if prtage>=45 & prtage<=54

bysort pesex: tab plf if civprimeage==1

//create variable to segment civilian prime age population by educational attainment
gen educ_range=0
replace educ_range=1 if peeduca<=39
replace educ_range=2 if peeduca>=40 & peeduca<=42
replace educ_range=3 if peeduca>=43

//weight variables for BLS comparisons
gen w_civprimeplf=(civprimeplf*pwcmpwgt/10000)
gen w_civprimeage=(civprimeage*pwcmpwgt/10000)
sum w_civprimeplf w_civprimeage

//OPTION 1 calculate labor force participation rates by month by gender
bysort mth_yr pesex: egen lfp_rate_num = total(w_civprimeplf) if civprimeage==1
bysort mth_yr pesex: egen lfp_rate_den = total(w_civprimeage) if civprimeage==1
bysort mth_yr pesex: gen lfp_rate = (lfp_rate_num/lfp_rate_den)

bysort mth_yr pesex: tab lfp_rate

end of code sample

While the "bysort ... tab" accurately calculates the lfp_rate for the groups I have indicated, it gives me a number of small tables with the lfp_rate in the upper left corner and frequencies in the cells. Since my data spans several years, this option won't work for more than a month or two. I am very thankful for this forum and any suggestions anyone can provide.

[Hemanshu Kumar]

The code gives an error

prpertyp not found

Could you please include that variable, and re-run the code to check that we can, too?

[JR Funk]

My apologies!

* Example generated by -dataex-. For more info, type help dataex
clear
input byte HRMONTH int HRYEAR4 byte(prtage pesex peeduca prpertyp pemlr) long pwcmpwgt
6 2022 80 1 34 2 5 34535536
6 2022 28 1 39 2 1 62898977
6 2022 36 1 37 2 1 42138911
6 2022 21 1 39 2 1 36426625
6 2022 54 1 39 2 5  5915492
6 2022 24 1 40 2 1 69548063
6 2022 45 1 44 2 1 35343361
6 2022 54 1 36 2 1 15933610
6 2022 68 1 39 2 1  6696708
6 2022 27 1 39 2 1 38134438
end

[Hemanshu Kumar]

I've made some minor changes to your code, but the critical part is just the two lines at the end.

clear
input byte HRMONTH int HRYEAR4 byte(prtage pesex peeduca prpertyp pemlr) long pwcmpwgt
6 2022 80 1 34 2 5 34535536
6 2022 28 1 39 2 1 62898977
6 2022 36 1 37 2 1 42138911
6 2022 21 1 39 2 1 36426625
6 2022 54 1 39 2 5  5915492
6 2022 24 1 40 2 1 69548063
6 2022 45 1 44 2 1 35343361
6 2022 54 1 36 2 1 15933610
6 2022 68 1 39 2 1  6696708
6 2022 27 1 39 2 1 38134438
end

//combine month and year into one variable
gen mth_yr = ym(HRYEAR4,HRMONTH)
format %tm mth_yr

//create variables to identify the civilian prime age population
gen byte primeage = inrange(prtage,25,54)
gen byte civprimeage = (primeage & prpertyp == 2)

//create variables to identify population participating in the labor force
gen byte plf = inrange(pemlr,1,4)
gen byte civprimeplf = (civprimeage & plf)

//create variable to segment civilian prime age population by age group
gen byte age_range =     cond(inrange(prtage,25,34),1, ///
                        cond(inrange(prtage,35,44),2, ///
                        cond(inrange(prtage,45,54),3,0)))


//create variable to segment civilian prime age population by educational attainment
gen byte educ_range =     cond(inrange(peeduca,0,39),1, ///
                        cond(inrange(peeduca,40,42),2,3))
                        

//weight variables for BLS comparisons
gen w_civprimeplf = (civprimeplf*pwcmpwgt/10000)
gen w_civprimeage = (civprimeage*pwcmpwgt/10000)

//OPTION 1 calculate labor force participation rates by month by gender
sort mth_yr pesex
by mth_yr pesex: egen lfp_rate_num = total(w_civprimeplf*civprimeage)
by mth_yr pesex: egen lfp_rate_den = total(w_civprimeage*civprimeage)
gen lfp_rate = (lfp_rate_num/lfp_rate_den)

table (mth_yr) (pesex), stat(mean lfp_rate) nototal
noi collect preview

which produces, for this small data example, the somewhat uninteresting result:

--------------------
         |    pesex
         |         1
---------+----------
mth_yr   |          
  2022m6 |  .9704764
--------------------

Is this along the lines of what you want? You might want to look at it with data that includes multiple values of mth_yr and pesex

[JR Funk]

Wow! That is exactly what I was looking for - I tested your code with two months and genders and it worked perfectly. Thank you, thank you!


------------------------------
| pesex
| 1 2
--------+---------------------
mth_yr |
62022 | .8837356 .7587043
72022 | .88441 .7572285
------------------------------

[Hemanshu Kumar]

Glad that helped. A few things:

• enclosing your code and/or results on this forum in CODE blocks puts it in a fixed width font, which makes it nicer to read.
• you may want to label your pesex variable, and see the way I constructed the mth_yr variable, to get nicer-looking results. In fact, with the mth_yr created that way, you could use nicer display formats. Try this instead of the format I used, for instance:

  format %tmMon_CCYY mth_yr

• The table command also has an nformat option to help format the numbers nicely.
• since you originally mentioned exporting to Excel, you may want to look at

  help collect export

[JR Funk]

Thank you for that advice, and your patience. I think I understand how to use the code blocks now ...

------------------------------
        |         pesex       
        |         1          2
--------+---------------------
mth_yr  |                     
  62022 |  .8837356   .7587043
  72022 |    .88441   .7572285
------------------------------

I will definitely look into your formatting and export suggestions. Again, I appreciate your time and expertise!