Combining multiple ci tables to one - or another, more effective solution?

[Patrick Moller]

Hey all

I have a (for many of you) pretty simple problem. It feels like I've tried 100 different things to make it work like I want it to, but I haven't quite nailed the solution as of yet.

I have a data sample I've collected, recording the saliva production of 130 individuals. I've split the 130 individuals into age groups with 10 year intervals (18-29, 30-39 and so on).

Now, I need to make a table - I've solved most of my problem with the table command - with c(freq mean Mean sd Mean min Mean max Mean) but I cant seem to wrap my head around how to display 95% CI as well. I've tried using the ci command instead, which kinda produces what I'm looking for, but instead of putting out one presentable table, it puts out a table for each age group AND for each gender within each age group. Is there any way to combine the two - or should I look at an entirely different command?

I've added my sample and thank you in advance.

No Mean age
1 0.574 40-49
2 0.949 30-39
3 0.596 50-59
4 0.434 30-39
5 0.629 60-69
6 1.993 40-49
7 1.044 40-49
8 0.772 50-59
9 0.449 50-59
10 1.129 40-49
11 0.497 30-39
12 0.166 30-39
13 1.041 18-29
14 1.050 30-39
15 0.717 30-39
16 0.468 18-29
17 0.413 40-49
18 0.523 40-49
19 0.645 18-29
20 0.660 30-39
21 0.491 30-39
22 1.519 18-29
23 0.444 30-39
24 2.390 30-39
25 0.988 18-29
26 1.489 30-39
27 0.213 30-39
28 0.713 60-69
29 0.723 40-49
30 1.029 40-49
31 1.169 30-39
32 1.337 30-39
33 0.791 30-39
34 0.607 30-39
35 1.272 50-59
36 0.989 60-69
37 0.692 50-59
38 0.471 40-49
39 0.787 30-39
40 0.579 18-29
41 0.714 18-29
42 0.691 70-79
43 1.024 70-79
44 0.593 50-59
45 0.646 40-49
46 0.759 18-29
47 0.198 70-79
48 1.043 30-39
49 1.156 18-29
50 1.062 60-69
51 1.114 30-39
52 0.337 60-69
53 0.205 40-49
54 0.331 30-39
55 0.234 50-59
56 0.283 40-49
57 0.579 30-39
58 0.291 40-49
59 0.312 18-29
60 0.330 18-29
61 0.492 50-59
62 1.127 50-59
63 0.165 18-29
64 0.677 18-29
65 0.721 40-49
66 1.445 40-49
67 0.629 40-49
68 0.231 70-79
69 0.871 50-59
70 0.481 60-69
71 1.207 70-79
72 1.154 50-59
73 0.642 70-79
74 0.959 70-79
75 1.889 40-49
76 1.302 40-49
77 0.407 50-59
78 0.548 70-79
79 1.654 70-79
80 1.026 50-59
81 0.846 60-69
82 0.547 60-69
83 0.581 40-49
84 0.708 18-29
85 0.792 18-29
86 1.092 50-59
87 1.133 60-69
88 0.590 50-59
89 0.519 60-69
90 1.045 60-69
91 0.529 60-69
92 1.389 30-39
93 0.968 50-59
94 0.857 60-69
95 0.633 60-69
96 0.390 50-59
97 0.716 60-69
98 0.456 60-69
99 0.804 40-49
100 0.493 60-69
101 0.420 60-69
102 0.679 60-69
103 0.615 50-59
104 1.025 50-59
105 0.726 40-49
106 0.970 40-49
107 0.739 40-49
108 0.847 70-79
109 2.272 18-29
110 1.008 60-69
111 0.408 70-79
112 2.442 18-29
113 1.780 60-69
114 0.620 18-29
115 1.330 18-29
116 0.440 18-29
117 1.102 50-59
118 0.720 40-49
119 0.549 30-39
120 0.365 30-39
121 0.986 70-79
122 0.460 70-79
123 0.432 70-79
124 0.492 70-79
125 1.460 70-79
126 0.565 70-79
127 1.081 70-79
128 0.387 70-79
129 0.484 70-79
130 1.552 70-79

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear*
input int no float mean str5 age
  1  .574 "40-49"
  2  .949 "30-39"
  3  .596 "50-59"
  4  .434 "30-39"
  5  .629 "60-69"
  6 1.993 "40-49"
  7 1.044 "40-49"
  8  .772 "50-59"
  9  .449 "50-59"
 10 1.129 "40-49"
 11  .497 "30-39"
 12  .166 "30-39"
 13 1.041 "18-29"
 14  1.05 "30-39"
 15  .717 "30-39"
 16  .468 "18-29"
 17  .413 "40-49"
 18  .523 "40-49"
 19  .645 "18-29"
 20   .66 "30-39"
 21  .491 "30-39"
 22 1.519 "18-29"
 23  .444 "30-39"
 24  2.39 "30-39"
 25  .988 "18-29"
 26 1.489 "30-39"
 27  .213 "30-39"
 28  .713 "60-69"
 29  .723 "40-49"
 30 1.029 "40-49"
 31 1.169 "30-39"
 32 1.337 "30-39"
 33  .791 "30-39"
 34  .607 "30-39"
 35 1.272 "50-59"
 36  .989 "60-69"
 37  .692 "50-59"
 38  .471 "40-49"
 39  .787 "30-39"
 40  .579 "18-29"
 41  .714 "18-29"
 42  .691 "70-79"
 43 1.024 "70-79"
 44  .593 "50-59"
 45  .646 "40-49"
 46  .759 "18-29"
 47  .198 "70-79"
 48 1.043 "30-39"
 49 1.156 "18-29"
 50 1.062 "60-69"
 51 1.114 "30-39"
 52  .337 "60-69"
 53  .205 "40-49"
 54  .331 "30-39"
 55  .234 "50-59"
 56  .283 "40-49"
 57  .579 "30-39"
 58  .291 "40-49"
 59  .312 "18-29"
 60   .33 "18-29"
 61  .492 "50-59"
 62 1.127 "50-59"
 63  .165 "18-29"
 64  .677 "18-29"
 65  .721 "40-49"
 66 1.445 "40-49"
 67  .629 "40-49"
 68  .231 "70-79"
 69  .871 "50-59"
 70  .481 "60-69"
 71 1.207 "70-79"
 72 1.154 "50-59"
 73  .642 "70-79"
 74  .959 "70-79"
 75 1.889 "40-49"
 76 1.302 "40-49"
 77  .407 "50-59"
 78  .548 "70-79"
 79 1.654 "70-79"
 80 1.026 "50-59"
 81  .846 "60-69"
 82  .547 "60-69"
 83  .581 "40-49"
 84  .708 "18-29"
 85  .792 "18-29"
 86 1.092 "50-59"
 87 1.133 "60-69"
 88   .59 "50-59"
 89  .519 "60-69"
 90 1.045 "60-69"
 91  .529 "60-69"
 92 1.389 "30-39"
 93  .968 "50-59"
 94  .857 "60-69"
 95  .633 "60-69"
 96   .39 "50-59"
 97  .716 "60-69"
 98  .456 "60-69"
 99  .804 "40-49"
