saving result output in the same spreadsheet ?

[Raja Dhungana]

Hi,
my datasheet contains 5000 observations with dichotomous variables smoking, alcohol, f_v intake, physical activity, and obesity. I also have age (coded 1-5), education (coded 1-5) and residence (1-3). I want to calculate the percentage of each dichotomous variable by age, education and residence categories and save the percentage in the same spreadsheet under the new variable names. For that, I first calculated the percentage separately and transferred them to the spreadsheet using the following command

input str16 factor male female age1 age2 age3 age4 age5 edu1 edu2 edu3 edu4 edu5 mountain hill tarai
"Cigarette smoking" 8.44 3.43 2.63 6.43 5.41 8.31 10.02 6.46 6.14 5.41 6.57 4.61 5.21 7.23 4.83
"Alcohol consumption" 6.74 3.61 2.97 5.71 4.79 6.85 7.68 6.48 3.85 5.29 5.92 5.56 4.74 6.16 4.06
"Low fruit and vegetable intake" 96.19 94.54 96.65 94.44 95.55 95.11 94.68 93.87 95.19 96.03 95.74 96.17 95.87 94.65 95.69
"Low physical activity" 76.91 80.72 82.80 81.92 75.04 75.61 78.51 89.48 83.31 73.58 69.21 71.38 81.91 76.26 77.05
"Overweight/obesity" 7.04 5.25 10.31 6.01 3.99 5.21 3.31 10.84 4.69 5.49 3.41 9.51 5.56 7.12 3.77
Is there any easy ways (macro or loop) to do that?

[Raja Dhungana]

The percentage is the proportion of the participants responded yes (code 1) of each dichotomous variable.

[Narendra Singh]

Is this possible that you can give few observation of your dataset?

[Raja Dhungana]

I have listed the 20 observations. Please have a look.

[Raja Dhungana]

Without label.

[Phil Bromiley]

You can write the proportions to variables and the labels for the proportions to other variables, and then export the entire data set.

[Raja Dhungana]

@ Phil, no idea. Would you mind helping me write codes?

[Mike Lacy]

Raja: I'd encourage you to take another look at the FAQ about using -dataex- to show example data. Your listing a #5 is not convenient for someone to work with, which reduces the chance that someone will want to help you. (And that situation is made worse by your having posted an image rather than a text listing, which makes it impossible to cut and paste your example and to surgery on it to make it usable. Posting images of text material is quite against your interests.)

In any event, I'm still confused about what you want. I'm guessing you want a data set where each observation contains the percentages of 1s for different categories of individuals, those categories to be defined by combinations of the values of education, gender, etc. If so, the -contract- command should do what you want, about which see -help contract- and look at examples in the PDF help files. Because you have not supplied example data in a usable form, it's not possible to conveniently show you how to use -contract-.

[Raja Dhungana]

Mike Lacy Thank you for your suggestion. Here are the examples of data and the structure (output) that I want in the same spreadsheet generated using dataex.

