SPSSX Discussion

Can I loop through a CTABLE command for mulitple similar survey questions?

Classic

List

Threaded

9 messages Options

William Peck

Can I loop through a CTABLE command for mulitple similar survey questions?

I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a
CTABLES
/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES
/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

end loop

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

spss.giesel@yahoo.de

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

Hi, William,

maybe something like this?

-----------------------------------------

/* Macro Definition */
DEFINE @tab(!POSITIONAL !CMDEND)
!DO !i !IN (!1)
CTABLES
  /VLABELS VARIABLES= !i  Academic_Division DISPLAY=LABEL
  /TABLE !i [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
  /CATEGORIES VARIABLES=!i ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
  /CRITERIA CILEVEL=95.
!DOEND
!ENDDEFINE.

/* Macro Call */
@tab q1_a q1_b q1_c q1_d.

-----------------------------------------
Mario Giesel
Munich, Germany

Am Dienstag, 2. April 2019, 17:08:12 MESZ hat William Peck <[hidden email]> Folgendes geschrieben:

I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a

CTABLES

/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL

/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]

/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE

/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE

/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES

/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL

/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]

/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE

/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE

/CRITERIA CILEVEL=95.

end loop

=====================

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

William Peck

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

whoa, that looks awesome, I'll give it a try. Thanks!

On Tue, Apr 2, 2019 3:26 PM, Mario Giesel [hidden email] wrote:

Hi, William,

maybe something like this?

-----------------------------------------

/* Macro Definition */
DEFINE @tab(!POSITIONAL !CMDEND)
!DO !i !IN (!1)
CTABLES
/VLABELS VARIABLES= !i Academic_Division DISPLAY=LABEL
/TABLE !i [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=!i ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.
!DOEND
!ENDDEFINE.

/* Macro Call */
@tab q1_a q1_b q1_c q1_d.

-----------------------------------------
Mario Giesel
Munich, Germany

Am Dienstag, 2. April 2019, 17:08:12 MESZ hat William Peck <[hidden email]> Folgendes geschrieben:

I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a
CTABLES
/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES
/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

end loop

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

Bill Peck

Information Technology Services Division

Institutional Research / Business Intelligence

United States Naval Academy

410-293-1475 (w)

410-207-3350 (c)

**** Data is your friend ===> at home, at work, and on the battlefield ****

**** Data is the fuel of your organization ****

David Marso

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

Administrator

In reply to this post by spss.giesel@yahoo.de

Note: While not required, it is sound practice to begin macro names with a !
so as to avoid collisions with variable names and SPSS procedures.

Mario Giesel-2 wrote

> Hi, William,
> maybe something like this?
> -----------------------------------------
>
> /* Macro Definition */.
> DEFINE !tab(!POSITIONAL !CMDEND)
> !DO !var !IN (!1)
> CTABLES
> /VLABELS VARIABLES= !var Academic_Division DISPLAY=LABEL
> /TABLE !var [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT
> PCT40.0]
> /CATEGORIES VARIABLES=!var ORDER=A KEY=VALUE EMPTY=INCLUDE
> /CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00]
> EMPTY=INCLUDE
> /CRITERIA CILEVEL=95.
> !DOEND
> !ENDDEFINE.
>
> /* Macro Call */.
> !tab q1_a q1_b q1_c q1_d.
>
> -----------------------------------------
> Mario GieselMunich, Germany
>
> Am Dienstag, 2. April 2019, 17:08:12 MESZ hat William Peck <

> peck@

> > Folgendes geschrieben:
>
> I have 5 blocks of questions, each with the same series of questions. So
> I can write
>
> frequencies q1_a to q5_e and it them prints two frequencies results, so
> that's nice and efficient.
>
> Can I do some sort of programming loop for a CTABLE command?
>
> Here's the command for q1_a
> CTABLES
> /VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
> /TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT
> PCT40.0]
> /CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
> /CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00]
> EMPTY=INCLUDE
> /CRITERIA CILEVEL=95.
>
> currently I have that duplicated and then just modify q1_b, q1_c, and etc.
>
> So was just wondering if I could (easily) do something like
>
> loop q1_a to q5_e for variable $xx
>
> CTABLES
> /VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
> /TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
> /CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
> /CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00]
> EMPTY=INCLUDE
> /CRITERIA CILEVEL=95.
>
> end loop
>
> =====================
> 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
>
> =====================
> 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