100  .493 "60-69"
end

levelsof age, local(ages)

frame create results str5 age_group mean std_err lb ub

foreach a of local ages {
    ci means mean if age == `"`a'"'
    frame post results (`"`a'"') (`r(mean)') (`r(se)') (`r(lb)') (`r(ub)')
}

frame results {
    format mean-ub %4.3f
    isid age_group, sort
    list, noobs clean
}

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Note: This code requires version 16 to run because it relies on frames. If you are running an older version of Stata, the FAQ states that you are supposed to say so in your post. In that case, use a -postfile- instead of a frame. The modifications of the code to do that are straightforward.

[Patrick Moller]

Thank you so much, A solution a bit different than I imagined, but it works!

[Patrick Moller]

Sorry to bother you again.

It seems like I forgot to state the gender in the sample I provided. When using the commands in Stata on my sample, it comes out with at type mismatch. Is there a way to fix that, and sort the different age groups into gender groups as well?

* Example generated by -dataex-. To install: ssc install dataex
clear
input int No byte(Gender age) double Mean
  1 1 3  .574
  2 1 2  .949
  3 1 4  .596
  4 2 2  .434
  5 2 5  .629
  6 1 3 1.993
  7 2 3 1.044
  8 1 4  .772
  9 2 4  .449
 10 1 3 1.129
 11 2 2  .497
 12 1 2  .166
 13 2 1 1.041
 14 2 2  1.05
 15 2 2  .717
 16 1 1  .468
 17 1 3  .413
 18 1 3  .523
 19 2 1  .645
 20 2 2   .66
 21 2 2  .491
 22 1 1 1.519
 23 1 2  .444
 24 1 2  2.39
 25 1 1  .988
 26 1 2 1.489
 27 1 2  .213
 28 1 5  .713
 29 2 3  .723
 30 1 3 1.029
 31 1 2 1.169
 32 1 2 1.337
 33 1 2  .791
 34 1 2  .607
 35 1 4 1.272
 36 1 5  .989
 37 1 4  .692
 38 2 3  .471
 39 2 2  .787
 40 2 1  .579
end

[William Lisowski]

I am guessing that somewhere in the code that you do not show us you have something like

ci means mean if age == `"`a'"' & gender == `"`g'"'

Unlike your age range variable, which is a string, your gender variable is a numeric variable, so the comparison would appropriately be

ci means mean if age == `"`a'"' & gender == `g'

If this guess is wrong, as it it quite likely to be, or if you have further problems, then your question really isn't clear without more detail, or at a minimum it is too difficult to guess at a good answer from what you have shared. Please help us help you. Show your code. Show us what Stata told you. The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output. Following that guidance, copy the commands you ran and their output from Stata's Results window and pasted them into a post using code delimiters [CODE] and [/CODE].

[Clyde Schechter]

In #1 you showed a string variable for age. But in #4 you have a numeric variable there, so that is why you are getting a type mismatch error. (I suspect that in reality your age-group variable is numeric but also has a value label attached to it. In posting #4 when you copied the -dataex- output you probably mistakenly left off the value label code at the end. Anyway, the following code will work with the numeric age variable, and it also further disaggregates the calculations by sex. (I would guess you also have a value label for the gender variable that you did not show here.)

levelsof age, local(ages)

frame create results byte age_group byte gender mean std_err lb ub

levelsof Gender, local(sexes)
foreach s of local sexes {
    foreach a of local ages {
        ci means Mean if age == `a' & Gender == `s'
        frame post results (`a') (`s') (`r(mean)') (`r(se)') (`r(lb)') (`r(ub)')
    }
}

