Search
   >> Home >> Resources & support >> FAQs >> Saving frequencies produced by tabulate

The above question appeared on Statalist. Here is our reply:

Is there any direct way to save into a new variable the frequencies obtained by applying the command tabulate?

Title   Saving frequencies produced by tabulate
Author William Sribney, StataCorp
Date March 1997; minor revisions September 2005

There are three ways to put frequencies in a new variable:

  1. tabulate oneway or tabulate twoway will save counts as a matrix. svmat can be used to create variables from the matrix.
  2. Use the collapse command. You may have to merge the resulting variables back with the original dataset if you want to have them both together. The counts are in a different form than (1)—you may or may not find this desirable.
  3. Use egen ... = count(1), by(...). The counts are in a different form than (1) or (2)—you may or may not find this desirable.

Below are some examples that use two by variables; you can also use only one or more than two.

Example using tabulate

 . sysuse auto, clear
 (1978 Automobile Data)
 
 . tab rep78 foreign, matcell(x)
 
 Repair     | Car type
 Record 1978|  Domestic    Foreign |     Total
 -----------+----------------------+----------
          1 |         2          0 |         2 
          2 |         8          0 |         8 
          3 |        27          3 |        30 
          4 |         9          9 |        18 
          5 |         2          9 |        11 
 -----------+----------------------+----------
      Total |        48         21 |        69 
 
 
 . matrix list x
 
 x[5,2]
     c1  c2
 r1   2   0
 r2   8   0
 r3  27   3
 r4   9   9
 r5   2   9
 
 . svmat x
 
 . list x* in 1/10
 
      +---------+
      | x1   x2 |
      |---------|
   1. |  2    0 |
   2. |  8    0 |
   3. | 27    3 |
   4. |  9    9 |
   5. |  2    9 |
      |---------|
   6. |  .    . |
   7. |  .    . |
   8. |  .    . |
   9. |  .    . |
  10. |  .    . |
      +---------+

Example using collapse

 . sysuse auto, clear
 (1978 Automobile Data)
 
 . gen x = 1
 
 . collapse (count) x, by(rep78 foreign)
 
 . list

      +-----------------------+
      | rep78    foreign    x |
      |-----------------------|
   1. |     1   Domestic    2 |
   2. |     2   Domestic    8 |
   3. |     3   Domestic   27 |
   4. |     3    Foreign    3 |
   5. |     4   Domestic    9 |
      |-----------------------|
   6. |     4    Foreign    9 |
   7. |     5   Domestic    2 |
   8. |     5    Foreign    9 |
   9. |     .   Domestic    4 |
  10. |     .    Foreign    1 |
      +-----------------------+

Notice that cells with 0 frequency are missing. That is,

 rep78   foreign             x
 -----  --------            ---
     1  Domestic             2
     1  Foreign              0  <-- this cell is not in dataset

     2  Domestic             8
     2  Foreign              0  <-- this cell is not in dataset

     3  Domestic            27
     3   Foreign             3

     4  Domestic             9
     4   Foreign             9

     5  Domestic             2
     5   Foreign             9

     .  Domestic             4
     .   Foreign             1

If you want to have the cells with 0 frequences in your dataset, you can do so using the fillin command and a replace.

 . sysuse auto, clear

 . gen x = 1
 
 . collapse (count) x, by(rep78 foreign)
 
 . fillin rep78 foreign
 
 . replace x = 0 if x==.
 (2 real changes made)
 
 . list

      +---------------------------------+
      | rep78    foreign    x   _fillin |
      |---------------------------------|
   1. |     1   Domestic    2         0 |
   2. |     1    Foreign    0         1 |
   3. |     2   Domestic    8         0 |
   4. |     2    Foreign    0         1 |
   5. |     3   Domestic   27         0 |
      |---------------------------------|
   6. |     3    Foreign    3         0 |
   7. |     4   Domestic    9         0 |
   8. |     4    Foreign    9         0 |
   9. |     5   Domestic    2         0 |
  10. |     5    Foreign    9         0 |
      |---------------------------------|
  11. |     .   Domestic    4         0 |
  12. |     .    Foreign    1         0 |
      +---------------------------------+