-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
--
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

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

William Peck

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

In reply to this post by spss.giesel@yahoo.de

suh-weet!!!! worked great, awesome, thank you!

much appreciated!

On Tue, Apr 2, 2019 3:26 PM, Mario Giesel [hidden email] wrote:

Hi, William,

maybe something like this?

-----------------------------------------

/* Macro Definition */
DEFINE @tab(!POSITIONAL !CMDEND)
!DO !i !IN (!1)
CTABLES
/VLABELS VARIABLES= !i Academic_Division DISPLAY=LABEL
/TABLE !i [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=!i ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.
!DOEND
!ENDDEFINE.

/* Macro Call */
@tab q1_a q1_b q1_c q1_d.

-----------------------------------------
Mario Giesel
Munich, Germany

Am Dienstag, 2. April 2019, 17:08:12 MESZ hat William Peck <[hidden email]> Folgendes geschrieben:

I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a
CTABLES
/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES
/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

end loop

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

Bill Peck

Information Technology Services Division

Institutional Research / Business Intelligence

United States Naval Academy

410-293-1475 (w)

410-207-3350 (c)

**** Data is your friend ===> at home, at work, and on the battlefield ****

**** Data is the fuel of your organization ****

Jon Peck

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

In reply to this post by William Peck

Mario's macro will handle an explicit list of variable names, but it doesn't allow the TO construct. Here is a solution that supports TO as well as an explicit list (or a mixture of the two). The first begin program block defines a function named mytable. It takes a string listing the variable(s) to be tabulated. The following program block using employee data.sav shows usage of this function.

Note that the case of the variable names in the arguments must match the case in the data file. (I deleted a scale variable between gender and educ for this example.)

The email containing the question contained some nonprinting characters, so I retyped the CTABLES syntax here.

begin program.

import spss, spssaux

def mytable(arg):

"""Run cmd for a list of variables, possibly including TO or ALL"""

cmd = """CTABLES

/VLABELS VARIABLES=%(v)s Academic_Division DISPLAY=LABEL

/TABLE %(v)s[C] by Academic_Division[C][COUNT COLPCT.COUNT]

/CATEGORIES VARIABLES=%(v)s ORDER=A KEY=VALUE EMPTY=INCLUDE

/CATEGORIES VARIABLES=Academic_Division [1 2 3 4] EMPTY=INCLUDE."""

args = spssaux.VariableDict().expand(arg.split())

for v in args:

spss.Submit(cmd % locals())

end program.

begin program.

mytable("gender educ")

end program.

begin program.

mytable("gender to educ minority")

end program.

On Tue, Apr 2, 2019 at 9:08 AM William Peck <[hidden email]> wrote:

I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a
CTABLES
/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES
/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

end loop

=====================
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 K Peck
[hidden email]

William Peck

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

Great, Jon, I'll try that out as well. Thanks!

On Tue, Apr 2, 2019 10:03 PM, Jon Peck [hidden email] wrote:

Mario's macro will handle an explicit list of variable names, but it doesn't allow the TO construct. Here is a solution that supports TO as well as an explicit list (or a mixture of the two). The first begin program block defines a function named mytable. It takes a string listing the variable(s) to be tabulated. The following program block using employee data.sav shows usage of this function.
Note that the case of the variable names in the arguments must match the case in the data file. (I deleted a scale variable between gender and educ for this example.)

The email containing the question contained some nonprinting characters, so I retyped the CTABLES syntax here.

begin program.
import spss, spssaux

def mytable(arg):
"""Run cmd for a list of variables, possibly including TO or ALL"""
cmd = """CTABLES
/VLABELS VARIABLES=%(v)s Academic_Division DISPLAY=LABEL
/TABLE %(v)s[C] by Academic_Division[C][COUNT COLPCT.COUNT]
/CATEGORIES VARIABLES=%(v)s ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1 2 3 4] EMPTY=INCLUDE."""

args = spssaux.VariableDict().expand(arg.split())
for v in args:
spss.Submit(cmd % locals())
end program.

begin program.
mytable("gender educ")
end program.

begin program.
mytable("gender to educ minority")
end program.

On Tue, Apr 2, 2019 at 9:08 AM William Peck <[hidden email]> wrote:
I have 5 blocks of questions, each with the same series of questions. So I can write

frequencies q1_a to q5_e and it them prints two frequencies results, so that's nice and efficient.

Can I do some sort of programming loop for a CTABLE command?

Here's the command for q1_a
CTABLES
/VLABELS VARIABLES=q1_a Academic_Division DISPLAY=LABEL
/TABLE q1_a [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=q1_a ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

currently I have that duplicated and then just modify q1_b, q1_c, and etc.

So was just wondering if I could (easily) do something like

loop q1_a to q5_e for variable $xx

CTABLES
/VLABELS VARIABLES=$xx Academic_Division DISPLAY=LABEL
/TABLE xx [C] BY Academic_Division [C][COUNT F40.0 COLPCT.COUNT PCT40.0]
/CATEGORIES VARIABLES=xx ORDER=A KEY=VALUE EMPTY=INCLUDE
/CATEGORIES VARIABLES=Academic_Division [1.00, 2.00, 3.00, 4.00] EMPTY=INCLUDE
/CRITERIA CILEVEL=95.

end loop

=====================
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 K Peck
[hidden email]

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

Bill Peck

Information Technology Services Division

Institutional Research / Business Intelligence

United States Naval Academy

410-293-1475 (w)

410-207-3350 (c)

**** Data is your friend ===> at home, at work, and on the battlefield ****

**** Data is the fuel of your organization ****

John F Hall

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

In reply to this post by Jon Peck

This looks to be a fantastically useful bit of code. I am constructing a similar series of analyses of differences between men and women in earnings (from paid work). They comprise two-way tables of sex (independent row variable) by earnings (dependent column variable) in quartile groups, elaborated in 3- and multi-way tables by one or more test variables. I do these analyses using both CROSSTABS and CTABLES to obtain row percent, but for each CTABLES run I have to manually substitute the test variables (eg working full-time, part-time / public-private sector/ qualifications etc.)

My original CTABLES syntax was modified by Jignesh Sutar

a) to get rid of clutter in the output to make it clearer,

b) to add a column with the base count for each row, and

