Subtotals in ctables

[Mike P-5]

Hi everybody,
I've recently been doing a project with Ctables and as part of the work
I needed to create subtotals (in the example below I have used age for
ease of understanding). The subtotals were the top two values and the
bottom two values of a scale of 1-5. so group 1 1-2, group2 4-5

As Ctables will not allow you to do this I created a very long set of
data manipulation in order to create these subtotals and then create the
tables of them.

My solution involved creating an OMS output of the original table,
having a couple of data flips to create the extra cases (The subtotals)
that I wanted, re-naming and then merging back on to the data set
keeping the original names.

As I say this solution works but has anyone encountered the same
problem? And if so what was there solution to this as my way seems very
very long winded?

Cheers

Mike

Example below of original table and the n the new tables after manips


age             (% of total) (count)    VAlue
<18             20                 30           1
18-24           20                 30           2
25-34           20                 30           3
35-44           20                 30           4
45-54           20                 30           5


age             (% of total) (count)    VAlue
<18             20                 30           1
18-24           20                 30           2
25-34           20                 30           3
35-44           20                 30           4
45-54           20                 30           5

<18-24  40                 60
35-54   40                 60



*OMS.
DATASET DECLARE Ctables.
OMS
 /SELECT TABLES
 /IF COMMANDS = ["CTables"]
     SUBTYPES = ["Custom Table"]
 /DESTINATION FORMAT = SAV NUMBERED = TableNumber_
  OUTFILE = Ctables
  Viewer = NO.

CTABLES
  /VLABELS VARIABLES=age DISPLAY=NONE
  /TABLE age [COLPCT.COUNT '% of Total' PCT40.2,COUNT F40.0]
  /CATEGORIES VARIABLES=age ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES
   TITLE= 'Any title you desire'.
OMSEND.

DATASET ACTIVATE Ctables.
STRING Var99 (A30).
compute VAr99 = var1.
exe.
del var var1.
rename var var99 = var1.


FLIP
 VARIABLES=TableNumber_ Command_ Subtype_ Label_ Var1 ofTotal Count  .
DATASET NAME FCtables.
DATASET ACTIVATE FCtables.
COMPUTE Sub1 = var001 + var002 .
COMPUTE Sub2 = var004 + var005 .
EXECUTE .
FLIP
 VARIABLES=CASE_LBL sub1 sub2  .
DATASET NAME FFCtables.

DEL VAR var1.
exe.
STRING Var1 (A30).
IF (SUBSTR(CASE_LBL,4) = '1') Var1 = 'Negative Subtotal' .
IF (SUBSTR(CASE_LBL,4) = '2') Var1 = 'Positive Subtotal' .
EXECUTE .
DATASET CLOSE FCTABLES.
DATASET ACTIVATE Ctables.
ADD FILES /FILE=*
 /RENAME (Command_ Label_ Subtype_ = d0 d1 d2)
 /FILE='FFCtables'
 /RENAME (CASE_LBL Command_ Label_ Subtype_ = d3 d4 d5 d6)
 /DROP= d0 d1 d2 d3 d4 d5 d6.
EXECUTE.
DATASET CLOSE FFCtables.

COMPUTE Categorys = $CASENUM .
VARIABLE LEVEL Categorys  (NOMINAL).
EXECUTE .
* Create new syntax to label this up.
WRITE OUTFILE = "C:\val_labs.sps"
/ "ADD VALUE LABEL categorys " $CASENUM " '"Var1"'.".
EXE.
INSERT FILE = "C:\val_labs.sps." SYNTAX=INTERACTIVE ERROR=STOP CD=NO.
EXE.

VARIABLE LEVEL ofTotal (Scale).

CTABLES
  /VLABELS VARIABLES=ofTotal Count DISPLAY=DEFAULT
  /VLABELS VARIABLES=Categorys DISPLAY=NONE
  /TABLE Categorys BY ofTotal [MEAN] + Count [S][MEAN]
  /CATEGORIES VARIABLES=Categorys ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES
   TITLE= 'Finished table with subtotals'.

DATASET ACTIVATE DATASET2.
DATASET CLOSE CTABLES.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________