SPSS CTABLE syntax

[jagadishpchary]

HI,

I trying to generate a table using the below syntax.


Ctables
/VLABELS VARIABLES=Q3r3_1,Q5r3c1_1 DISPLAY=label
/VLABELS VARIABLES=TOTAL,Q8r7 DISPLAY=none
/TABLE (Q1r1+Q1r2+Q1r3)[S] [SUM] by (Q3r3_1+Q5r3c1_1)
/SLABELS POSITION=ROW VISIBLE=NO
/CATEGORIES VARIABLES=Q3r3_1,Q5r3c1_1  TOTAL=NO
/CATEGORIES VARIABLES=Q1r1,Q1r2,Q1r3 TOTAL=YES POSITION=BEFORE LABEL="Total
number of brands purchase".  

However the grand total are not being displayed in the output. I need to
generate table as specified in the attached excel i.e. Table-2

table.xlsx
<http://spssx-discussion.1045642.n5.nabble.com/file/t340698/table.xlsx>  

Here attached the SPSS data file as well.

data.sav
<http://spssx-discussion.1045642.n5.nabble.com/file/t340698/data.sav>  

Please assist me with the correct code.

Regards,
Jagadish




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[John F Hall-2]

Have a look at this and see if you can modify your syntax to produce something similar..

Unlike CROSSTABS the CTABLES command can display the row totals used (base n) on the same row as the percentages.  This is done by specifying TOTALS [COUNT] as an additional element inside the square brackets.  However, to display totals you need an additional line for each /TABLE specification:

 

ctables    /TABLE sex BY earngrp  [ROWPCT.COUNT TOTALS [COUNT]] .

                        /CATEGORIES VARIABLES =  earngrp TOTAL=YES .

 

Table 21:  Sex by earngrp

		Q918b  Gross earnings of R (if working) [3 groups]
		<£6000	<£12000	£12000+	Total
		Row N %	Row N %	Row N %	Count
Sex of respondent	Men	9.8%	41.4%	48.7%	874
	Women	55.8%	33.7%	10.5%	686

 

ctables    /TABLE workmode BY earngrp  [ROWPCT.COUNT TOTALS [COUNT]]

                        /CATEGORIES VARIABLES=  earngrp TOTAL=YES .

 

Table 22:  Sex by earngrp

		Gross earnings of R (if working) [3 groups]
		<£6000	<£12000	£12000+	Total
		Row N %	Row N %	Row N %	Count
Mode of work	Parttime	86.5%	10.4%	3.0%	297
	Fulltime	16.8%	44.5%	38.7%	1263

 

These tables are now much easier to read and interpret, but they are still slightly cluttered with superfluous information.  They can be further improved by getting rid of all the % signs in the body of the table and by changing the column headers from Row N % to % and from Count to n = 100%.

 

The elements in the /TABLES specification can also be extended by adding a label in double primes:

 

[ROWPCT.COUNT "%"] and [COUNT "n = 100%"]

 

The default formats are integer for cell counts and one decimal place for percentages.

 

ctables

   /TABLE sex BY earngrp  [ROWPCT.COUNT f5.1 "%"  totals [count "n= 100%"]]

             /CATEGORIES VARIABLES=  earngrp TOTAL=YES

   /TABLE workmode BY earngrp  [ROWPCT.COUNT f5.1 "%" totals [count "n= 100%"]]

            /CATEGORIES VARIABLES=  earngrp TOTAL=YES .

 

 

Table 23:  Sex by earngrp

		Gross earnings of R (if working) [3 groups]
		<£6000	<£12000	£12000+	Total
		%	%	%	n= 100%
Sex of respondent	Men	9.8	41.4	48.7	874
	Women	55.8	33.7	10.5	686

 

On Wed, 20 Nov 2019 at 16:26, jagadishpchary <[hidden email]> wrote:

HI,

I trying to generate a table using the below syntax.