c) to label the columns: % % % % (n=100%)

Unlike CROSSTABS, CTABLES allows you to display the row totals on the same line as the row percentages (i.e. without the row counts) by requesting TOTALS [COUNT] as an additional element inside the square brackets:

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

This will display the same table as the above. To display the row totals used as a base for percentages, you need an additional line for each /TABLE specification:

/CATEGORIES VARIABLES= earngrp TOTAL=YES .

ctables /TABLE sex BY earngrp [ROWPCT.COUNT TOTALS [COUNT]] /CATEGORIES VARIABLES= earngrp TOTAL=YES

/TABLE workmode BY earngrp [ROWPCT.COUNT TOTALS [COUNT]] /CATEGORIES VARIABLES= earngrp TOTAL=YES .

		earngrp Quartile group of R's earnings from paid work
		1 Q1	2 Q2	3 Q3	4 Q4	Total
		Row N %	Row N %	Row N %	Row N %	Count
Rsex Person 1 SEX	1 Male	13.0%	25.9%	29.0%	32.1%	4299
	2 Female	35.6%	29.3%	21.2%	13.9%	4490

		earngrp Quartile group of R's earnings from paid work
		1 Q1	2 Q2	3 Q3	4 Q4	Total
		Row N %	Row N %	Row N %	Row N %	Count
workmode R working	1 Full time	10.5%	29.9%	31.2%	28.3%	5459
full- or part-time	2 Part time	59.3%	23.8%	10.5%	6.5%	1999
	2 Part time	59.3%	23.8%	10.5%	6.5%	1999

