Hi Jon

Thank you very much for replying. My understanding is that in the Custom Tables syntax, the EMPTY=EXCLUDE option will suppress rows/ columns where categories are empty. By contrast, I want to suppress rows/ columns where they are empty because the statistic is not shown in them. This occurs because I am showing different statistics for the categories vs the totals.

The syntax below is an example of what I'm doing:

CTables
    /VLABELS VARIABLES= var1 var2 DISPLAY=LABEL
    /TABLE Var1 [COLPCT.COUNT PCT40.0,TOTALS[UCOUNT F40.0]]
        BY Var2
    /SLABELS POSITION=ROW VISIBLE=NO
    /CATEGORIES VARIABLES = Var1
        ORDER=A KEY=VALUE EMPTY=EXCLUDE MISSING=EXCLUDE TOTAL=YES LABEL='Unweighted base' POSITION=BEFORE
    /CRITERIA CILEVEL=95.

Where I have the subcommand /SLABELS POSITION=ROW VISIBLE=NO, this pivots the statistics into rows, creating two rows for each category (see table 1 below). One for COLPCTs, which is only shown for categories, and one for UCOUNT, which is only shown for the TOTAL. In the SPSS viewer, this doesn't look so bad. But when I export to Excel, there are visibly blank columns and rows. I can fix this with by right clicking on each table to select Edit, then from the menu, selecting 'Hide empty rows and columns'. However, I can't find an equivalent solution using syntax.

I don't see why you are getting two rows per category, one blank.  I ran what I think is an equivalent table and got only the one expected row (using employee data.sav)..

CTABLES
  /VLABELS VARIABLES=jobcat minority DISPLAY=LABEL
  /TABLE jobcat [COLPCT.COUNT PCT40.1, TOTALS[UCOUNT F40.0]] BY minority
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=jobcat ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=BEFORE
    MISSING=EXCLUDE.

This is the table I got.

Could there be something weird about your value labels?  Could you send me a small data file that shows this?

Hi Jon

Thank you very much for replying. My understanding is that in the Custom Tables syntax, the EMPTY=EXCLUDE option will suppress rows/ columns where categories are empty. By contrast, I want to suppress rows/ columns where they are empty because the statistic is not shown in them. This occurs because I am showing different statistics for the categories vs the totals.

The syntax below is an example of what I'm doing:

CTables
    /VLABELS VARIABLES= var1 var2 DISPLAY=LABEL
    /TABLE Var1 [COLPCT.COUNT PCT40.0,TOTALS[UCOUNT F40.0]]
        BY Var2
    /SLABELS POSITION=ROW VISIBLE=NO
    /CATEGORIES VARIABLES = Var1
        ORDER=A KEY=VALUE EMPTY=EXCLUDE MISSING=EXCLUDE TOTAL=YES LABEL='Unweighted base' POSITION=BEFORE
    /CRITERIA CILEVEL=95.

Where I have the subcommand /SLABELS POSITION=ROW VISIBLE=NO, this pivots the statistics into rows, creating two rows for each category (see table 1 below). One for COLPCTs, which is only shown for categories, and one for UCOUNT, which is only shown for the TOTAL. In the SPSS viewer, this doesn't look so bad. But when I export to Excel, there are visibly blank columns and rows. I can fix this with by right clicking on each table to select Edit, then from the menu, selecting 'Hide empty rows and columns'. However, I can't find an equivalent solution using syntax.

Hi Jon

Thank you so much for looking into this. I ran the exact block as in your message, using employee data.sav and got this table:

However, I then changed the table look (I am using a custom table look) to the 'classic default' and got the following:

So - there is clearly something up with my table look. I suspect when I created the table look the 'hide empty rows and columns' box wasn't checked.

Many thanks for your help.

Best wishes

Doug

I don't see why you are getting two rows per category, one blank.  I ran what I think is an equivalent table and got only the one expected row (using employee data.sav)..

CTABLES
  /VLABELS VARIABLES=jobcat minority DISPLAY=LABEL
  /TABLE jobcat [COLPCT.COUNT PCT40.1, TOTALS[UCOUNT F40.0]] BY minority
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=jobcat ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=BEFORE
    MISSING=EXCLUDE.

This is the table I got.

Could there be something weird about your value labels?  Could you send me a small data file that shows this?

Hi Jon

Thank you very much for replying. My understanding is that in the Custom Tables syntax, the EMPTY=EXCLUDE option will suppress rows/ columns where categories are empty. By contrast, I want to suppress rows/ columns where they are empty because the statistic is not shown in them. This occurs because I am showing different statistics for the categories vs the totals.

The syntax below is an example of what I'm doing:

CTables
    /VLABELS VARIABLES= var1 var2 DISPLAY=LABEL
    /TABLE Var1 [COLPCT.COUNT PCT40.0,TOTALS[UCOUNT F40.0]]
        BY Var2
    /SLABELS POSITION=ROW VISIBLE=NO
    /CATEGORIES VARIABLES = Var1
        ORDER=A KEY=VALUE EMPTY=EXCLUDE MISSING=EXCLUDE TOTAL=YES LABEL='Unweighted base' POSITION=BEFORE
    /CRITERIA CILEVEL=95.

Where I have the subcommand /SLABELS POSITION=ROW VISIBLE=NO, this pivots the statistics into rows, creating two rows for each category (see table 1 below). One for COLPCTs, which is only shown for categories, and one for UCOUNT, which is only shown for the TOTAL. In the SPSS viewer, this doesn't look so bad. But when I export to Excel, there are visibly blank columns and rows. I can fix this with by right clicking on each table to select Edit, then from the menu, selecting 'Hide empty rows and columns'. However, I can't find an equivalent solution using syntax.