Ctables
/VLABELS VARIABLES=Q3r3_1,Q5r3c1_1 DISPLAY=label
/VLABELS VARIABLES=TOTAL,Q8r7 DISPLAY=none
/TABLE (Q1r1+Q1r2+Q1r3)[S] [SUM] by (Q3r3_1+Q5r3c1_1)   
/SLABELS POSITION=ROW VISIBLE=NO
/CATEGORIES VARIABLES=Q3r3_1,Q5r3c1_1  TOTAL=NO
/CATEGORIES VARIABLES=Q1r1,Q1r2,Q1r3 TOTAL=YES POSITION=BEFORE LABEL="Total
number of brands purchase".   

However the grand total are not being displayed in the output. I need to
generate table as specified in the attached excel i.e. Table-2

table.xlsx
<http://spssx-discussion.1045642.n5.nabble.com/file/t340698/table.xlsx> 

Here attached the SPSS data file as well.

data.sav
<http://spssx-discussion.1045642.n5.nabble.com/file/t340698/data.sav> 

Please assist me with the correct code.

Regards,
Jagadish




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[jagadishpchary]

Hi,

I tried to modify my syntax to get the total column at the first row.
however it was unsuccessful.

Also, could you please send me syntax to get the table number at the top of
the pivot table - as given by you in the below table. I need to put the
table name and title in the similar way.

Please help me with the code.

Thanks,
Jagadish




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[PRogman]

My solution would restructure data to make brand purchase 1 variable. Total
would be calculated over that variable. If you want the first data line
colored you can use the SPSS MODIFY TABLES utility.
/PR

DATASET COPY dsCopy.
DATASET ACTIVATE dsCopy WINDOW=ASIS.

VARIABLE LEVEL Q1r1 Q1r2 Q1r3 (SCALE).
VARSTOCASES
  /ID    = id
  /MAKE    Purchase FROM Q1r1 Q1r2 Q1r3
  /INDEX = BrandPurchase(3)
  /KEEP  = Q8r7 TOTAL Q3r3_1 Q5r3c1_1
  /NULL  = KEEP.

VALUE LABELS BrandPurchase
  1'Brand 1 purchase'
  2'Brand 2 purchase'
  3'Brand 3 purchase'
.
CTABLES
  /WEIGHT     VARIABLE  = Purchase
  /VLABELS    VARIABLES = BrandPurchase
              DISPLAY   = NONE
  /VLABELS    VARIABLES = Q3r3_1 Q5r3c1_1
              DISPLAY   = LABEL
  /TABLE      BrandPurchase [C]
           BY Q3r3_1        [C][COUNT F8.0]
            + Q5r3c1_1      [C][COUNT F8.0]
  /SLABELS    VISIBLE   = NO
  /CATEGORIES VARIABLES = BrandPurchase   ORDER=A KEY=VALUE EMPTY=EXCLUDE
TOTAL=YES POSITION=BEFORE
                                          LABEL="Total number of brands
purchase"
  /CATEGORIES VARIABLES = Q3r3_1 Q5r3c1_1 ORDER=A KEY=VALUE EMPTY=INCLUDE
.
SPSSINC MODIFY TABLES
  SUBTABLES = "Custom Table"
  SELECT=0
  DIMENSION= ROWS LEVEL = -1  SIGLEVELS=BOTH
  PROCESS = PRECEDING
  /STYLES  
   APPLYTO=BOTH
   BACKGROUNDCOLOR=0 255 0.



jagadishpchary wrote

> HI,
>
> I trying to generate a table using the below syntax.
>
>
> Ctables
> /VLABELS VARIABLES=Q3r3_1,Q5r3c1_1 DISPLAY=label
> /VLABELS VARIABLES=TOTAL,Q8r7 DISPLAY=none
> /TABLE (Q1r1+Q1r2+Q1r3)[S] [SUM] by (Q3r3_1+Q5r3c1_1)
> /SLABELS POSITION=ROW VISIBLE=NO
> /CATEGORIES VARIABLES=Q3r3_1,Q5r3c1_1  TOTAL=NO
> /CATEGORIES VARIABLES=Q1r1,Q1r2,Q1r3 TOTAL=YES POSITION=BEFORE
> LABEL="Total
> number of brands purchase".  
>
> However the grand total are not being displayed in the output. I need to
> generate table as specified in the attached excel i.e. Table-2
>
> table.xlsx
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/table.xlsx&gt; 
>
> Here attached the SPSS data file as well.
>
> data.sav
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/data.sav&gt; 
>
> Please assist me with the correct code.
>
> Regards,
> Jagadish
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[jagadishpchary]

