Report zeroes instead of missings in table output

[Sergiy Radyakin]

Dear All,

please, consider the following example:.

clear all
version 15
input x y s
1 1 100
1 2 120
1 3 140
2 1 50
2 3 100
3 2 100
3 3 170
4 1 50
6 2 70
6 3 100
end
table x y, c(sum s) row col missing

which produces:

--------------------------------------
          |             y             
        x |     1      2      3  Total
----------+---------------------------
        1 |   100    120    140    360
        2 |    50      .    100    150
        3 |     .    100    170    270
        4 |    50      .      .     50
        6 |     .     70    100    170
          | 
    Total |   200    290    510   1000
--------------------------------------

1. Due to the nature of the table I'd much rather see zeroes instead of dots in the output, for example for (x=6;y=1) and other pairs not mentioned in the input. Is there any way to convince standard table command to produce such an output?
2. It is also desirable to have table understand that line for x=5 is all missing, and have it will all zeroes, including the total. Is there any way to again convince table to maintain continuity? or adding zeroes into the data to fill in the gaps is the only way?

Thank you, Sergiy

[Andrew Musau]

I do not know much about Version 17+ table command, but I am relatively confident that you cannot do this without manipulating the data in previous versions. For example, the values of "x" could be anything, only you know that they are continuous in the interval 1-6. I think you know how to manipulate the data, but here is how I would do it.

clear all
version 15
input x y s
1 1 100
1 2 120
1 3 140
2 1 50
2 3 100
3 2 100
3 3 170
4 1 50
6 2 70
6 3 100
end
table x y, c(sum s) row col missing

frame put *, into(table)
frame table{
     xtset y x
     tsfill, full
     xtset x y
     tsfill, full
     mvencode s, mv(0)
     table x y, c(sum s) row col
}
frame drop table

Res.:

. table x y, c(sum s) row col missing

--------------------------------------
          |             y            
        x |     1      2      3  Total
----------+---------------------------
        1 |   100    120    140    360
        2 |    50      .    100    150
        3 |     .    100    170    270
        4 |    50      .      .     50
        6 |     .     70    100    170
          |
    Total |   200    290    510   1000
--------------------------------------

.
.
.
. frame put *, into(table)

.
. frame table{
.
.      xtset y x
       panel variable:  y (unbalanced)
        time variable:  x, 1 to 6, but with gaps
                delta:  1 unit
.
.      tsfill, full
.
.      xtset x y
       panel variable:  x (strongly balanced)
        time variable:  y, 1 to 3
                delta:  1 unit
.
.      tsfill, full
.
.      mvencode s, mv(0)
           s: 8 missing values recoded
.
.      table x y, c(sum s) row col

--------------------------------------
          |             y            
        x |     1      2      3  Total
----------+---------------------------
        1 |   100    120    140    360
        2 |    50      0    100    150
        3 |     0    100    170    270
        4 |    50      0      0     50
        5 |     0      0      0      0
        6 |     0     70    100    170
          |
    Total |   200    290    510   1000
--------------------------------------
.
. }

[Sergiy Radyakin]

I am relatively confident that you cannot do this without manipulating the data in previous versions

Thank you very much, Andrew. This confirmation helps.
And thank you for taking time to code the data filling. This is the step I was hoping to avoid.

Best, Sergiy