SPSSX Discussion

CTABLES for col %

Classic

List

Threaded

7 messages Options

John F Hall

CTABLES for col %

Dominic Abrams (Professor of Social Psychology, Kent) is currently preparing a 'driver review' for the (UK) Government Office of Science on the topic of social attitudes to ageing in the UK. He has asked me for some analysis of the 1981 European Values Survey on which I worked with his grandfather, the late Dr Mark Abrams, many years ago.

Matching up variable names in three different versions of SPSS *.sav files and questionnaires from the same survey from UKDS and GESIS has been difficult, but I have managed to locate the correct variables and produce the following uncluttered tables:

Young people’s trust in older people [a166]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables

/vlabels variables=x003r a166 display=none

/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a166 total=yes position=after.

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

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

Re: CTABLES for col %

Here is an example using employee data.sav. All generated from the Ctables gui, but I deleted redundant syntax.

CTABLES
/TABLE educ [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY jobcat
/SLABELS POSITION=ROW VISIBLE=NO
/CATEGORIES VARIABLES=educ TOTAL=YES LABEL='n = 100%' .

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: John F Hall <[hidden email]>
To: [hidden email]
Date: 11/12/2014 01:50 PM
Subject: [SPSSX-L] CTABLES for col %
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Young people’s trust in older people [a166]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables

/vlabels variables=x003r a166 display=none

/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a166 total=yes position=after.

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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

Re: CTABLES for col %

Jon and Tim sent different solutions (thanks to both):

*Hennigan.

ctables

/table a167 [c] [totals[count,f8.0,"n= 100%"], colpct.count,f8.1,"%"]

by x003r [c]

/categories variables= a167 total=yes LABEL=”n=100%” position=after.

*Peck.

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=x003r TOTAL=YES LABEL='n = 100%' .

They both produce the same table:

		x003r Age recoded
		1 15-24	2 25-34	3 35-44	4 45-54	5 55-64	6 65 and more years	n = 100%
a167 Older peoples trust in young people	1 None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2 2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3 3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4 4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5 5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6 6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7 7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8 8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9 9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	10 A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%

My original syntax was:

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

. . which produces:

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

The table I want needs to look like this:

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
N = 100%	343	230	158	113	134	189

Even better would be a Total column headed All either before or after (as in Valid Percent from frequencies, but with N instead 100%.

Valid Percent
5.8
9.4
16.3
19.8
23.7
9.8
6.2
6.0
1.6
1.4
100.0

All
5.8
9.4
16.3
19.8
23.7
9.8
6.2
6.0
1.6
1.4
1128

Traditional presentation would be:

	15-24	25-34	35-44	45-54	55-64	65 and more years	All
	%	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
9	.0	1.8	2.7	.9	1.5	4.0	1.6
A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
(n = 100%)	343	230	158	113	134	189	1128

. . but most of my underlying pedagogy is based on partitioning a known distribution or statistic into its constituent parts, so my preferred layout would be:

	All	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%	%
None at all	5.8	10.2	4.4	3.4	5.4	2.3	4.0
2	9.4	12.9	10.2	10.7	6.3	6.8	4.5
3	16.3	21.3	20.4	15.4	6.3	9.1	14.1
4	19.8	24.9	20.8	19.5	18.9	12.1	15.3
5	23.7	20.1	22.1	25.5	21.6	29.5	27.7
6	9.8	6.0	9.7	9.4	12.6	16.7	10.7
7	6.2	2.7	5.3	7.4	9.9	11.4	6.8
8	6.0	1.5	5.3	4.0	16.2	6.8	10.2
9	1.6	.0	1.8	2.7	.9	1.5	4.0
A great deal	1.4	.3	.0	2.0	1.8	3.8	2.8
(n = 100%)	1128	343	230	158	113	134	189

I can produce the same table with age groups as the row variable, making comparisons between age groups visually easier, but it gets too wide to display for emails and Word.

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jon K Peck
Sent: 12 November 2014 22:59
To: [hidden email]
Subject: Re: CTABLES for col %

Young people’s trust in older people [a166]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables

/vlabels variables=x003r a166 display=none

/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a166 total=yes position=after.

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

John F Hall

Re: CTABLES for col %

Tim

Which bit of your syntax displays the row total Ns?

I’ve got this far with modifications,

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] [totals [validn,f8.0,"n= 100%"], colpct.count f8.1,"%"]

by x003r [c]

/categories variables= x003r total=yes position=after.

	15-24	25-34	35-44	45-54	55-64	65 +	Total
	%	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
9	.0	1.8	2.7	.9	1.5	4.0	1.6
A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4

. . but also need an extra row at the bottom for the base Ns.

Traditional presentation would be:

	15-24	25-34	35-44	45-54	55-64	65 and more years	All
	%	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
9	.0	1.8	2.7	.9	1.5	4.0	1.6
A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
(n = 100%)	343	230	158	113	134	189	1128

. . but most of my underlying pedagogy is based on partitioning a known distribution or statistic into its constituent parts, so my preferred layout would be:

	All	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%	%
None at all	5.8	10.2	4.4	3.4	5.4	2.3	4.0
2	9.4	12.9	10.2	10.7	6.3	6.8	4.5
3	16.3	21.3	20.4	15.4	6.3	9.1	14.1
4	19.8	24.9	20.8	19.5	18.9	12.1	15.3
5	23.7	20.1	22.1	25.5	21.6	29.5	27.7
6	9.8	6.0	9.7	9.4	12.6	16.7	10.7
7	6.2	2.7	5.3	7.4	9.9	11.4	6.8
8	6.0	1.5	5.3	4.0	16.2	6.8	10.2
9	1.6	.0	1.8	2.7	.9	1.5	4.0
A great deal	1.4	.3	.0	2.0	1.8	3.8	2.8
(n = 100%)	1128	343	230	158	113	134	189

I can produce the same table with age groups as the row variable, making comparisons between age groups visually easier, but it gets too wide to display for emails and Word. Next trick will be to produce overlaid barcharts and/or line graphs. I’ve already produced a nice looking one for three age groups with:

GRAPH /bar (grouped) pct by a167 by x003r2.

Doing it with five age groups is a bit too cluttered, but I’ll see what I can do with line graphs.

John

From: Tim Hennigar [mailto:[hidden email]]
Sent: 13 November 2014 15:38
To: 'John F Hall'
Subject: RE: CTABLES for col %

No uses data from file provided with SPSS from GSS

Thanks!

[hidden email]

(W) 416-947-7807

(H) 905-987-3250

*********************************

Notice: This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, do not use the information, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal. Email transmissions cannot be guaranteed to be secure or error free. The sender therefore does not accept any liability for errors or omissions in the contents of this message that arise as a result of email transmissions.

From: John F Hall [[hidden email]]
Sent: Thursday, November 13, 2014 9:33 AM
To: 'Tim Hennigar'
Subject: RE: CTABLES for col %

Tim

I’m about to modify your syntax for my example and would like to copy our exchange to the list. Is this table confidential? Does it need to be anonymised?

John

From: Tim Hennigar [[hidden email]]
Sent: 13 November 2014 14:03
To: 'John F Hall'
Subject: RE: CTABLES for col %

I don’t know what your are doing exactly but here is my example

CTABLES

/FORMAT EMPTY=BLANK UNIT=POINTS MINCOLWIDTH=45 MAXCOLWIDTH=45

/MRSETS COUNTDUPLICATES=NO

/VLABELS VARIABLES=ALL DISPLAY=NONE

/TABLE sex [C] [TOTALS[UCOUNT,' ',F6.0],COLPCT.VALIDN,' ',PCT5.1]

BY base[C]+$ban1b[C]+$ban1a[C]

/SLABELS POSITION=ROW VISIBLE=YES

/CATEGORIES VAR=sex TOTAL=YES POSITION=AFTER LABEL="n=100%" EMPTY=EXCLUDE MISSING=EXCLUDE

/TITLE

CAPTION="Tim Hennigar"

'SPSS TABLES EXAMPLE'

'Primary Banner (Weighted) Run: Date-)DATE, Time-)TIME'

TITLE=

"(1) Respondent's Sex"

" "

"BASE: ALL RESPONDENTS"

(1) Respondent's Sex

BASE: ALL RESPONDENTS

	Total	Rol3 3-5 (T)	Rol3 2-4 (R)	Rol3 1-3 (Y)	Exct'g (T)	Rutne (H)	Dull (E)	Exctn Rutne (S)	Rutne/ Dull (E)
Male	41.9%	43.9%	39.1%	40.4%	49.1%	39.6%	29.3%	44.0%	38.8%
Female	58.1%	56.1%	60.9%	59.6%	50.9%	60.4%	70.7%	56.0%	61.2%
n=100%	1517	460	608	664	434	505	41	939	546

Tim Hennigar

SPSS TABLES EXAMPLE

Primary Banner (Weighted) Run: Date-11/13/2014, Time-8:00:39 AM

Thanks!

[hidden email]

(W) 416-947-7807

(H) 905-987-3250

*********************************

From: SPSSX(r) Discussion [[hidden email]] On Behalf Of John F Hall
Sent: Thursday, November 13, 2014 4:12 AM
To: [hidden email]
Subject: Re: CTABLES for col %

Jon and Tim sent different solutions (thanks to both):

*Hennigan.

ctables

/table a167 [c] [totals[count,f8.0,"n= 100%"], colpct.count,f8.1,"%"]

by x003r [c]

/categories variables= a167 total=yes LABEL=”n=100%” position=after.

*Peck.

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=x003r TOTAL=YES LABEL='n = 100%' .

They both produce the same table:

		x003r Age recoded
		1 15-24	2 25-34	3 35-44	4 45-54	5 55-64	6 65 and more years	n = 100%
a167 Older peoples trust in young people	1 None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2 2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3 3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4 4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5 5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6 6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7 7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8 8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9 9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	10 A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%

My original syntax was:

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

. . which produces:

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

The table I want needs to look like this:

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
N = 100%	343	230	158	113	134	189

Even better would be a Total column headed All either before or after (as in Valid Percent from frequencies, but with N instead 100%.

Valid Percent
5.8
9.4
16.3
19.8
23.7
9.8
6.2
6.0
1.6
1.4
100.0

All
5.8
9.4
16.3
19.8
23.7
9.8
6.2
6.0
1.6
1.4
1128

Traditional presentation would be:

	15-24	25-34	35-44	45-54	55-64	65 and more years	All
	%	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
9	.0	1.8	2.7	.9	1.5	4.0	1.6
A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
(n = 100%)	343	230	158	113	134	189	1128

. . but most of my underlying pedagogy is based on partitioning a known distribution or statistic into its constituent parts, so my preferred layout would be:

	All	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%	%
None at all	5.8	10.2	4.4	3.4	5.4	2.3	4.0
2	9.4	12.9	10.2	10.7	6.3	6.8	4.5
3	16.3	21.3	20.4	15.4	6.3	9.1	14.1
4	19.8	24.9	20.8	19.5	18.9	12.1	15.3
5	23.7	20.1	22.1	25.5	21.6	29.5	27.7
6	9.8	6.0	9.7	9.4	12.6	16.7	10.7
7	6.2	2.7	5.3	7.4	9.9	11.4	6.8
8	6.0	1.5	5.3	4.0	16.2	6.8	10.2
9	1.6	.0	1.8	2.7	.9	1.5	4.0
A great deal	1.4	.3	.0	2.0	1.8	3.8	2.8
(n = 100%)	1128	343	230	158	113	134	189

I can produce the same table with age groups as the row variable, making comparisons between age groups visually easier, but it gets too wide to display for emails and Word.

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Jon K Peck
Sent: 12 November 2014 22:59
To: [hidden email]
Subject: Re: CTABLES for col %

Young people’s trust in older people [a166]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables

/vlabels variables=x003r a166 display=none

/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a166 total=yes position=after.

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

John F Hall

Re: CTABLES for col %

In reply to this post by Jon K Peck

Jon

The measurement levels in EVS 1981 are haywire: they are all Scale and need to be reset to avoid error messages :

var lev

a165 (nom)

a166 a167

x003r x003r2

(ord).

exec.

The syntax you sent (substituting my variables):

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

does this:

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%
	n = 100%	333	226	149	111	132	177

(Ns for age groups, but no total % for trust values)

. . but I want an extra column headed “Total” or preferably “All” showing % for the whole sample.

I’ve also tried:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=x003r TOTAL=YES LABEL='n = 100%' .

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	n = 100%
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%

( Total % for trust, but no Ns for age groups)

In this table “n = 100%” needs changing to “All” but I can do that myself.

So far I’ve got one table or the or the other, but not both.

That’s what I’ve spent hours trying to do and the nearest I’ve got is:

ctables

/TABLE a167 [c] BY x003r [C] [colPCT.COUNT f8.1 "%" TOTALS[COUNT f8.1 "%"]]

/CATEGORIES VARIABLES= x003r TOTAL=YES POSITION=AFTER LABEL="All"

/CATEGORIES VARIABLES= a167 TOTAL=YES POSITION=AFTER LABEL="n = 100%".

[NB: % suppressed in body of table: new row indicating statistic displayed is %]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
		%	%	%	%	%	%	%
Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	65.0
	2	12.9	10.2	10.7	6.3	6.8	4.5	106.0
	3	21.3	20.4	15.4	6.3	9.1	14.1	184.0
	4	24.9	20.8	19.5	18.9	12.1	15.3	223.0
	5	20.1	22.1	25.5	21.6	29.5	27.7	267.0
	6	6.0	9.7	9.4	12.6	16.7	10.7	111.0
	7	2.7	5.3	7.4	9.9	11.4	6.8	70.0
	8	1.5	5.3	4.0	16.2	6.8	10.2	68.0
	9	.0	1.8	2.7	.9	1.5	4.0	18.0
	A great deal	.3	.0	2.0	1.8	3.8	2.8	16.0
	n = 100%	100.0	100.0	100.0	100.0	100.0	100.0	1128.0

All I need is a tweak to the syntax to yield the base for % within each agegroup (? VALIDN ?) instead of 100.0 in the bottom row, but leaving overall total N as 1128 (not 1128.0).

[Hand-made table]

			Age recoded
			15-24	25-34	35-44	45-54	55-64	65 +	All
			%	%	%	%	%	%	%
	Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
		2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
		3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
		4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
		5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
		6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
		7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
		8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
		9	.0	1.8	2.7	.9	1.5	4.0	1.6
		A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
n = 100%			333	125	0	60	132	177	827

I’ve been going round in circles trying to get that, so far to no avail. I’m beginning to feel like the guy at the coffee machine where each choice is labelled:

Coffee/no cup

Cup/ no coffee

Milk, sugar/ no coffee

etc.

This kind of table is likely to be repeated dozens of times in my tutorials, so it’s worth the effort to get it right . I’d even be prepared to create a tutorial showing how it’s done in GUI, but step-by-step with screenshots at each step. Remember I’m writing for newbies, not professional programmers.

Has anyone thought of an additional facility in CROSSTABS to produce the same table(s)?

cro a167 by x003r

/cel col / coltot validn .

[my colour coding as SPSS doesn’t do it for abbreviated syntax]

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jon K Peck
Sent: 12 November 2014 22:59
To: [hidden email]
Subject: Re: CTABLES for col %

Young people’s trust in older people [a166]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables

/vlabels variables=x003r a166 display=none

/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a166 total=yes position=after.

ctables

/vlabels variables=x003r a167 display=none

/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]

/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

John F Hall

Re: CTABLES for col %

Tim

Very grateful for this.

*Hennigar.

COMPUTE base=1.

VAR LAB base " ".

VAL LAB base 1 "All".

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY base[c] + x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

Et Voila!

[My post-production shading and adjustments to columns widths]

			Age recoded
		All	15-24	25-34	35-44	45-54	55-64	65 +
Older peoples trust in young people	None at all	5.8%	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%
	2	9.4%	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%
	3	16.3%	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%
	4	19.8%	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%
	5	23.7%	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%
	6	9.8%	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%
	7	6.2%	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%
	8	6.0%	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%
	9	1.6%	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%
	A great deal	1.4%	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%
	n = 100%	1128	333	226	149	111	132	177

Alternatively:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

[My post-production shading and adjustments to columns widths]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%
	n = 100%	333	226	149	111	132	177	1128

Just got to get rid of % in body cells

CTABLES

/TABLE a167 [COLPCT.COUNT F5.1 "%", TOTALS[COUNT F5.0 "%" ]] BY x003r + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL="n = 100%" .

[My post-production shading and adjustments to columns widths]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
	2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
	3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
	4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
	5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
	6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
	7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
	8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
	9	.0	1.8	2.7	.9	1.5	4.0	1.6
	A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
	n = 100%	333	226	149	111	132	177	1128

. . and add column header with %: been trying all afternoon without success. Any offers of a final tweak, anyone?

However, a bit of serendipity following an unintentional syntax error:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY base[c] .


		All
		Column N %
Older peoples trust in young people	None at all	5.8%
	2	9.4%
	3	16.3%
	4	19.8%
	5	23.7%
	6	9.8%
	7	6.2%
	8	6.0%
	9	1.6%
	A great deal	1.4%

Can I get N as base? Yes:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]]

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

		Column N %	Count