input double(smoking alcohol) float fv_intake double pa float overweight double(age gender education) long e_belt
0 0 1 1 0 3 1 2 3
0 0 1 1 0 5 1 2 3
0 0 1 1 0 4 1 2 1
0 0 1 1 0 3 1 1 1
1 0 1 0 0 4 1 3 3
0 0 1 1 0 2 1 3 3
0 0 1 1 0 3 1 2 3
0 0 1 1 0 5 1 4 2
0 0 1 1 0 3 1 3 1
0 0 0 1 0 5 1 4 3
0 0 1 0 0 5 1 4 2
0 0 1 1 0 6 1 1 3
1 1 1 0 0 3 1 2 3
0 0 1 1 0 5 1 4 3
0 0 1 1 0 2 1 2 2
0 0 1 0 0 5 1 4 2
0 0 1 1 0 3 1 2 3
0 0 1 1 0 2 1 1 3
1 0 1 1 0 5 1 4 1
0 0 1 1 0 5 1 1 3
0 0 1 1 0 4 1 2 1
0 0 1 1 1 2 1 . 1
0 0 1 0 0 5 1 4 2
1 0 1 1 0 3 1 1 3
0 0 0 1 0 2 1 1 2
0 0 1 1 0 4 1 3 2
0 0 1 1 0 4 1 4 3
0 1 1 1 . 4 1 4 1
1 0 1 1 0 6 1 4 1
0 0 1 0 . 3 1 2 2
1 1 1 0 0 5 1 1 2
1 0 1 1 0 4 1 5 2
0 0 1 1 0 4 1 2 1
0 0 1 0 0 2 1 2 2
0 0 1 0 0 5 1 4 1
0 0 1 0 0 3 1 3 2
1 0 1 0 0 5 1 3 2
0 0 0 1 0 2 1 3 2
0 0 1 1 0 3 1 2 3
0 0 1 1 0 3 1 1 2
0 0 1 1 0 3 1 3 3
0 1 1 0 0 5 1 4 2
0 0 1 0 . 3 1 3 1
0 0 1 1 0 6 1 3 2
0 0 1 1 0 5 1 4 3
0 0 1 1 0 5 1 4 2
0 0 1 1 . 4 1 1 3
0 0 1 0 0 5 1 4 2
0 0 1 1 0 6 1 4 2
0 0 1 0 0 6 1 3 3
0 0 1 1 0 3 1 1 3
0 0 1 1 0 5 1 4 1
0 0 1 1 0 4 1 4 1
0 0 1 1 0 2 1 2 3
0 0 1 1 0 2 1 1 1
0 0 1 0 0 5 1 5 3
1 1 1 0 0 5 1 3 2
0 0 1 0 0 3 1 3 2
0 0 1 1 0 3 1 2 2
0 0 1 1 0 5 1 2 2
0 0 1 1 . 4 1 3 2
1 1 1 . 1 3 1 1 2
. 0 1 0 0 5 1 5 1
0 0 1 1 0 3 1 2 3
0 0 1 1 0 4 1 4 1
0 0 1 1 0 3 1 1 1
0 0 1 1 0 5 1 4 1
0 0 1 1 0 2 1 2 1
0 0 1 1 0 3 1 3 2
0 0 . 1 0 5 1 4 3
0 0 1 0 1 2 1 3 2
0 0 1 1 0 5 1 3 3
0 0 1 0 0 3 1 3 3
0 0 1 1 . 5 1 4 1
1 0 1 1 0 3 1 1 2
0 0 1 1 0 3 1 2 1
0 . 1 1 0 3 1 2 2
0 0 1 0 0 4 1 3 3
0 0 1 1 0 6 1 4 3
0 0 1 1 0 5 1 4 2
0 0 1 0 0 6 1 4 2
0 0 1 1 0 4 1 1 3
0 0 1 1 0 3 1 1 1
0 0 1 1 0 3 1 2 3
0 0 1 1 0 3 1 1 3
0 0 1 0 0 5 1 4 3
0 1 1 1 0 5 1 4 2
0 0 1 1 0 2 1 1 3
0 0 1 1 0 5 1 2 3
0 0 1 1 0 2 1 2 2
0 0 1 1 0 3 1 2 3
0 0 1 1 1 3 1 3 2
0 0 1 1 0 6 1 4 2
0 0 1 1 0 6 1 4 2
0 0 1 0 0 4 1 2 2
1 1 1 1 1 5 1 4 3
0 0 1 0 . 3 1 3 1
0 0 1 0 0 5 1 4 2
0 0 1 1 0 3 1 1 1
0 0 1 1 0 2 1 2 3
end
label values smoking smoking
label def smoking 0 "non-smoker", modify
label def smoking 1 "smokers", modify
label values alcohol alchol
label def alchol 0 "non-alcohol user", modify
label def alchol 1 "alcohol user", modify
label values pa pa
label def pa 0 "physically active", modify
label def pa 1 "less physically active", modify
label values overweight overweight
label def overweight 0 "other than overweight/obesity", modify
label def overweight 1 "overweight/obesity", modify
label values age age
label def age 2 "13 years old", modify
label def age 3 " 14 years old", modify
label def age 4 "15 years old", modify
label def age 5 "16 years old", modify
label def age 6 "17 years and older", modify
label values gender Q2
label def Q2 1 "Male", modify
label values education Q3
label def Q3 1 "Class 7", modify
label def Q3 2 "Class 8", modify
label def Q3 3 "Class 9", modify
label def Q3 4 "Class 10", modify
label def Q3 5 "Class 11", modify
label values e_belt e_belt
label def e_belt 1 "Mountain", modify
label def e_belt 2 "Hill", modify
label def e_belt 3 "Tarai", modify
[/CODE]


input str39 factor float(male female age1 age2 age3 age4 age5 edu1 edu2 edu3 edu4 edu5 mountain hill tarai)
"Cigarette smoking" 8.44 3.43 2.63 6.43 5.41 8.31 10.02 6.46 6.14 5.41 6.57 4.61 5.21 7.23 4.83
"Alcohol consumption" 6.74 3.61 2.97 5.71 4.79 6.85 7.68 6.48 3.85 5.29 5.92 5.56 4.74 6.16 4.06
"Low fruit and vegetable intake" 96.19 94.54 96.65 94.44 95.55 95.11 94.68 93.87 95.19 96.03 95.74 96.17 95.87 94.65 95.69
"Low physical activity" 76.91 80.72 82.8 81.92 75.04 75.61 78.51 89.48 83.31 73.58 69.21 71.38 81.91 76.26 77.05
"Overweight/obesity" 7.04 5.25 10.31 6.01 3.99 5.21 3.31 10.84 4.69 5.49 3.41 9.51 5.56 7.12 3.77
"" . . . . . . . . . . . . . . .
"" . . . . . . . . . . . . . . .
"" . . . . . . . . . . .