For analytical purposes these tables are far more useful (and far less cluttered) than the output from CROSSTABS and it's much easier to calculate the epsilons.

Although the tables are now much easier to read and interpret, they can be further improved by getting rid of all the % signs in the body of the table and by changing the column headers. The elements in the /TABLES specification can be extended by adding labels enclosed in double primes eg: [ROWPCT.COUNT "%"] and [COUNT "n = 100%"]

The default formats are integer for cell counts and one decimal place for percentages, but if needed the latter can be changed by adding a format eg:

[ROWPCT.COUNT f4.2 ]

However two decimal places for percentages seems a bit pointless: we're trying to reduce the clutter, not add to it!

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 .

		earngrp Quartile group of R's earnings from paid work
		1 Q1	2 Q2	3 Q3	4 Q4	Total
		%	%	%	%	n= 100%
Rsex Person 1 SEX 1	Male	13.0	25.9	29.0	32.1	4299
2	Female	35.6	29.3	21.2	13.9	4490

Epsilon			-22.6	-3.4	+7.8	+18.2

			earngrp Quartile group of R's earnings from paid work
			1 Q1	2 Q2	3 Q3	4 Q4	Total
			%	%	%	%	n= 100%
workmode R working full-	1	Full time	10.5	29.9	31.2	28.3	5459
or part-time	2	Part time	59.3	23.8	10.5	6.5	1999
Epsilon			-48.8	+6.1	+20.7	+21.8

Variables rsex and workmode both have strong effects on earnings. The above tables do not have totals for the earnings groups as it's easier to compare the income groups of men/women and full-time/part-time workers without them (and to calculate the percentage point differences, epsilons).

[NB: The epsilons were produced separately by copying the tables into Excel, performing the calculations, copying the epsilons back into Word, inserting the + signs and changing the colour of positive epsilons to blue] I wonder if CTABLES can produce tables with epsilons?

For elaboration purposes you need to compare these conditional distributions with the original overall distribution to see how they have been partitioned when controlling for test variables.

earngrp Quartile group of R's earnings from paid work
1 Q1	2 Q2	3 Q3	4 Q4
%	%	%	%	n = 100%
24.5	27.6	25.0	22.8	8789

Test variables can be added at any stage.

Model for elaboration exercise
Dependent variable	Y = Earnings	rearngrp	(Quartile groups)
Independent variable	X = Sex	rsex	(Men, Women)
Test variable 1	T1 = Hours of work	workmode	(Part-time, Full-time)

Zero order tables
Model	Table to display (Row variable by Column variable)
1: X → Y	<independent variable> by <dependent variable>	(<indvar> by <testvar>)
2: T1 → Y	<test variable> by <dependent variable>	(<testvar> by <depvar>)

SPSS ctables syntax [General format for two-way tables]

ctables

/TABLE <indvar> BY <depvar> [ROWPCT.COUNT f5.1 "%" totals [count "n= 100%"]] /CATEGORIES VARIABLES= <depvar> TOTAL=YES

The above syntax looks very complex, but all you have to worry about is copy/pasting it from here (or from the Appendix) substituting the variable names, repeating for each table required.

For the zero order tables:

Model	<indvar> by <depvar>	(Row variable by Column variable)
1: X → Y	rsex by earngrp	Sex of respondent by earnings group
2: T1 → Y	workmode by earngrp	R working FT/PT by earnings group

ctables

/TABLE rsex 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 .

		earngrp Quartile group of R's earnings from paid work
		1 Q1	2 Q2	3 Q3	4 Q4	Total
		%	%	%	%	n= 100%
Rsex Person 1 SEX	1 Male	13.0	25.9	29.0	32.1	4299
	2 Female	35.6	29.3	21.2	13.9	4490

		earngrp Quartile group of R's earnings from paid work
		1 Q1	2 Q2	3 Q3	4 Q4	Total
		%	%	%	%	n= 100%
workmode R working full- 1 Full time		10.5	29.9	31.2	28.3	5459
or part-time	2 Part time	59.3	23.8	10.5	6.5	1999

