SPSS Statistics

SPSS Statistics

Your hub for statistical analysis, data management, and data documentation. Connect, learn, and share with your peers! 

 View Only
  • 1.  Hide empty rows and columns in Pivot tables using syntax

    Posted Thu February 09, 2023 09:23 AM

    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
    ------------------------------


  • 2.  RE: Hide empty rows and columns in Pivot tables using syntax

    Posted Thu February 09, 2023 10:43 AM
    Usually in CTABLES you can suppress the empty rows in the initial table.  Can you say more about your CTABLES code?



    --





  • 3.  RE: Hide empty rows and columns in Pivot tables using syntax

    Posted Thu February 09, 2023 12:08 PM

    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
    ------------------------------



  • 4.  RE: Hide empty rows and columns in Pivot tables using syntax

    Posted Thu February 09, 2023 01:56 PM
    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.
    image.png
    Could there be something weird about your value labels?  Could you send me a small data file that shows this?






  • 5.  RE: Hide empty rows and columns in Pivot tables using syntax

    Posted Fri February 10, 2023 04:48 AM

    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
    ------------------------------



  • 6.  RE: Hide empty rows and columns in Pivot tables using syntax

    Posted Fri February 10, 2023 09:36 AM
    Glad that is solved.  Just as a curiosity, can you send me the custom tablelook that produced that odd result (jkpeck@gmail.com)?   Since the statistic would only take one row, there must be something else wrong with the formatting.

    --