deleting rows from CTABLES based on data values
Kylie
I am after some help with what I believe will be a script (though I
could be wrong!). For a current project we will be generating ~300
custom tables with the following structure:
CTABLES
/VLABELS VARIABLES=majorgrp agegroup sex nut1 DISPLAY=DEFAULT
/TABLE majorgrp BY agegroup > sex > nut1 [COLPCT.SUM PCT40.1]
/CATEGORIES VARIABLES=majorgrp ORDER=A KEY=VALUE EMPTY=INCLUDE
TOTAL=YES POSITION=AFTER
/CATEGORIES VARIABLES=agegroup sex ORDER=A KEY=VALUE EMPTY=INCLUDE.
Ie, one row per 'majorgrp', with columns by age and sex, and the data
cells containing column percentages.
We need to be able to delete any rows from the table where all values
are less than 1.5%. The actual row should be deleted (including row
labels), not just the data values cleared from the cells.
My best guess is that this will require a script, but I couldn't find
anything online that was close enough to give me a starting point.
Does anyone have any suggestions or alternative ways to approach this?
Something that could be run over all pivot tables in an output file
would be great.
Thanks,
Kylie.
Bruce Weaver
Hi Kylie. Have you thought about writing the tables to a data set via
OMS, and then using either REPORT or SUMMARIZE to display them? It
would be dead easy to filter out (or delete) the unwanted rows.
--
Bruce Weaver
bwe...@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/Home
"When all else fails, RTFM."
Kylie
> "When all else fails, RTFM."
Hi Bruce,
Yes good point, that would certainly work too. I think with such a
large number of tables though, that could get a bit intensive? A
python solution has been proposed on the SPSSX-L list, and it's
probably about time I bit the bullet and had a play with the python
integration so I'm trying that out first. The idea that it can run
over all the pivot tables simultaneously is certainly appealing too.
Will report back on how I go!
Thanks,
Kylie.
Kylie
The details of the python solution:
The SPSSINC MODIFY TABLES python extension command now has a
customstylefunctions.HideRowBasedOnValues(threshold=xx)
function that can be used to hide rows where all values in that row
are below the threshold value. Besides from some timing issues that
are problematic in version 17, this is working to do what I need. Am
looking forward to testing it out on version 18 in the next few weeks
to see if the timing issues are fully resolved.
Example code:
CTABLES
/TABLE educ BY gender > jobcat [COLPCT.COUNT].
SPSSINC MODIFY TABLES subtype="Custom Table"
SELECT="<<ALL>>" DIMENSION= ROWS LEVEL = -1 PROCESS = ALL
/STYLES APPLYTO=LABELS
CUSTOMFUNCTION="customstylefunctions.HideRowBasedOnValues(threshold=5)".
Cheers,
Kylie.