[NB: Unlike CROSSTABS, CTABLES does not display a title for the tables, so for variable labels SPSS settings are best left as Names and Labels. If you don't want to display the numeric values, change the settings for value labels to Labels only.]

The full tutorial is on https://surveyresearch.weebly.com/uploads/2/9/9/8/2998485/3.2.1.4__elaboration_4__income_differences_2009_%E2%80%93_2014_ctables_.pdf

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)

command INFO REFCARD

Jon Peck

Re: Can I loop through a CTABLE command for mulitple similar survey questions?

Since this may be of general use, here is a more refined verision of what I posted yesterday. It uses the SPSSINC PROGRAM extensions command, which can be installed from the Extensions menu in Statistics.

Notes:

cmd = """..."""

can be any sequence of commands enclosed in triple quotes

To refer to the current variable in the cmd syntax, write %(v)s.

As the examples below illustrate, the arguments to the function are one or more variables, including the use of TO and ALL as needed.

Other parameters could be added as needed before the variable list depending on the specific usage.

The function definition could be moved to some utilities module with minor modifications to the code.

begin program.

import spss, spssaux, sys

def mytable():

"""Run cmd for a list of variables, possibly including TO or ALL"""

cmd = """CTABLES

/VLABELS VARIABLES=%(v)s Academic_Division DISPLAY=LABEL

/TABLE %(v)s[C] by Academic_Division[C][COUNT COLPCT.COUNT]

/CATEGORIES VARIABLES=%(v)s ORDER=A KEY=VALUE EMPTY=INCLUDE

/CATEGORIES VARIABLES=Academic_Division [1 2 3 4] EMPTY=INCLUDE."""

vars = spssaux.VariableDict().expand(sys.argv[1:])

for v in vars:

spss.Submit(cmd % locals())

end program.

* variable names must match the case in the dataset.

* Examples...

spssinc program mytable gender educ.

spssinc program mytable gender to educ.

On Thu, Apr 4, 2019 at 11:24 AM John F Hall <[hidden email]> wrote:

This looks to be a fantastically useful bit of code. I am constructing a similar series of analyses of differences between men and women in earnings (from paid work). They comprise two-way tables of sex (independent row variable) by earnings (dependent column variable) in quartile groups, elaborated in 3- and multi-way tables by one or more test variables. I do these analyses using both CROSSTABS and CTABLES to obtain row percent, but for each CTABLES run I have to manually substitute the test variables (eg working full-time, part-time / public-private sector/ qualifications etc.)

My original CTABLES syntax was modified by Jignesh Sutar
a) to get rid of clutter in the output to make it clearer,
b) to add a column with the base count for each row, and
c) to label the columns: % % %   %   (n=100%)

Unlike CROSSTABS, CTABLES allows you to display the row totals on the same line as the row percentages (i.e. without the row counts) by requesting TOTALS [COUNT] as an additional element inside the square brackets:

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

This will display the same table as the above. To display the row totals used as a base for percentages, you need an additional line for each /TABLE specification:

/CATEGORIES VARIABLES= earngrp TOTAL=YES .

ctables            /TABLE sex BY earngrp [ROWPCT.COUNT TOTALS [COUNT]] /CATEGORIES VARIABLES= earngrp TOTAL=YES

/TABLE workmode BY earngrp [ROWPCT.COUNT TOTALS [COUNT]] /CATEGORIES VARIABLES= earngrp TOTAL=YES .

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2
3 Q3
4 Q4
Total

Row N %
Row N %
Row N %
Row N %
Count
Rsex Person 1 SEX
1 Male
13.0%
25.9%
29.0%
32.1%

4299

2 Female
35.6%
29.3%
21.2%
13.9%

4490

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2
3 Q3
4 Q4

Total

Row N %
Row N %
Row N %
Row N %

Count
workmode R working
1 Full time
10.5%
29.9%
31.2%
28.3%

5459
full- or part-time
2 Part time
59.3%
23.8%
10.5%
6.5%

1999

For analytical purposes these tables are far more useful (and far less cluttered) than the output from CROSSTABS and it's much easier to calculate the epsilons.