Older peoples trust in young people	None at all	5.8%
	2	9.4%
	3	16.3%
	4	19.8%
	5	23.7%
	6	9.8%
	7	6.2%
	8	6.0%
	9	1.6%
	A great deal	1.4%
	n = 100%		1128

This is a useful alternative to FREQUENCIES: will now spend the weekend getting N into another row at the bottom of the same column. Hang on a second . .

CTABLES

/TABLE a167 [COLPCT F5.1 "%", TOTALS [COUNT F5.0 ]] + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 LABEL="n = 100%".

[For some reason CTABLES stayed red, but it worked anyway.]

Older peoples trust in young people	None at all	5.8
	2	9.4
	3	16.3
	4	19.8
	5	23.7
	6	9.8
	7	6.2
	8	6.0
	9	1.6
	A great deal	1.4
	All	1167

Eureka!

Reward self with England vs Slovenia (k.o. in 29 mins) and a can or three of Old Speckled Hen when other half has gone to Eric Bibb (Blues guitar) concert in Coutances.

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: Tim Hennigar [mailto:[hidden email]]
Sent: 15 November 2014 13:27
To: John F Hall
Subject: RE: CTABLES for col %

to get a total col - use

COMPUTE base=1.
VAR LAB base " ".
VAL LAB base 1 "All".

