Original Message:
Sent: 2/10/2023 4:48:00 AM
From: Douglas Warren
Subject: RE: Hide empty rows and columns in Pivot tables 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
------------------------------
Douglas Warren
------------------------------
Original Message:
Sent: Thu February 09, 2023 01:55 PM
From: Jon Peck
Subject: Hide empty rows and columns in Pivot tables 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?
Original Message:
Sent: 2/9/2023 12:08:00 PM
From: Douglas Warren
Subject: RE: Hide empty rows and columns in Pivot tables using syntax
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.

------------------------------
Douglas Warren
Original Message:
Sent: Thu February 09, 2023 10:42 AM
From: Jon Peck
Subject: Hide empty rows and columns in Pivot tables using syntax
Usually in CTABLES you can suppress the empty rows in the initial table. Can you say more about your CTABLES code?
--
Original Message:
Sent: 2/9/2023 4:48:00 AM
From: Douglas Warren
Subject: Hide empty rows and columns in Pivot tables using syntax
I am currently pulling together some basic data tables using CUSTOM TABLES syntax. My tables show column percentages for the values and unweighted counts for the totals. To make the table look neater, I have specified that the statistics labels should be positioned in rows, and not be visible. This looks clean, but each value now has two associated rows, with one being blank. I need to get rid of the blank rows.
I can achieve this via the menus by right clicking on a pivot table and selecting Edit. Then, from the General menu there is an option to 'Hide empty rows and columns'. This is exactly what I need to do. However, I need a syntactic equivalent. I've tried playing around with OUTPUT MODIFY and MODIFY TABLES, but can't work out how to do it.
------------------------------
Douglas Warren
------------------------------