Although the tables are now much easier to read and interpret, they can be further improved by getting rid of all the % signs in the body of the table and by changing the column headers. The elements in the /TABLES specification can be extended by adding labels enclosed in double primes eg: [ROWPCT.COUNT "%"] and [COUNT "n = 100%"]

The default formats are integer for cell counts and one decimal place for percentages, but if needed the latter can be changed by adding a format eg:

[ROWPCT.COUNT f4.2 ]

However two decimal places for percentages seems a bit pointless: we're trying to reduce the clutter, not add to it!

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 .

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2
3 Q3
4 Q4
Total

%
%
%
%
n= 100%
Rsex Person 1 SEX 1
Male
13.0
25.9
29.0
32.1
4299
2
Female
35.6
29.3
21.2
13.9
4490

Epsilon
-22.6

-3.4
+7.8
+18.2

earngrp Quartile group of R's earnings from paid work

1 Q1

2 Q2
3 Q3
4 Q4

Total

%

%
%
%

n= 100%
workmode R working full-
1
Full time
10.5

29.9
31.2
28.3

5459
or part-time
2
Part time
59.3

23.8
10.5
6.5

1999
Epsilon
-48.8
+6.1
+20.7
+21.8

Variables rsex and workmode both have strong effects on earnings. The above tables do not have totals for the earnings groups as it's easier to compare the income groups of men/women and full-time/part-time workers without them (and to calculate the percentage point differences, epsilons).

[NB: The epsilons were produced separately by copying the tables into Excel, performing the calculations, copying the epsilons back into Word, inserting the + signs and changing the colour of positive epsilons to blue] I wonder if CTABLES can produce tables with epsilons?

For elaboration purposes you need to compare these conditional distributions with the original overall distribution to see how they have been partitioned when controlling for test variables.

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2
3 Q3
4 Q4

%
%
%
%
n = 100%
24.5
27.6
25.0
22.8
8789

Test variables can be added at any stage.

5

Model for elaboration exercise

Dependent variable
Y = Earnings
rearngrp
(Quartile groups)
Independent variable
X = Sex
rsex
(Men, Women)
Test variable 1
T1 = Hours of work
workmode
(Part-time, Full-time)

Zero order tables

Model
Table to display (Row variable by Column variable)
1: X → Y
<independent variable> by <dependent variable>
(<indvar> by <testvar>)
2: T1 → Y
<test variable> by <dependent variable>
(<testvar> by <depvar>)

SPSS ctables syntax [General format for two-way tables]

ctables

/TABLE <indvar> BY <depvar> [ROWPCT.COUNT f5.1 "%" totals [count "n= 100%"]] /CATEGORIES VARIABLES= <depvar> TOTAL=YES

The above syntax looks very complex, but all you have to worry about is copy/pasting it from here (or from the Appendix) substituting the variable names, repeating for each table required.

For the zero order tables:

Model
<indvar> by <depvar>
(Row variable by Column variable)
1: X → Y
rsex by earngrp
Sex of respondent by earnings group
2: T1 → Y
workmode by earngrp
R working FT/PT by earnings group

ctables

/TABLE rsex 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 .

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2
3 Q3
4 Q4
Total

%
%

%
%

n= 100%
Rsex Person 1 SEX
1 Male
13.0
25.9

29.0
32.1

4299

2 Female
35.6
29.3

21.2
13.9

4490

earngrp Quartile group of R's earnings from paid work

1 Q1
2 Q2

3 Q3
4 Q4

Total

%
%

%
%

n= 100%
workmode R working full- 1 Full time
10.5
29.9

31.2
28.3

5459
or part-time
2 Part time
59.3
23.8

10.5
6.5

1999

[NB: Unlike CROSSTABS, CTABLES does not display a title for the tables, so for variable labels SPSS settings are best left as Names and Labels. If you don't want to display the numeric values, change the settings for value labels to Labels only.]

The full tutorial is on https://surveyresearch.weebly.com/uploads/2/9/9/8/2998485/3.2.1.4__elaboration_4__income_differences_2009_%E2%80%93_2014_ctables_.pdf

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)

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 K Peck
[hidden email]