BY base[c] + x003r

Date: Sat, 15 Nov 2014 10:52:15 +0100
From: [hidden email]
Subject: Re: CTABLES for col %
To: [hidden email]

Jon

The measurement levels in EVS 1981 are haywire: they are all Scale and need to be reset to avoid error messages :

var lev

a165 (nom)

a166 a167

x003r x003r2

(ord).

exec.

The syntax you sent (substituting my variables):

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

does this:

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%
	n = 100%	333	226	149	111	132	177

(Ns for age groups, but no total % for trust values)

. . but I want an extra column headed “Total” or preferably “All” showing % for the whole sample.

I’ve also tried:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=x003r TOTAL=YES LABEL='n = 100%' .

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	n = 100%
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%

( Total % for trust, but no Ns for age groups)

In this table “n = 100%” needs changing to “All” but I can do that myself.

So far I’ve got one table or the or the other, but not both.

That’s what I’ve spent hours trying to do and the nearest I’ve got is:

ctables

/TABLE a167 [c] BY x003r [C] [colPCT.COUNT f8.1 "%" TOTALS[COUNT f8.1 "%"]]

/CATEGORIES VARIABLES= x003r TOTAL=YES POSITION=AFTER LABEL="All"

/CATEGORIES VARIABLES= a167 TOTAL=YES POSITION=AFTER LABEL="n = 100%".

