Help in CTABLES

[Arora, Manoj (IMDLR)]

Hi All,

 

I have generated the following table using PCOMPUTE command in custom tables

 

 

			Total	Zone: ZONE
			Total	WEST	SOUTH	NORTH	EAST
Q7b_1-Ease of filling up the Insurance application/proposal form	Top-2	Column N %	51.1	62.7	37.9	45.0	44.2
Q7b_2-Range of options available for premium payment	Top-2	Column N %	42.8	45.8	32.8	49.1	40.0
Q7b_3-Ability to keep you updated via. SMS on the application processing status	Top-2	Column N %	43.0	51.1	32.0	42.9	30.8

Average of 3 above

 

 

And the syntax is given below

 

CTABLES

  /VLABELS VARIABLES=Q7b_1 Zone DISPLAY=LABEL

  /PCOMPUTE &cat1 = EXPR([1]*0 + [2]*0 + [3]*0 + [4] + [5])

  /PPROPERTIES &cat1 LABEL = "Top-2" FORMAT=COUNT F40.0, COLPCT.COUNT F40.1 HIDESOURCECATS=YES

  /TABLE Q7b_1 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

+ Q7b_2 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

+ Q7b_3 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

BY ct + Zone [C]

  /SLABELS POSITION=Row

  /CATEGORIES VARIABLES=Q7b_1 q7b_2 q7b_3 [1, 2, 3, 4, 5, &cat1, OTHERNM] EMPTY=INCLUDE

  /CATEGORIES VARIABLES=Zone ORDER=A KEY=VALUE EMPTY=INCLUDE.

 

I want to add one row at the end of table which will be average of all Q7b_1 to Q7b_3.

 

Please help in solving the same.

 

Thanks & Regards

Manoj

 

Kantar Disclaimer ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Jon Peck]

I don't think the table displayed was actually generated by the posted syntax.  For example, the table has a preceding total for each variable that is not specified by the syntax, and the statistics do not match.

However, you cannot aggregate across variables with CTABLES, but you can add calculated rows or columns using the STATS TABLE CALC extension command (Utilities > Calculate with pivot table).  Here is an example.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING 

/TARGET FORMULA="(x0+x1+x2)/3" DIMENSION=ROWS LEVEL = -1  LOCATION=-1 REPEATLOC=NO LABEL="average" MODE=AFTER HIDEINPUTS=NO.

This says to add a row after the last row in the table (LOCATION=-1) to the preceding table of type customtable.  The row is labeled "average", and the calculation is given by the FORMULA expression as the average of rows 0, 1, and 2 counting from zero.  The dialog or syntax help gives details on how to write the formula.

If this command is not already installed, you can install it from the Utilities menu (V22 or 23) or the Extensions menu in later versions.

On Tue, Dec 26, 2017 at 4:27 AM, Arora, Manoj (IMDLR) <[hidden email]> wrote:

Hi All,

 

I have generated the following table using PCOMPUTE command in custom tables

 

 

			Total	Zone: ZONE
			Total	WEST	SOUTH	NORTH	EAST
Q7b_1-Ease of filling up the Insurance application/proposal form	Top-2	Column N %	51.1	62.7	37.9	45.0	44.2
Q7b_2-Range of options available for premium payment	Top-2	Column N %	42.8	45.8	32.8	49.1	40.0
Q7b_3-Ability to keep you updated via. SMS on the application processing status	Top-2	Column N %	43.0	51.1	32.0	42.9	30.8

Average of 3 above

 

 

And the syntax is given below

 

CTABLES

  /VLABELS VARIABLES=Q7b_1 Zone DISPLAY=LABEL

  /PCOMPUTE &cat1 = EXPR([1]*0 + [2]*0 + [3]*0 + [4] + [5])

  /PPROPERTIES &cat1 LABEL = "Top-2" FORMAT=COUNT F40.0, COLPCT.COUNT F40.1 HIDESOURCECATS=YES

  /TABLE Q7b_1 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

+ Q7b_2 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

+ Q7b_3 [C][COLPCT.COUNT PCT40.0, TOTALS[COUNT F40.0]]

BY ct + Zone [C]

  /SLABELS POSITION=Row

  /CATEGORIES VARIABLES=Q7b_1 q7b_2 q7b_3 [1, 2, 3, 4, 5, &cat1, OTHERNM] EMPTY=INCLUDE

  /CATEGORIES VARIABLES=Zone ORDER=A KEY=VALUE EMPTY=INCLUDE.

 

I want to add one row at the end of table which will be average of all Q7b_1 to Q7b_3.

 

Please help in solving the same.

 

Thanks & Regards

Manoj

 

Kantar Disclaimer

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD