SPSS: Inconsistent totals due to rounding of numbers

Question

I am using weights when running the data with SPSS custom tables.

Thus it is expected that the column or row values may not add up to row total, column total or Table Total due to rounding of decimals

sample table result:

                                  variable 2
                         category 1       category 2      Total
variable 1   category 1       45             52             97
             category 2       60             56             115
             Total           105            107             211

Is there a way to force SPSS to output the correct row, column, or table totals?

expected table output:

                                  variable 2
                         category 1       category 2      Total
variable 1   category 1       45             52             97
             category 2       60             56             116
             Total           105            108             213

score 2 · Answer 1 · answered Dec 22 '16 at 08:49

2

If you are using the CROSSTABS procedure to produce these figures then you should do using the option ASIS.

answered Dec 22 '16 at 08:49

Jignesh Sutar

2,909
10
13

Hi Jignesh! I am using custom table. – dixi Dec 22 '16 at 14:01
I don't think this should be a problem with `CTABLES`. Please compare results with `CROSSTAB` as a test? – Jignesh Sutar Dec 22 '16 at 15:21
Per checking, it is a problem with ctables - not crosstabs. Crosstab has this /COUNT ROUND CELL. I think that is the reason why the output is correct in crosstab? Anyway, is there something like that in ctables? – dixi Dec 22 '16 at 15:38
What do you mean by "correct"? The totals in CTABLES are computed from the exact, i.e., unformatted values, so they are exact. Then the total display is rounded as specified. You can increase the number of decimals in the statistic format to see that. The calculation is the same as CROSSTABS with the no adjustment setting – JKP Dec 22 '16 at 15:52
by correct, crosstabs output the expected output in my post. ctables return the sample table result in my post. – dixi Dec 22 '16 at 15:59
Could you create a truncated or rounded version of the relevant variables, using `TRUNC` or `RND` and then use these variables for the `CTABLES`? – figurine Dec 22 '16 at 16:33

score 0 · Accepted Answer · edited Dec 23 '16 at 00:15

To be clear: the total displayed by CTABLES is mathematically correct. However, if you want to display as the total the sum of the displayed values in the rows, instead, the only way to do this is by using the STATS TABLE CALC extension command to recompute the totals using the rounded values.

Here is how to do that. First, you need to create a Python module named customcalc.py with the following contents

def custom(datacells, ncells, roworcol):  
    '''Calculate sum of formatted values'''  
    total = sum(float(datacells.GetValueAt(roworcol,i)) for i in range(ncells))  
    return(total)

This file should be saved in the python\lib\site-packages directory under your Statistics installation or anywhere else that Python can find it.

Then, after your CTABLES command, run this syntax

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING  
/TARGET custommodule="customcalc"  
FORMULA="customcalc.custom(datacells, ncells, roworcol)" DIMENSION=COLUMNS LEVEL = -2  LOCATION="Total"   
LABEL="Rounded Count".

That custom function adds up the formatted values in each row instead of the full precision values. If you have suppressed the default statistic name, Count, so that "Total" is the innermost label, use LEVEL=-1 instead of LEVEL=-2 ABOVE.

SPSS: Inconsistent totals due to rounding of numbers

2 Answers2