Example using egen

 . sysuse auto, clear
 (1978 Automobile Data)
 
 . egen x = count(1), by(rep78 foreign)
 
 . list rep78 foreign x

      +-----------------------+
      | rep78    foreign    x |
      |-----------------------|
   1. |     3   Domestic   27 |
   2. |     3   Domestic   27 |
   3. |     .   Domestic    4 |
   4. |     3   Domestic   27 |
   5. |     4   Domestic    9 |
      |-----------------------|
   6. |     3   Domestic   27 |
   7. |     .   Domestic    4 |
   8. |     3   Domestic   27 |
   9. |     3   Domestic   27 |
  10. |     3   Domestic   27 |
      |-----------------------|
  11. |     3   Domestic   27 |
  12. |     2   Domestic    8 |
  13. |     3   Domestic   27 |
  14. |     3   Domestic   27 |
  15. |     4   Domestic    9 |
      |-----------------------|
  16. |     3   Domestic   27 |
  17. |     2   Domestic    8 |
  18. |     2   Domestic    8 |
  19. |     3   Domestic   27 |
  20. |     5   Domestic    2 |
      |-----------------------|
  21. |     2   Domestic    8 |
  22. |     2   Domestic    8 |
  23. |     2   Domestic    8 |
  24. |     4   Domestic    9 |
  25. |     3   Domestic   27 |
      |-----------------------|
  26. |     3   Domestic   27 |
  27. |     3   Domestic   27 |
  28. |     3   Domestic   27 |
  29. |     4   Domestic    9 |
  30. |     4   Domestic    9 |
      |-----------------------|
  31. |     3   Domestic   27 |
  32. |     3   Domestic   27 |
  33. |     4   Domestic    9 |
  34. |     3   Domestic   27 |
  35. |     4   Domestic    9 |
      |-----------------------|
  36. |     3   Domestic   27 |
  37. |     3   Domestic   27 |
  38. |     4   Domestic    9 |
  39. |     3   Domestic   27 |
  40. |     1   Domestic    2 |
      |-----------------------|
  41. |     3   Domestic   27 |
  42. |     3   Domestic   27 |
  43. |     5   Domestic    2 |
  44. |     3   Domestic   27 |
  45. |     .   Domestic    4 |
      |-----------------------|
  46. |     2   Domestic    8 |
  47. |     4   Domestic    9 |
  48. |     1   Domestic    2 |
  49. |     3   Domestic   27 |
  50. |     3   Domestic   27 |
      |-----------------------|
  51. |     .   Domestic    4 |
  52. |     2   Domestic    8 |
  53. |     5    Foreign    9 |
  54. |     3    Foreign    3 |
  55. |     4    Foreign    9 |
      |-----------------------|
  56. |     4    Foreign    9 |
  57. |     5    Foreign    9 |
  58. |     4    Foreign    9 |
  59. |     4    Foreign    9 |
  60. |     3    Foreign    3 |
      |-----------------------|
  61. |     5    Foreign    9 |
  62. |     4    Foreign    9 |
  63. |     4    Foreign    9 |
  64. |     .    Foreign    1 |
  65. |     3    Foreign    3 |
      |-----------------------|
  66. |     5    Foreign    9 |
  67. |     5    Foreign    9 |
  68. |     5    Foreign    9 |
  69. |     5    Foreign    9 |
  70. |     4    Foreign    9 |
      |-----------------------|
  71. |     5    Foreign    9 |
  72. |     4    Foreign    9 |
  73. |     4    Foreign    9 |
  74. |     5    Foreign    9 |
      +-----------------------+
The Stata Blog: Not Elsewhere Classified Find us on Facebook Follow us on Twitter LinkedIn Google+ Watch us on YouTube