[Mike Lacy]

Now that I have a better sense of what your data is like, I see that I was wrong that -contract- would provide a convenient solution to your problem. There's doubtless a way to create the summary listing you want, but I don't see something easy offhand; perhaps someone else will contribute an idea here now that you have provided example data in an easy to use form.

In any event, because Stata is a data analysis program and not a spreadsheet, there will not be a way to have your results results in the same "sheet." A summary data listing such as you want would have to be in a different data file than your raw data.

[David Benson]

Hi Raja,

Thanks for sharing your data through dataex. Let me see if this helps.

*** If when you say you want "percentage of each dichotomous variable by age, education and residence categories" you
mean 2x2 tables then this should work

. tabulate age education

                   |                       education
               age |   Class 7    Class 8    Class 9   Class 10   Class 11 |     Total
-------------------+-------------------------------------------------------+----------
      13 years old |         4          6          3          0          0 |        13
      14 years old |        10         12          9          0          0 |        31
      15 years old |         2          4          4          4          1 |        15
      16 years old |         2          3          3         21          2 |        31
17 years and older |         1          0          2          6          0 |         9
-------------------+-------------------------------------------------------+----------
             Total |        19         25         21         31          3 |        99

. tabulate age education, row

+----------------+
| Key            |
|----------------|
|   frequency    |
| row percentage |
+----------------+

                   |                       education
               age |   Class 7    Class 8    Class 9   Class 10   Class 11 |     Total
-------------------+-------------------------------------------------------+----------
      13 years old |         4          6          3          0          0 |        13
                   |     30.77      46.15      23.08       0.00       0.00 |    100.00
-------------------+-------------------------------------------------------+----------
      14 years old |        10         12          9          0          0 |        31
                   |     32.26      38.71      29.03       0.00       0.00 |    100.00
-------------------+-------------------------------------------------------+----------
      15 years old |         2          4          4          4          1 |        15
                   |     13.33      26.67      26.67      26.67       6.67 |    100.00
-------------------+-------------------------------------------------------+----------
      16 years old |         2          3          3         21          2 |        31
                   |      6.45       9.68       9.68      67.74       6.45 |    100.00
-------------------+-------------------------------------------------------+----------
17 years and older |         1          0          2          6          0 |         9
                   |     11.11       0.00      22.22      66.67       0.00 |    100.00
-------------------+-------------------------------------------------------+----------
             Total |        19         25         21         31          3 |        99
                   |     19.19      25.25      21.21      31.31       3.03 |    100.00

. tabulate age education, row nofreq  // only lists the percentage in each category
// NOTE: be careful with this because it ignores missing obs, and in the 100 obs you shared, some obs are missing info

                   |                       education
               age |   Class 7    Class 8    Class 9   Class 10   Class 11 |     Total
-------------------+-------------------------------------------------------+----------
      13 years old |     30.77      46.15      23.08       0.00       0.00 |    100.00
      14 years old |     32.26      38.71      29.03       0.00       0.00 |    100.00
      15 years old |     13.33      26.67      26.67      26.67       6.67 |    100.00
      16 years old |      6.45       9.68       9.68      67.74       6.45 |    100.00
17 years and older |     11.11       0.00      22.22      66.67       0.00 |    100.00
-------------------+-------------------------------------------------------+----------
             Total |     19.19      25.25      21.21      31.31       3.03 |    100.00


tabulate age education, missing   // use this to find missing

                   |                             education
               age |   Class 7    Class 8    Class 9   Class 10   Class 11          . |     Total
-------------------+------------------------------------------------------------------+----------
      13 years old |         4          6          3          0          0          1 |        14
      14 years old |        10         12          9          0          0          0 |        31
      15 years old |         2          4          4          4          1          0 |        15
      16 years old |         2          3          3         21          2          0 |        31
17 years and older |         1          0          2          6          0          0 |         9
-------------------+------------------------------------------------------------------+----------
             Total |        19         25         21         31          3          1 |       100

. tabulate edu over, missing

           |            overweight
 education | other tha  overweigh          . |     Total