[NB: % suppressed in body of table: new row indicating statistic displayed is %]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
		%	%	%	%	%	%	%
Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	65.0
	2	12.9	10.2	10.7	6.3	6.8	4.5	106.0
	3	21.3	20.4	15.4	6.3	9.1	14.1	184.0
	4	24.9	20.8	19.5	18.9	12.1	15.3	223.0
	5	20.1	22.1	25.5	21.6	29.5	27.7	267.0
	6	6.0	9.7	9.4	12.6	16.7	10.7	111.0
	7	2.7	5.3	7.4	9.9	11.4	6.8	70.0
	8	1.5	5.3	4.0	16.2	6.8	10.2	68.0
	9	.0	1.8	2.7	.9	1.5	4.0	18.0
	A great deal	.3	.0	2.0	1.8	3.8	2.8	16.0
	n = 100%	100.0	100.0	100.0	100.0	100.0	100.0	1128.0

All I need is a tweak to the syntax to yield the base for % within each agegroup (? VALIDN ?) instead of 100.0 in the bottom row, but leaving overall total N as 1128 (not 1128.0).

[Hand-made table]

			Age recoded
			15-24	25-34	35-44	45-54	55-64	65 +	All
			%	%	%	%	%	%	%
	Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
		2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
		3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
		4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
		5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
		6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
		7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
		8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
		9	.0	1.8	2.7	.9	1.5	4.0	1.6
		A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