Hi peder,

Thanks for the reply,

There are many variables in the similar manner.. I can't restructure the data. Can you please help me with the code - with out restructuring the data.

Thanks.

On Thu, Nov 21, 2019, 13:04 PRogman <[hidden email]> wrote:

My solution would restructure data to make brand purchase 1 variable. Total
would be calculated over that variable. If you want the first data line
colored you can use the SPSS MODIFY TABLES utility.
/PR

DATASET COPY dsCopy.
DATASET ACTIVATE dsCopy WINDOW=ASIS.

VARIABLE LEVEL Q1r1 Q1r2 Q1r3 (SCALE).
VARSTOCASES
  /ID    = id
  /MAKE    Purchase FROM Q1r1 Q1r2 Q1r3
  /INDEX = BrandPurchase(3)
  /KEEP  = Q8r7 TOTAL Q3r3_1 Q5r3c1_1
  /NULL  = KEEP.

VALUE LABELS BrandPurchase
  1'Brand 1 purchase'
  2'Brand 2 purchase'
  3'Brand 3 purchase'
.
CTABLES
  /WEIGHT     VARIABLE  = Purchase
  /VLABELS    VARIABLES = BrandPurchase
              DISPLAY   = NONE
  /VLABELS    VARIABLES = Q3r3_1 Q5r3c1_1
              DISPLAY   = LABEL
  /TABLE      BrandPurchase [C]
           BY Q3r3_1        [C][COUNT F8.0]
            + Q5r3c1_1      [C][COUNT F8.0]
  /SLABELS    VISIBLE   = NO
  /CATEGORIES VARIABLES = BrandPurchase   ORDER=A KEY=VALUE EMPTY=EXCLUDE
TOTAL=YES POSITION=BEFORE
                                          LABEL="Total number of brands
purchase"
  /CATEGORIES VARIABLES = Q3r3_1 Q5r3c1_1 ORDER=A KEY=VALUE EMPTY=INCLUDE
.
SPSSINC MODIFY TABLES
  SUBTABLES = "Custom Table"
  SELECT=0
  DIMENSION= ROWS LEVEL = -1  SIGLEVELS=BOTH
  PROCESS = PRECEDING
  /STYLES 
   APPLYTO=BOTH
   BACKGROUNDCOLOR=0 255 0.



jagadishpchary wrote
> HI,
>
> I trying to generate a table using the below syntax.
>
>
> Ctables
> /VLABELS VARIABLES=Q3r3_1,Q5r3c1_1 DISPLAY=label
> /VLABELS VARIABLES=TOTAL,Q8r7 DISPLAY=none
> /TABLE (Q1r1+Q1r2+Q1r3)[S] [SUM] by (Q3r3_1+Q5r3c1_1)
> /SLABELS POSITION=ROW VISIBLE=NO
> /CATEGORIES VARIABLES=Q3r3_1,Q5r3c1_1  TOTAL=NO
> /CATEGORIES VARIABLES=Q1r1,Q1r2,Q1r3 TOTAL=YES POSITION=BEFORE
> LABEL="Total
> number of brands purchase".   
>
> However the grand total are not being displayed in the output. I need to
> generate table as specified in the attached excel i.e. Table-2
>
> table.xlsx
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/table.xlsx&gt; 
>
> Here attached the SPSS data file as well.
>
> data.sav
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/data.sav&gt; 
>
> Please assist me with the correct code.
>
> Regards,
> Jagadish
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[Jon Peck]