-----------+---------------------------------+----------
   Class 7 |        17          1          1 |        19
   Class 8 |        24          0          1 |        25
   Class 9 |        16          2          3 |        21
  Class 10 |        28          1          2 |        31
  Class 11 |         3          0          0 |         3
         . |         0          1          0 |         1
-----------+---------------------------------+----------
     Total |        88          5          7 |       100


*** If the above is what you want, then run the following loop.  After the loop, right click on the output, select "Copy Table" and then you
can easily paste into a spreadsheet
foreach cat of varlist smoking alcohol fv_intake pa overweight {
    foreach v of varlist age education e_belt {
        tabulate `v' `cat'
        tabulate `v' `cat', row nofreq
    }
}

*** If instead you want the data broken out by more than one category, try the following:
gen id = _n  // creates unique id for each obs.  I just use this so I know it is NOT missing for all the observations

table age e_belt smoking, c(n id) row col scol

---------------------------------------------------------------------------------------------------------------------------------------------
                   |                                                    smoking and e_belt                                                  
                   | ------------- non-smoker -------------   --------------- smokers --------------   ---------------- Total ---------------
               age | Mountain      Hill     Tarai     Total   Mountain      Hill     Tarai     Total   Mountain      Hill     Tarai     Total
-------------------+-------------------------------------------------------------------------------------------------------------------------
      13 years old |        3         6         5        14                                                   3         6         5        14
      14 years old |        8         8        11        27                    2         2         4          8        10        13        31
      15 years old |        6         3         4        13                    1         1         2          6         4         5        15
      16 years old |        4        11        10        25          1         3         1         5          5        14        11        30
17 years and older |                  5         3         8          1                             1          1         5         3         9
                   |
             Total |       21        33        33        87          2         6         4        12         23        39        37        99
---------------------------------------------------------------------------------------------------------------------------------------------


. table age e_belt over, c(n id) row col scol

---------------------------------------------------------------------------------------------------------------------------------------------
                   |                                                  overweight and e_belt                                                  
                   | ---- other than overweight/obesity ---   --------- overweight/obesity ---------   ---------------- Total ---------------
               age | Mountain      Hill     Tarai     Total   Mountain      Hill     Tarai     Total   Mountain      Hill     Tarai     Total
-------------------+-------------------------------------------------------------------------------------------------------------------------
      13 years old |        2         5         5        12          1         1                   2          3         6         5        14
      14 years old |        6         7        13        26                    2                   2          6         9        13        28
      15 years old |        5         3         4        12                                                   5         3         4        12
      16 years old |        5        14        10        29                              1         1          5        14        11        30
17 years and older |        1         5         3         9                                                   1         5         3         9
                   |
             Total |       19        34        35        88          1         3         1         5         20        37        36        93
---------------------------------------------------------------------------------------------------------------------------------------------

. table education e_belt over, c(n id) row col scol

--------------------------------------------------------------------------------------------------------------------------------------
          |                                                   overweight and e_belt                                                  
          | ---- other than overweight/obesity ---    --------- overweight/obesity ---------    ---------------- Total ---------------
education | Mountain      Hill     Tarai     Total    Mountain      Hill     Tarai     Total    Mountain      Hill     Tarai     Total
----------+---------------------------------------------------------------------------------------------------------------------------
  Class 7 |        5         4         8        17                     1                   1           5         5         8        18
  Class 8 |        5         7        12        24                                                     5         7        12        24
  Class 9 |        1         8         7        16                     2                   2           1        10         7        18
 Class 10 |        7        14         7        28                               1         1           7        14         8        29
 Class 11 |        1         1         1         3                                                     1         1         1         3
          |
    Total |       19        34        35        88                     3         1         4          19        37        36        92
--------------------------------------------------------------------------------------------------------------------------------------


*** NOTE: The above gives the numbers in each category (not the percentage), but if you use "Copy Table" and
paste it into a spreadsheet, it is quite easy to create a mimic table with percentages.

*** A loop to run the above for all categories (adjust to suit your needs)
foreach v of varlist smoking alcohol fv_intake pa overweight {
  table age e_belt `v', c(n id) row col scol
  table education e_belt `v', c(n id) row col scol
  }

[David Benson]

You might also look at the user-created command tabout (SSC).

For examples of tabout tables visit the official on-line gallery and consult the User Guide (skip down to chapter 3). A similar Tabout user guide is available at ResearchGate (you may need to register to download it). But it has a number of really nice tables / crosstabs of survey data (& the Stata code to create them). Link

Other things to look at:
Checkout this post on Creating 3-way, 4-way, and 5-way cross-tabs in Stata

See also this post on Stack Overflow

[Raja Dhungana]

David Benson ---thank you for the response.

[Raja Dhungana]

Mike Lacy, thank you for the response.