n = 100%			333	125	0	60	132	177	827

I’ve been going round in circles trying to get that, so far to no avail. I’m beginning to feel like the guy at the coffee machine where each choice is labelled:

Coffee/no cup

Cup/ no coffee

Milk, sugar/ no coffee

etc.

Has anyone thought of an additional facility in CROSSTABS to produce the same table(s)?

cro a167 by x003r

/cel col / coltot validn .

[my colour coding as SPSS doesn’t do it for abbreviated syntax]

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Jon K Peck
Sent: 12 November 2014 22:59
To: [hidden email]
Subject: Re: CTABLES for col %

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables
/vlabels variables=x003r a166 display=none
/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]
/categories variables= a166 total=yes position=after.

ctables
/vlabels variables=x003r a167 display=none
/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]
/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)
[Retired academic survey researcher]

Email: [hidden email]
Website: www.surveyresearch.weebly.com
SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

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

Re: CTABLES for col %

Just to add a little more automation, you can use OUTPUT MODIFY to color the "All" column and Totals row, and you can set the column widths there, too. You can generate the syntax from the Custom Tables gui in V21 or later.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: "John F Hall" <[hidden email]>
To: "'Tim Hennigar'" <[hidden email]>
Cc: "'Dominic Abrams'" <[hidden email]>, <[hidden email]>, "'Bruce Weaver'" <[hidden email]>, Jon K Peck/Chicago/IBM@IBMUS, "'Andy W'" <[hidden email]>, "'Jignesh Sutar'" <[hidden email]>
Date: 11/15/2014 10:32 AM
Subject: RE: CTABLES for col %

