collect all results into a single table using collect

[Debbie Burke]

I am using stata 18.5 for windows

I have the following code. I want to see all the results of the loop into a single stacked table but only get the last looped result.
I've tried multple variation of the coding and cannot figure it out.

collect clear
svyset [pweight=weightvar]

foreach var in var list gender married etc... {

collect _r_b _r_ci: svy: mean hours_worked, over (year_group `var')

collect style cell result [_r_b _r_ci], nformat (%4.1f)
collect layout (`var'#result) (year_group#smdset)
}

collect style head cmdset, title(hide) level(hide)
collect preview

[Jeff Pitblado (StataCorp)]

Thank you for the code, but you make it difficult for us to help you without providing a working example (with data) of what you are actually doing.

Your layout is changed with each variable in your foreach loop, throwing the previous variable out. You need to build a list of the variables you want to include in the row specification of the layout, I would do that before your loop and use the list in the loop specification.

Here is a working example using some publicly available survey data.

clear all

webuse nhanes2l
svyset

unab rowvars : houssiz sex race agegrp

foreach var of local rowvars {
    collect _r_b _r_ci: svy: mean weight, over(highbp `var') 
}

collect style header `rowvars' highbp, title(label)
collect style column, dups(center)
collect style cell result[_r_b _r_ci], nformat(%4.1f)
collect layout ((`rowvars')#result) (highbp)

Here is the resulting table.

. collect layout ((`rowvars')#result) (highbp)

Collection: default
      Rows: (houssiz sex race agegrp)#result
   Columns: highbp
   Table 1: 78 x 2

----------------------------------------------------
                              |  High blood pressure
                              |         0          1
------------------------------+---------------------
Number of people in household |
  1                           |
    Coefficient               |      66.5       73.9
    95% CI                    | 65.4 67.6  72.4 75.3
  2                           |
    Coefficient               |      68.1       76.6
    95% CI                    | 67.2 69.0  75.5 77.7
  3                           |
    Coefficient               |      68.2       79.2
    95% CI                    | 67.0 69.4  77.7 80.7
  4                           |
    Coefficient               |      69.3       80.1
    95% CI                    | 68.2 70.4  78.2 82.0
  5                           |
    Coefficient               |      69.1       80.0
    95% CI                    | 67.7 70.4  77.8 82.1
  6                           |
    Coefficient               |      68.8       81.2
    95% CI                    | 67.0 70.5  78.6 83.9
  7                           |
    Coefficient               |      70.4       79.4
    95% CI                    | 66.8 74.0  74.5 84.3
  8                           |
    Coefficient               |      67.6       81.8
    95% CI                    | 63.0 72.2  74.3 89.3
  9                           |
    Coefficient               |      73.6       90.0
    95% CI                    | 63.8 83.5 79.9 100.1
  10                          |
    Coefficient               |      65.7       83.2
    95% CI                    | 60.2 71.2  69.0 97.4
  11                          |
    Coefficient               |      66.1       64.1
    95% CI                    | 48.4 83.9  48.4 79.8
  12                          |
    Coefficient               |      59.8       93.8
    95% CI                    | 43.1 76.5 81.4 106.1
  13                          |
    Coefficient               |      66.3       82.2
    95% CI                    | 47.2 85.3  69.0 95.3
  14                          |
    Coefficient               |       0.0       92.1
Sex                           |
  Male                        |
    Coefficient               |      75.7       82.6
    95% CI                    | 75.0 76.3  81.9 83.3
  Female                      |
    Coefficient               |      62.7       72.3
    95% CI                    | 62.1 63.3  71.2 73.4
Race                          |
  White                       |
    Coefficient               |      68.3       78.0
    95% CI                    | 67.8 68.8  77.2 78.8
  Black                       |
    Coefficient               |      70.4       81.1
    95% CI                    | 69.0 71.9  79.1 83.1
  Other                       |
    Coefficient               |      62.3       66.8
    95% CI                    | 58.3 66.4  65.2 68.4
Age group                     |
  20–29                       |
    Coefficient               |      66.8       80.0
    95% CI                    | 65.9 67.7  78.2 81.7
  30–39                       |
    Coefficient               |      69.0       82.4
    95% CI                    | 67.9 70.0  80.9 83.9
  40–49                       |
    Coefficient               |      70.6       80.4
    95% CI                    | 69.6 71.6  78.8 81.9
  50–59                       |
    Coefficient               |      69.3       78.1
    95% CI                    | 67.9 70.6  76.5 79.7
  60–69                       |
    Coefficient               |      68.2       74.3
    95% CI                    | 67.2 69.2  73.1 75.4
  70+                         |
    Coefficient               |      65.9       70.9
    95% CI                    | 64.3 67.6  69.5 72.3
----------------------------------------------------

[Debbie Burke]

My apologies for not providing data. It is hidden behind a security wall, so I am not able to share it. Another issue is that the version on Stata I have on my computer is much older than the version I am using behind the wall.
I tried your suggested coding and I get a familiar error: "your layout specification does not uniquely match any items.... I did display "`rowvars'" and it does not return any results. I would have expected it to list the variables I used "gender, married, age_10" to test the code.

[Jeff Pitblado (StataCorp)]

Can you show us the code you ran?

If not, try it with a small handful of variables first. Build your do-file with display checks along the way, like display the rowvars macro after you create it to verify it was properly constructed, look at the output from your foreach loop, make sure you do not have any typos in your collect layout specification.

[Debbie Burke]

Good Monring Jeff,
You are correct. A tiny little typo slipped past me, "results" instead of "result". It is now printing out like your example above. Thank you so much for your guidance. Best.

[Debbie Burke]

.