Keep cells with zero observations after collapsing the data

[Trevor Andrew]

My understanding is that stata drops all cells with zero observations when I use collapse (correct me if I'm wrong!) How can I force stata to assign zeros to those cells with no observations. Or how can I create a data set that includes cells with no observations after collapsing the data!

[Nick Cox]

The point of view of collapse is that cross-combinations of variables with zero observations -- correspond to zero observations and so there is nothing to collapse.

contract was written partly to do what I guess you want to do. As the help for collapse advises

Note: See [D] contract if you want to collapse to a dataset of frequencies.

. sysuse auto, clear
(1978 Automobile Data)

. contract foreign rep78, zero

. list, sepby(foreign)

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

contract was first published as collfreq.

STB-44 dm59 . . . . . . . . . . . . . . . Collapsing datasets to frequencies
(help collfreq if installed) . . . . . . . . . . . . . . . N. J. Cox
7/98 pp.2--3; STB Reprints Vol 8, pp.20--21
produces a new dataset consisting of all combinations of
varlist that exist in the data together with a new variable
that contains the frequency of each combination; renamed
contract and incorporated into Stata 6

At the time, as now, the Stata company asked user-programmers to avoid simple English words for their command names. When the command was folded into official Stata, the name contract seemed congenial as the opposite of expand.

I'd also draw your attention to groups (you can see that I haven't always taken the company's requests to heart). There is an overview at https://www.statalist.org/forums/for...updated-on-ssc (which includes mention of a fillin option), formal account at

SJ-17-3 st0496 . . . . . Speaking Stata: Tables as lists: The groups command
(help groups if installed) . . . . . . . . . . . . . . . . N. J. Cox
Q3/17 SJ 17(3):760--773
presents command for listing group frequencies and percents and
cumulations thereof; for various subsetting and ordering by
frequencies, percents, and so on; for reordering of columns;
and for saving tabulated data to new datasets

while software should be downloaded from st0496_1

SJ-18-1 st0496_1 . . . . . . . . . . . . . . . . . Software update for groups
(help groups if installed) . . . . . . . . . . . . . . . . N. J. Cox
Q1/18 SJ 18(1):291
groups exited with an error message if weights were specified;
this has been corrected

Typing

search st0496_1, entry sj

gives a clickable link. 20 years on, I much prefer the name fillin for the option concerned, which in turn prompts mention of the command fillin.

[D] fillin . . . . . . . . . . . . . . . . . . . . Rectangularize dataset
(help fillin)

SJ-5-1 dm0011 . . . . . . . . . . . . . . Stata tip 17: Filling in the gaps
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q1/05 SJ 5(1):135--136 (no commands)
tips for using fillin to fill in gaps in a rectangular
data structure

https://www.stata-journal.com/sjpdf....iclenum=dm0011

[Trevor Andrew]

Thank you very much for the response! It is very helpful! Is the a way to calculate means, counts etc. (like in collapse) of each cell when using contract and still have the cells with zero observations?

[Nick Cox]

It follows from #2 that you should use fillin first and then collapse.

[Trevor Andrew]

That worked for me! Thanks a lot!