Tim

Very grateful for this.

*Hennigar.

COMPUTE base=1.

VAR LAB base " ".

VAL LAB base 1 "All".

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY base[c] + x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

Et Voila!

[My post-production shading and adjustments to columns widths]

			Age recoded
		All	15-24	25-34	35-44	45-54	55-64	65 +
Older peoples trust in young people	None at all	5.8%	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%
	2	9.4%	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%
	3	16.3%	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%
	4	19.8%	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%
	5	23.7%	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%
	6	9.8%	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%
	7	6.2%	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%
	8	6.0%	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%
	9	1.6%	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%
	A great deal	1.4%	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%
	n = 100%	1128	333	226	149	111	132	177

Alternatively:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

[My post-production shading and adjustments to columns widths]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%
	n = 100%	333	226	149	111	132	177	1128

Just got to get rid of % in body cells

CTABLES

/TABLE a167 [COLPCT.COUNT F5.1 "%", TOTALS[COUNT F5.0 "%" ]] BY x003r + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL="n = 100%" .

[My post-production shading and adjustments to columns widths]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
	2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
	3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
	4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
	5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
	6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
	7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
	8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
	9	.0	1.8	2.7	.9	1.5	4.0	1.6
	A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
	n = 100%	333	226	149	111	132	177	1128

. . and add column header with %: been trying all afternoon without success. Any offers of a final tweak, anyone?

However, a bit of serendipity following an unintentional syntax error:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY base[c] .


		All
		Column N %
Older peoples trust in young people	None at all	5.8%
	2	9.4%
	3	16.3%
	4	19.8%
	5	23.7%
	6	9.8%
	7	6.2%
	8	6.0%
	9	1.6%
	A great deal	1.4%

Can I get N as base? Yes:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]]

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

		Column N %	Count
Older peoples trust in young people	None at all	5.8%
	2	9.4%
	3	16.3%
	4	19.8%
	5	23.7%
	6	9.8%
	7	6.2%
	8	6.0%
	9	1.6%
	A great deal	1.4%
	n = 100%		1128

This is a useful alternative to FREQUENCIES: will now spend the weekend getting N into another row at the bottom of the same column. Hang on a second . .

CTABLES

/TABLE a167 [COLPCT F5.1 "%", TOTALS [COUNT F5.0 ]] + base [c]

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 LABEL="n = 100%".

[For some reason CTABLES stayed red, but it worked anyway.]

Older peoples trust in young people	None at all	5.8
	2	9.4
	3	16.3
	4	19.8
	5	23.7
	6	9.8
	7	6.2
	8	6.0
	9	1.6
	A great deal	1.4
	All	1167

Eureka!