I think this could be collapsed into one TABLE CALC command.  Location can be a list of column numbers, and the formula can refer to the current cell as x[0].

On Thu, Nov 21, 2019 at 9:37 AM [hidden email] <[hidden email]> wrote:

Jagadish,

that's easier if you follow the restructuring idea Peder gave you.

Column Percentages are calculated for levels within variables only - not between variables.

The best approach I can give without restructuring is this:

PRESERVE.

SET TVARS = LABELS.

CTABLES

  /VLABELS VARIABLES=Q1r1 Q1r2 Q1r3 Q3r3_1 Q5r3c1_1 DISPLAY=DEFAULT

  /TABLE (Q1SUM + Q1r1 + Q1r2 + Q1r3) [S][SUM '' F40.0] BY Q3r3_1 [C] + Q5r3c1_1 [C].

OUTPUT MODIFY

/SELECT ALL EXCEPT (TABLES)

/DELETEOBJECT DELETE = YES

/SELECT TABLES

/TABLECELLS SELECT=["Total number of brands purchase"] SELECTDIMENSION=ROWS APPLYTO=ROW BACKGROUNDCOLOR=GREEN.

RESTORE.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=ALL

/TARGET FORMULA="(x1*100)/x0" DIMENSION=ROWS LEVEL=-1  LOCATION = 1 REPEATLOC=NO LABEL="COL%" MODE=AFTER HIDEINPUTS=NO

/FORMAT  DECIMALS=2  INVALID="".

STATS TABLE CALC SUBTYPE="customtable" PROCESS=ALL

/TARGET FORMULA="(x3*100)/x0" DIMENSION=ROWS LEVEL=-1  LOCATION = 3 REPEATLOC=NO LABEL="COL%" MODE=AFTER HIDEINPUTS=NO

/FORMAT DECIMALS=2  INVALID="".

STATS TABLE CALC SUBTYPE="customtable" PROCESS=ALL

/TARGET FORMULA="(x5*100)/x0" DIMENSION=ROWS LEVEL=-1  LOCATION = 5 REPEATLOC=NO LABEL="COL%" MODE=AFTER HIDEINPUTS=NO

/FORMAT DECIMALS=2  INVALID="".

Mario Giesel

Munich, Germany

---

Am Donnerstag, 21. November 2019, 15:43:56 MEZ hat jagadish chary <[hidden email]> Folgendes geschrieben:

Hi Mario,

Many thanks for the code.

It is working exactly as i requested. However, I would also like to specify the column% as well . Hence I have specified [SUM '' F40.0, COLPCT.COUNT] but unfortunately the percentages are coming as 100% - not calculating on the 'total number of brand purchase'. Can you please correct the code.

Regards,

Jagadish

On Thu, Nov 21, 2019 at 7:46 PM Mario Giesel <[hidden email]> wrote:

Like so?

* Custom Tables.

COMPUTE Q1SUM = SUM(Q1r1,Q1r2,Q1r3).

FORMATS Q1SUM (F3).

VAR LAB Q1SUM "Total number of brands purchase".

PRESERVE.

SET TVARS = LABELS.

CTABLES

  /VLABELS VARIABLES=Q1r1 Q1r2 Q1r3 Q3r3_1 Q5r3c1_1 DISPLAY=DEFAULT

  /TABLE (Q1SUM + Q1r1 + Q1r2 + Q1r3) [S][SUM '' F40.0] BY Q3r3_1 [C] + Q5r3c1_1 [C].

OUTPUT MODIFY

/SELECT ALL EXCEPT (TABLES)

/DELETEOBJECT DELETE = YES

/SELECT TABLES

/TABLECELLS SELECT=["Total number of brands purchase"] SELECTDIMENSION=ROWS APPLYTO=ROW BACKGROUNDCOLOR=GREEN.

RESTORE.

Mario Giesel

Munich, Germany

Am Donnerstag, 21. November 2019, 08:49:37 MEZ hat jagadish chary <[hidden email]> Folgendes geschrieben:

Hi peder,

Thanks for the reply,

There are many variables in the similar manner.. I can't restructure the data. Can you please help me with the code - with out restructuring the data.

Thanks.

On Thu, Nov 21, 2019, 13:04 PRogman <[hidden email]> wrote:

My solution would restructure data to make brand purchase 1 variable. Total
would be calculated over that variable. If you want the first data line
colored you can use the SPSS MODIFY TABLES utility.
/PR

DATASET COPY dsCopy.
DATASET ACTIVATE dsCopy WINDOW=ASIS.

VARIABLE LEVEL Q1r1 Q1r2 Q1r3 (SCALE).
VARSTOCASES
  /ID    = id
  /MAKE    Purchase FROM Q1r1 Q1r2 Q1r3
  /INDEX = BrandPurchase(3)
  /KEEP  = Q8r7 TOTAL Q3r3_1 Q5r3c1_1
  /NULL  = KEEP.

VALUE LABELS BrandPurchase
  1'Brand 1 purchase'
  2'Brand 2 purchase'
  3'Brand 3 purchase'
.
CTABLES
  /WEIGHT     VARIABLE  = Purchase
  /VLABELS    VARIABLES = BrandPurchase
              DISPLAY   = NONE
  /VLABELS    VARIABLES = Q3r3_1 Q5r3c1_1
              DISPLAY   = LABEL
  /TABLE      BrandPurchase [C]
           BY Q3r3_1        [C][COUNT F8.0]
            + Q5r3c1_1      [C][COUNT F8.0]
  /SLABELS    VISIBLE   = NO
  /CATEGORIES VARIABLES = BrandPurchase   ORDER=A KEY=VALUE EMPTY=EXCLUDE
TOTAL=YES POSITION=BEFORE
                                          LABEL="Total number of brands
purchase"
  /CATEGORIES VARIABLES = Q3r3_1 Q5r3c1_1 ORDER=A KEY=VALUE EMPTY=INCLUDE
.
SPSSINC MODIFY TABLES
  SUBTABLES = "Custom Table"
  SELECT=0
  DIMENSION= ROWS LEVEL = -1  SIGLEVELS=BOTH
  PROCESS = PRECEDING
  /STYLES 
   APPLYTO=BOTH
   BACKGROUNDCOLOR=0 255 0.



jagadishpchary wrote
> HI,
>
> I trying to generate a table using the below syntax.
>
>
> Ctables
> /VLABELS VARIABLES=Q3r3_1,Q5r3c1_1 DISPLAY=label
> /VLABELS VARIABLES=TOTAL,Q8r7 DISPLAY=none
> /TABLE (Q1r1+Q1r2+Q1r3)[S] [SUM] by (Q3r3_1+Q5r3c1_1)
> /SLABELS POSITION=ROW VISIBLE=NO
> /CATEGORIES VARIABLES=Q3r3_1,Q5r3c1_1  TOTAL=NO
> /CATEGORIES VARIABLES=Q1r1,Q1r2,Q1r3 TOTAL=YES POSITION=BEFORE
> LABEL="Total
> number of brands purchase".   
>
> However the grand total are not being displayed in the output. I need to
> generate table as specified in the attached excel i.e. Table-2
>
> table.xlsx
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/table.xlsx&gt; 
>
> Here attached the SPSS data file as well.
>
> data.sav
> &lt;http://spssx-discussion.1045642.n5.nabble.com/file/t340698/data.sav&gt; 
>
> Please assist me with the correct code.
>
> Regards,
> Jagadish
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

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

[jagadishpchary]

Hi,

many thanks for the code.

I trying to use as the restructuring method. However, the base should be
total (numeric number) and not as 100%. could you please let me know how
should i change it.

Thanks,
jagadish



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

[John F Hall]

Jagadish

Why don't you send us a blank example of the table(s) you need, marked up with title, row and column headers?

 

 

	V1	V2	V3	(n=100%)
Total	n	n	n

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

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