frame results {
    format mean-ub %4.3f
    isid age_group gender, sort
    list, noobs clean
}

Note that if you have value labels for age and gender in your original data, they will not be carried over to the results frame, so you will need to re-create those and apply them to the corresponding variables there.

[Patrick Moller]

I am guessing that somewhere in the code that you do not show us you have something like

ci means mean if age == `"`a'"' & gender == `"`g'"'

Unlike your age range variable, which is a string, your gender variable is a numeric variable, so the comparison would appropriately be

ci means mean if age == `"`a'"' & gender == `g'

If this guess is wrong, as it it quite likely to be, or if you have further problems, then your question really isn't clear without more detail, or at a minimum it is too difficult to guess at a good answer from what you have shared. Please help us help you. Show your code. Show us what Stata told you. The Statalist FAQ provides advice on effectively posing your questions, posting data, and sharing Stata output. Following that guidance, copy the commands you ran and their output from Stata's Results window and pasted them into a post using code delimiters [CODE] and [/CODE].

Hi and thank you for your time.

1) I imported the xls-file


2)

format %04.3f Mean

label define Gender 1 "F" 2 "M"
label values Gender Gender

label define Disease 1 "No" 2 "Yes"
label values SGD Disease


*Age groups leaving out patients with SGD*
recode Age (18/29=1 "18-29") (30/39=2 "30-39") (40/49=3 "40-49") (50/59=4 "50-59") (60/69=5 "60-69") (70/79=6 "70-79") if SGD==1, generate(age)
label variable age "Age Group"

That leaves me with the next thing, which is the table with the contents as described above. I've tried table, tabulate, list, ci and summarize. All of them do something I want, but none of them do the exact thing I need.

[Clyde Schechter]

So it is exactly as I suspected in #6. You have value-labeled variables. In #1, because you did not use -dataex- to show the example, you create the false impression that you had a string variable for the age group. When you show your data incorrectly, don't be surprised that you get incorrect code back. The code in #6 works with the numeric variables--but it doesn't have the labels because you didn't show them in #4. (Either you ran -dataex- before those -label values- commands were run, or when you copied the -datae- output into the Forum editor you left off the -label- commands at the end.)

Here is code, including complete and correct -dataex- output for your example data, properly labeled. The only omission is that it doesn't do anything with the variable SGD because you have never shown any example data with that variable. And I believe this code contains all the statistics you are looking for.

* Example generated by -dataex-. To install: ssc install dataex
clear*
input int No byte Gender double Mean byte age
 1 1  .574 3
 2 1  .949 2
 3 1  .596 4
 4 2  .434 2
 5 2  .629 5
 6 1 1.993 3
 7 2 1.044 3
 8 1  .772 4
 9 2  .449 4
10 1 1.129 3
11 2  .497 2
12 1  .166 2
13 2 1.041 1
14 2  1.05 2
15 2  .717 2
16 1  .468 1
17 1  .413 3
18 1  .523 3
19 2  .645 1
20 2   .66 2
21 2  .491 2
22 1 1.519 1
23 1  .444 2
24 1  2.39 2
25 1  .988 1
26 1 1.489 2
27 1  .213 2
28 1  .713 5
29 2  .723 3
30 1 1.029 3
31 1 1.169 2
32 1 1.337 2
33 1  .791 2
34 1  .607 2
35 1 1.272 4
36 1  .989 5
37 1  .692 4
38 2  .471 3
39 2  .787 2
40 2  .579 1
end
label values Gender Gender
label def Gender 1 "F", modify
label def Gender 2 "M", modify
label values age age2
label def age2 1 "18-29", modify
label def age2 2 "30-39", modify
label def age2 3 "40-49", modify
label def age2 4 "50-59", modify
label def age2 5 "60-69", modify


levelsof age, local(ages)

frame create results byte age_group byte gender mean std_err lb ub min max

levelsof Gender, local(sexes)
foreach s of local sexes {
    foreach a of local ages {
        ci means Mean if age == `a' & Gender == `s'
        local topost (`a') (`s') (`r(mean)') (`r(se)') (`r(lb)') (`r(ub)')
        summ Mean if age == `a' & Gender == `s', meanonly
        local topost `topost' (`r(min)') (`r(max)')
        frame post results `topost'
    }
}

frame results {
    format mean-max %4.3f
    label values gender Gender
    label def Gender 1 "F", modify
    label def Gender 2 "M", modify
    label values age_group age2
    label def age2 1 "18-29", modify
    label def age2 2 "30-39", modify
    label def age2 3 "40-49", modify
    label def age2 4 "50-59", modify
    label def age2 5 "60-69", modify
    isid age_group gender, sort
    list, noobs clean
}

[Patrick Moller]

I apologize for the troubles and thank you for your time. You solved in 30 minutes, what I've spent 4 evenings trying to figure out.