Reward self with England vs Slovenia (k.o. in 29 mins) and a can or three of Old Speckled Hen when other half has gone to Eric Bibb (Blues guitar) concert in Coutances.

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: Tim Hennigar [mailto:tim.hennigar@...]
Sent: 15 November 2014 13:27
To: John F Hall
Subject: RE: CTABLES for col %

to get a total col - use

COMPUTE base=1.
VAR LAB base " ".
VAL LAB base 1 "All".

BY base[c] + x003r

Date: Sat, 15 Nov 2014 10:52:15 +0100
From: johnfhall@...
Subject: Re: CTABLES for col %
To: [hidden email]

Jon

The measurement levels in EVS 1981 are haywire: they are all Scale and need to be reset to avoid error messages :

var lev

a165 (nom)

a166 a167

x003r x003r2

(ord).

exec.

The syntax you sent (substituting my variables):

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=a167 TOTAL=YES LABEL='n = 100%' .

does this:

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%
	n = 100%	333	226	149	111	132	177

(Ns for age groups, but no total % for trust values)

. . but I want an extra column headed “Total” or preferably “All” showing % for the whole sample.

I’ve also tried:

CTABLES

/TABLE a167 [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY x003r

/SLABELS POSITION=ROW VISIBLE=NO

/CATEGORIES VARIABLES=x003r TOTAL=YES LABEL='n = 100%' .

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	n = 100%
Older peoples trust in young people	None at all	10.2%	4.4%	3.4%	5.4%	2.3%	4.0%	5.8%
	2	12.9%	10.2%	10.7%	6.3%	6.8%	4.5%	9.4%
	3	21.3%	20.4%	15.4%	6.3%	9.1%	14.1%	16.3%
	4	24.9%	20.8%	19.5%	18.9%	12.1%	15.3%	19.8%
	5	20.1%	22.1%	25.5%	21.6%	29.5%	27.7%	23.7%
	6	6.0%	9.7%	9.4%	12.6%	16.7%	10.7%	9.8%
	7	2.7%	5.3%	7.4%	9.9%	11.4%	6.8%	6.2%
	8	1.5%	5.3%	4.0%	16.2%	6.8%	10.2%	6.0%
	9	0.0%	1.8%	2.7%	0.9%	1.5%	4.0%	1.6%
	A great deal	0.3%	0.0%	2.0%	1.8%	3.8%	2.8%	1.4%

( Total % for trust, but no Ns for age groups)

In this table “n = 100%” needs changing to “All” but I can do that myself.

So far I’ve got one table or the or the other, but not both.

That’s what I’ve spent hours trying to do and the nearest I’ve got is:

ctables

/TABLE a167 [c] BY x003r [C] [colPCT.COUNT f8.1 "%" TOTALS[COUNT f8.1 "%"]]

/CATEGORIES VARIABLES= x003r TOTAL=YES POSITION=AFTER LABEL="All"

/CATEGORIES VARIABLES= a167 TOTAL=YES POSITION=AFTER LABEL="n = 100%".

[NB: % suppressed in body of table: new row indicating statistic displayed is %]

		Age recoded
		15-24	25-34	35-44	45-54	55-64	65 +	All
		%	%	%	%	%	%	%
Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	65.0
	2	12.9	10.2	10.7	6.3	6.8	4.5	106.0
	3	21.3	20.4	15.4	6.3	9.1	14.1	184.0
	4	24.9	20.8	19.5	18.9	12.1	15.3	223.0
	5	20.1	22.1	25.5	21.6	29.5	27.7	267.0
	6	6.0	9.7	9.4	12.6	16.7	10.7	111.0
	7	2.7	5.3	7.4	9.9	11.4	6.8	70.0
	8	1.5	5.3	4.0	16.2	6.8	10.2	68.0
	9	.0	1.8	2.7	.9	1.5	4.0	18.0
	A great deal	.3	.0	2.0	1.8	3.8	2.8	16.0
	n = 100%	100.0	100.0	100.0	100.0	100.0	100.0	1128.0

All I need is a tweak to the syntax to yield the base for % within each agegroup (? VALIDN ?) instead of 100.0 in the bottom row, but leaving overall total N as 1128 (not 1128.0).

[Hand-made table]

			Age recoded
			15-24	25-34	35-44	45-54	55-64	65 +	All
			%	%	%	%	%	%	%
	Older peoples trust in young people	None at all	10.2	4.4	3.4	5.4	2.3	4.0	5.8
		2	12.9	10.2	10.7	6.3	6.8	4.5	9.4
		3	21.3	20.4	15.4	6.3	9.1	14.1	16.3
		4	24.9	20.8	19.5	18.9	12.1	15.3	19.8
		5	20.1	22.1	25.5	21.6	29.5	27.7	23.7
		6	6.0	9.7	9.4	12.6	16.7	10.7	9.8
		7	2.7	5.3	7.4	9.9	11.4	6.8	6.2
		8	1.5	5.3	4.0	16.2	6.8	10.2	6.0
		9	.0	1.8	2.7	.9	1.5	4.0	1.6
		A great deal	.3	.0	2.0	1.8	3.8	2.8	1.4
n = 100%			333	125	0	60	132	177	827

I’ve been going round in circles trying to get that, so far to no avail. I’m beginning to feel like the guy at the coffee machine where each choice is labelled:

Coffee/no cup

Cup/ no coffee

Milk, sugar/ no coffee

etc.

Has anyone thought of an additional facility in CROSSTABS to produce the same table(s)?

cro a167 by x003r

/cel col / coltot validn .

[my colour coding as SPSS doesn’t do it for abbreviated syntax]

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Jon K Peck
Sent: 12 November 2014 22:59
To: [hidden email]
Subject: Re: CTABLES for col %

Here is an example using employee data.sav. All generated from the Ctables gui, but I deleted redundant syntax.

CTABLES
/TABLE educ [COLPCT.COUNT PCT40.1, TOTALS[COUNT F40.0]] BY jobcat
/SLABELS POSITION=ROW VISIBLE=NO
/CATEGORIES VARIABLES=educ TOTAL=YES LABEL='n = 100%' .

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
peck@...
phone: 720-342-5621

From: John F Hall <johnfhall@...>
To: [hidden email]
Date: 11/12/2014 01:50 PM
Subject: [SPSSX-L] CTABLES for col %
Sent by: "SPSSX(r) Discussion" <[hidden email]>

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	2.4	1.8	1.4	3.6	4.6	7.6
2	1.2	1.8	.7	3.6	4.6	3.5
3	4.2	7.2	12.4	6.4	6.9	5.8
4	15.7	10.8	13.8	11.8	10.0	8.8
5	21.8	25.2	23.4	22.7	32.3	34.5
6	17.2	20.7	12.4	17.3	13.1	12.3
7	19.3	19.8	12.4	14.5	9.2	8.2
8	10.9	9.9	15.2	15.5	10.0	11.7
9	3.3	1.8	6.2	3.6	3.1	3.5
A great deal	3.9	.9	2.1	.9	6.2	4.1
Total	100.0	100.0	100.0	100.0	100.0	100.0

Older people’s trust in young people [a167]

	15-24	25-34	35-44	45-54	55-64	65 and more years
	%	%	%	%	%	%
None at all	10.2	4.4	3.4	5.4	2.3	4.0
2	12.9	10.2	10.7	6.3	6.8	4.5
3	21.3	20.4	15.4	6.3	9.1	14.1
4	24.9	20.8	19.5	18.9	12.1	15.3
5	20.1	22.1	25.5	21.6	29.5	27.7
6	6.0	9.7	9.4	12.6	16.7	10.7
7	2.7	5.3	7.4	9.9	11.4	6.8
8	1.5	5.3	4.0	16.2	6.8	10.2
9	.0	1.8	2.7	.9	1.5	4.0
A great deal	.3	.0	2.0	1.8	3.8	2.8
Total	100.0	100.0	100.0	100.0	100.0	100.0

. . using the CTABLES commands:

ctables
/vlabels variables=x003r a166 display=none
/table a166 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]
/categories variables= a166 total=yes position=after.

ctables
/vlabels variables=x003r a167 display=none
/table a167 [c] by x003r [c] [colpct.count f8.1 "%" totals[count f8.0 "n= 100%"]]
/categories variables= a167 total=yes position=after.

Can anyone suggest how I can modify the syntax to replace 100.0 in the Total row with the counts used as base Ns and Total with n = 100%? I can do it with the row% but not with the col%.

This Old Dog still needs to learn some New Tricks.

Thanks in advance

John F Hall (Mr)
[Retired academic survey researcher]

Email: johnfhall@...
Website: www.surveyresearch.weebly.com
SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop

PS If anyone knows of similar questions in other surveys, especially before 2005, we’d be grateful to hear about them.

===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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@... (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