SPSSX Discussion

Decimal places and column headers in MEANS output

Classic

List

Threaded

6 messages Options

John F Hall

Decimal places and column headers in MEANS output

I’m writing a set of tutorials to investigate what happens to differences between gross earnings (from paid work) of men and women when controlling for one or more test variables. Doing this with CROSSTABS can lead to some very cluttered and confusing output even for zero-order tables, let alone 1^st and 2^nd order.

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total
			<£6000	<£12000	£12000+	Total
Q901a Sex of respondent	Men	Count	86	362	426	874
	Men	% within Q901a Sex of respondent	9.8%	41.4%	48.7%	100.0%
	Women	Count	383	231	72	686
	Women	% within Q901a Sex of respondent	55.8%	33.7%	10.5%	100.0%
Total		Count	469	593	498	1560
Total		% within Q901a Sex of respondent	30.1%	38.0%	31.9%	100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

I can copy the above table into Word/Excel and edit it manually (including a few calculations inside Excel) into a more easily interpretable format, and add epsilons (percentage point differences) between men and women .

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total (n = 100%)
			<£6000 %	<£12000 %	£12000+ %	Total (n = 100%)
Q901a Sex of respondent	Total		30.1	38.0	31.9	1560
	Total
	Men Women		9.8	41.4	48.7	874
	Men Women		55.8	33.7	10.5	686

		Epsilon	-46.0	+7.7	+38.3

The logic is to see what happens to percentages or epsilons (and possibly odds ratios) when controlling for test variables. To reduce clutter I’ve been playing with an old trick using MEANS to summarise percentages of cases falling into a criterion category.

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

/cells mean count.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	Mean	N
Men	47.9339	726
Women	16.2730	381
Total	37.0370	1107

I can reduce the decimals from 4 places to 1 using cell properties, but again is there a way to set this as a default for all tables, and also to change the column headers to % and N=100% as per the table below?

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	%	N=100%
Men	48.7	874
Women	10.5	686
Total	31.9	1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

*Q901a: Sex of respondent Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation**
% within Q901a: Sex of respondent
Social class of work			Q918b Gross income of R (if working) [3 groups]			Total
Social class of work			<£6000	<£12000	£12000 +	Total
Non-manual	Q901a: Sex of respondent	Men	3.7%	25.9%	70.5%	100.0%
	Q901a: Sex of respondent	Women	21.3%	56.3%	22.4%	100.0%
	Total		11.4%	39.3%	49.3%	100.0%
Manual	Q901a: Sex of respondent	Men	12.7%	61.4%	25.9%	100.0%
	Q901a: Sex of respondent	Women	61.8%	38.2%		100.0%
	Total		23.6%	56.2%	20.1%	100.0%
Total	Q901a: Sex of respondent	Men	8.2%	43.7%	48.1%	100.0%
	Q901a: Sex of respondent	Women	32.2%	51.5%	16.4%	100.0%
	Total		16.6%	46.4%	37.0%	100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.

Jon K Peck

Re: Decimal places and column headers in MEANS output

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: 08/21/2013 05:07 AM
Subject: [SPSSX-L] Decimal places and column headers in MEANS output
Sent by: "SPSSX(r) Discussion" <[hidden email]>

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total
			<£6000	<£12000	£12000+	Total
Q901a Sex of respondent	Men	Count	86	362	426	874
	Men	% within Q901a Sex of respondent	9.8%	41.4%	48.7%	100.0%
	Women	Count	383	231	72	686
	Women	% within Q901a Sex of respondent	55.8%	33.7%	10.5%	100.0%
Total		Count	469	593	498	1560
Total		% within Q901a Sex of respondent	30.1%	38.0%	31.9%	100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

>>>I don't see how removing the % is an improvement, but there are several ways to do this.

1. In V22 the new OUTPUT MODIFY command and equivalent dialog can do this.

2. If you use CTABLES, you can set the percentage format in the CTABLES TABLE subcommand

3. In general, you can use the SPSSINC MODIFY TABLES extension command for this

4. You can write a script or autoscript

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total (n = 100%)
			<£6000 %	<£12000 %	£12000+ %	Total (n = 100%)
Q901a Sex of respondent	Total		30.1	38.0	31.9	1560
	Total
	Men Women		9.8	41.4	48.7	874
	Men Women		55.8	33.7	10.5	686

		Epsilon	-46.0	+7.7	+38.3

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

/cells mean count.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	Mean	N
Men	47.9339	726
Women	16.2730	381
Total	37.0370	1107

>>>Same tactics as above, but also note

1. CTABLES has a POSTCOMPUTE facility that can do calculations like this

2. OLAP CUBES, which is a Base command, includes some postcompute facilities

3. The STATS TABLE CALC extension command can do computations based on table cells and replace or (V21 or later) add new rows and columns for the results.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	%	N=100%
Men	48.7	874
Women	10.5	686
Total	31.9	1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

>>>This may depend on line thickness. There were also some bug fixes to Word exports in V21 FP1 if my memory is right.

*Q901a: Sex of respondent Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation**
% within Q901a: Sex of respondent
Social class of work			Q918b Gross income of R (if working) [3 groups]			Total
Social class of work			<£6000	<£12000	£12000 +	Total
Non-manual	Q901a: Sex of respondent	Men	3.7%	25.9%	70.5%	100.0%
	Q901a: Sex of respondent	Women	21.3%	56.3%	22.4%	100.0%
	Total		11.4%	39.3%	49.3%	100.0%
Manual	Q901a: Sex of respondent	Men	12.7%	61.4%	25.9%	100.0%
	Q901a: Sex of respondent	Women	61.8%	38.2%		100.0%
	Total		23.6%	56.2%	20.1%	100.0%
Total	Q901a: Sex of respondent	Men	8.2%	43.7%	48.1%	100.0%
	Q901a: Sex of respondent	Women	32.2%	51.5%	16.4%	100.0%
	Total		16.6%	46.4%	37.0%	100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

>>>If you don't like Times Roman, look at Edit > Options > Viewer depending on what text you are referring to.

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.

John F Hall

Re: Decimal places and column headers in MEANS output

Jon

Many thanks for this.

I missed the embedded comments at first and only saw the final comment about the TNR font. That may not be SPSS, but Word, as it only happens on the line immediately following a table copied from the Viewer (it happens in Outlook as well).

The OLAP suggestion seems very promising, especially the CREATE facility: I’ve never used OLAP before so will play around to see what it can do.

I can use:

recode incr3 (3=100)(1,2=0)(else=sysmis) into earn12k.

means earn12k by sex edlevel /cel mea cou.

. . to get % of men and women earning £12K+

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
Total	31.9231	1560

Percent difference here is 38.2, . .but when I do:

OLAP CUBES earn12k BY RSex

/CELLS=MEAN COUNT

/CREATE 'earnings' GAC (rsex (1 2) (2 1))

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes'.

I expected CREATE to generate the % point difference between men and women, but this is all I get:

Case Processing Summary
	Cases
	Included		Excluded		Total
	N	Percent	N	Percent	N	Percent
earn12k * edlevel Highest qualification level	1554	51.4%	1471	48.6%	3025	100.0%

OLAP Cubes
edlevel Highest qualification level: Total
	Mean	N
earn12k	32.0463	1554

This only gives me the overall mean (actually a %). What I want to display is the percent difference between men and women for those earning £12K, as per table below:

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
% difference Total	+38.2 31.9231	1560

What am I doing wrong? Or should I be using CTABLES instead?

I’m on v21, but it’s no good me downloading add-ons for SPSS as I’m writing for students who may have only the Gradpack, or no time to play around, or who have much earlier versions of SPSS.

John

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

From: Jon K Peck [mailto:[hidden email]]
Sent: 21 August 2013 19:05
To: John F Hall
Cc: [hidden email]
Subject: Re: [SPSSX-L] Decimal places and column headers in MEANS output

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total
			<£6000	<£12000	£12000+	Total
Q901a Sex of respondent	Men	Count	86	362	426	874
	Men	% within Q901a Sex of respondent	9.8%	41.4%	48.7%	100.0%
	Women	Count	383	231	72	686
	Women	% within Q901a Sex of respondent	55.8%	33.7%	10.5%	100.0%
Total		Count	469	593	498	1560
Total		% within Q901a Sex of respondent	30.1%	38.0%	31.9%	100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

>>>I don't see how removing the % is an improvement, but there are several ways to do this.

1. In V22 the new OUTPUT MODIFY command and equivalent dialog can do this.

2. If you use CTABLES, you can set the percentage format in the CTABLES TABLE subcommand

3. In general, you can use the SPSSINC MODIFY TABLES extension command for this

4. You can write a script or autoscript

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total (n = 100%)
			<£6000 %	<£12000 %	£12000+ %	Total (n = 100%)
Q901a Sex of respondent	Total		30.1	38.0	31.9	1560
	Total
	Men Women		9.8	41.4	48.7	874
	Men Women		55.8	33.7	10.5	686

		Epsilon	-46.0	+7.7	+38.3

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

/cells mean count.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	Mean	N
Men	47.9339	726
Women	16.2730	381
Total	37.0370	1107

>>>Same tactics as above, but also note

1. CTABLES has a POSTCOMPUTE facility that can do calculations like this

2. OLAP CUBES, which is a Base command, includes some postcompute facilities

3. The STATS TABLE CALC extension command can do computations based on table cells and replace or (V21 or later) add new rows and columns for the results.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	%	N=100%
Men	48.7	874
Women	10.5	686
Total	31.9	1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

>>>This may depend on line thickness. There were also some bug fixes to Word exports in V21 FP1 if my memory is right.

*Q901a: Sex of respondent Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation**
% within Q901a: Sex of respondent
Social class of work			Q918b Gross income of R (if working) [3 groups]			Total
Social class of work			<£6000	<£12000	£12000 +	Total
Non-manual	Q901a: Sex of respondent	Men	3.7%	25.9%	70.5%	100.0%
	Q901a: Sex of respondent	Women	21.3%	56.3%	22.4%	100.0%
	Total		11.4%	39.3%	49.3%	100.0%
Manual	Q901a: Sex of respondent	Men	12.7%	61.4%	25.9%	100.0%
	Q901a: Sex of respondent	Women	61.8%	38.2%		100.0%
	Total		23.6%	56.2%	20.1%	100.0%
Total	Q901a: Sex of respondent	Men	8.2%	43.7%	48.1%	100.0%
	Q901a: Sex of respondent	Women	32.2%	51.5%	16.4%	100.0%
	Total		16.6%	46.4%	37.0%	100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

>>>If you don't like Times Roman, look at Edit > Options > Viewer depending on what text you are referring to.

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.

Art Kendall

Re: Decimal places and column headers in MEANS output

Is there a reason to use sysmis rather than a user missing value?
It seems to me that anytime sysmis is assigned by the user, there is a known reason for it being missing.

Art Kendall
Social Research Consultants

On 8/25/2013 4:50 AM, John F Hall [via SPSSX Discussion] wrote:

Jon

Many thanks for this.

I missed the embedded comments at first and only saw the final comment about the TNR font. That may not be SPSS, but Word, as it only happens on the line immediately following a table copied from the Viewer (it happens in Outlook as well).

The OLAP suggestion seems very promising, especially the CREATE facility: I’ve never used OLAP before so will play around to see what it can do.

I can use:

recode incr3 (3=100)(1,2=0)(else=sysmis) into earn12k.

means earn12k by sex edlevel /cel mea cou.

. . to get % of men and women earning £12K+

earn12k * sex Q901a: Sex of respondent

earn12k

sex Q901a: Sex of respondent

Mean = (%)

N

1 Men

48.7414

874

2 Women

10.4956

686

Total

31.9231

1560

Percent difference here is 38.2, . .but when I do:

OLAP CUBES earn12k BY RSex

/CELLS=MEAN COUNT

/CREATE 'earnings' GAC (rsex (1 2) (2 1))

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes'.

I expected CREATE to generate the % point difference between men and women, but this is all I get:

Case Processing Summary

Cases

Included

Excluded

Total

N

Percent

N

Percent

N

Percent

earn12k * edlevel Highest qualification level

1554

51.4%

1471

48.6%

3025

100.0%

OLAP Cubes

edlevel Highest qualification level:   Total

Mean

N

earn12k

32.0463

1554

This only gives me the overall mean (actually a %). What I want to display is the percent difference between men and women for those earning £12K, as per table below:

earn12k * sex Q901a: Sex of respondent

earn12k

sex Q901a: Sex of respondent

Mean = (%)

N

1 Men

48.7414

874

2 Women

10.4956

686

% difference

Total

+38.2

31.9231

1560

What am I doing wrong? Or should I be using CTABLES instead?

I’m on v21, but it’s no good me downloading add-ons for SPSS as I’m writing for students who may have only the Gradpack, or no time to play around, or who have much earlier versions of SPSS.

John

John F Hall (Mr)

[Retired academic survey researcher]

Email:   [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html





From: Jon K Peck [mailto:[hidden email]]
Sent: 21 August 2013 19:05
To: John F Hall
Cc: [hidden email]
Subject: Re: [SPSSX-L] Decimal places and column headers in MEANS output

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: 08/21/2013 05:07 AM
Subject: [SPSSX-L] Decimal places and column headers in MEANS output
Sent by: "SPSSX(r) Discussion" <[hidden email]>

I’m writing a set of tutorials to investigate what happens to differences between gross earnings (from paid work) of men and women when controlling for one or more test variables. Doing this with CROSSTABS can lead to some very cluttered and confusing output even for zero-order tables, let alone 1^st and 2^nd order.

Q901a Sex of respondent * Q918b Gross income of R (if working) [3 groups] Crosstabulation

Q918b Gross income of R (if working) [3 groups]

Total

<£6000

<£12000

£12000+

Q901a Sex of respondent

Men

Count

86

362

426

874

% within Q901a Sex of respondent

9.8%

41.4%

48.7%

100.0%

Women

Count

383

231

72

686

% within Q901a Sex of respondent

55.8%

33.7%

10.5%

100.0%

Total

Count

469

593

498

1560

% within Q901a Sex of respondent

30.1%

38.0%

31.9%

100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

>>>I don't see how removing the % is an improvement, but there are several ways to do this.

1. In V22 the new OUTPUT MODIFY command and equivalent dialog can do this.

2. If you use CTABLES, you can set the percentage format in the CTABLES TABLE subcommand

3. In general, you can use the SPSSINC MODIFY TABLES extension command for this

4. You can write a script or autoscript

I can copy the above table into Word/Excel and edit it manually (including a few calculations inside Excel) into a more easily interpretable format, and add epsilons (percentage point differences) between men and women .

Q901a Sex of respondent * Q918b Gross income of R (if working) [3 groups] Crosstabulation

Q918b Gross income of R (if working) [3 groups]

Total

(n = 100%)

<£6000

%

<£12000

%

£12000+

%

Q901a Sex of respondent

Total

30.1

38.0

31.9

1560

Men

Women

9.8

41.4

48.7

874

55.8

33.7

10.5

686

Epsilon

-46.0

+7.7

+38.3

The logic is to see what happens to percentages or epsilons (and possibly odds ratios) when controlling for test variables. To reduce clutter I’ve been playing with an old trick using MEANS to summarise percentages of cases falling into a criterion category.

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if     ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

   /cells mean count.

Percent earning £12,000 or more * Q901a: Sex of respondent

Percent earning £12,000 or more

Q901a: Sex of respondent

Mean

N

Men

47.9339

726

Women

16.2730

381

Total

37.0370

1107

I can reduce the decimals from 4 places to 1 using cell properties, but again is there a way to set this as a default for all tables, and also to change the column headers to % and N=100% as per the table below?

>>>Same tactics as above, but also note

1. CTABLES has a POSTCOMPUTE facility that can do calculations like this

2. OLAP CUBES, which is a Base command, includes some postcompute facilities

3. The STATS TABLE CALC extension command can do computations based on table cells and replace or (V21 or later) add new rows and columns for the results.

Percent earning £12,000 or more * Q901a: Sex of respondent

Percent earning £12,000 or more

Q901a: Sex of respondent

%

N=100%

Men

48.7

874

Women

10.5

686

Total

31.9

1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

>>>This may depend on line thickness. There were also some bug fixes to Word exports in V21 FP1 if my memory is right.

Q901a: Sex of respondent * Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation

% within Q901a: Sex of respondent

Social class of work

Q918b Gross income of R (if working) [3 groups]

Total

<£6000

<£12000

£12000 +

Non-manual

Q901a: Sex of respondent

Men

3.7%

25.9%

70.5%

100.0%

Women

21.3%

56.3%

22.4%

100.0%

Total

11.4%

39.3%

49.3%

100.0%

Manual

Q901a: Sex of respondent

Men

12.7%

61.4%

25.9%

100.0%

Women

61.8%

38.2%

100.0%

Total

23.6%

56.2%

20.1%

100.0%

Total

Q901a: Sex of respondent

Men

8.2%

43.7%

48.1%

100.0%

Women

32.2%

51.5%

16.4%

100.0%

Total

16.6%

46.4%

37.0%

100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

>>>If you don't like Times Roman, look at Edit > Options > Viewer depending on what text you are referring to.

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email:   [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Decimal-places-and-column-headers-in-MEANS-output-tp5721658p5721722.html

To start a new topic under SPSSX Discussion, email [hidden email]
To unsubscribe from SPSSX Discussion, click here.
NAML

Art Kendall
Social Research Consultants

Jon K Peck

Re: Decimal places and column headers in MEANS output

In reply to this post by John F Hall

OLAP CUBES puts the statistics in the layers with the Total layer on top. Double click and you can see different layers and pivot the statistics into the rows or columns.

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

From: "John F Hall" <[hidden email]>
To: Jon K Peck/Chicago/IBM@IBMUS,
Cc: <[hidden email]>, "'Bruce Weaver'" <[hidden email]>, "'Aidan Kelly'" <[hidden email]>
Date: 08/25/2013 04:11 AM
Subject: RE: [SPSSX-L] Decimal places and column headers in MEANS output

Jon

Many thanks for this.

The OLAP suggestion seems very promising, especially the CREATE facility: I’ve never used OLAP before so will play around to see what it can do.

I can use:

recode incr3 (3=100)(1,2=0)(else=sysmis) into earn12k.

means earn12k by sex edlevel /cel mea cou.

. . to get % of men and women earning £12K+

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
Total	31.9231	1560

Percent difference here is 38.2, . .but when I do:

OLAP CUBES earn12k BY RSex

/CELLS=MEAN COUNT

/CREATE 'earnings' GAC (rsex (1 2) (2 1))

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes'.

I expected CREATE to generate the % point difference between men and women, but this is all I get:

Case Processing Summary
	Cases
	Included		Excluded		Total
	N	Percent	N	Percent	N	Percent
earn12k * edlevel Highest qualification level	1554	51.4%	1471	48.6%	3025	100.0%

OLAP Cubes
edlevel Highest qualification level: Total
	Mean	N
earn12k	32.0463	1554

This only gives me the overall mean (actually a %). What I want to display is the percent difference between men and women for those earning £12K, as per table below:

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
% difference Total	+38.2 31.9231	1560

What am I doing wrong? Or should I be using CTABLES instead?

I’m on v21, but it’s no good me downloading add-ons for SPSS as I’m writing for students who may have only the Gradpack, or no time to play around, or who have much earlier versions of SPSS.

John

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

From: Jon K Peck [mailto:peck@...]
Sent: 21 August 2013 19:05
To: John F Hall
Cc: [hidden email]
Subject: Re: [SPSSX-L] Decimal places and column headers in MEANS output

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

From: John F Hall <johnfhall@...>
To: [hidden email],
Date: 08/21/2013 05:07 AM
Subject: [SPSSX-L] Decimal places and column headers in MEANS output
Sent by: "SPSSX(r) Discussion" <[hidden email]>

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total
			<£6000	<£12000	£12000+	Total
Q901a Sex of respondent	Men	Count	86	362	426	874
	Men	% within Q901a Sex of respondent	9.8%	41.4%	48.7%	100.0%
	Women	Count	383	231	72	686
	Women	% within Q901a Sex of respondent	55.8%	33.7%	10.5%	100.0%
Total		Count	469	593	498	1560
Total		% within Q901a Sex of respondent	30.1%	38.0%	31.9%	100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

>>>I don't see how removing the % is an improvement, but there are several ways to do this.

1. In V22 the new OUTPUT MODIFY command and equivalent dialog can do this.

2. If you use CTABLES, you can set the percentage format in the CTABLES TABLE subcommand

3. In general, you can use the SPSSINC MODIFY TABLES extension command for this

4. You can write a script or autoscript

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total (n = 100%)
			<£6000 %	<£12000 %	£12000+ %	Total (n = 100%)
Q901a Sex of respondent	Total		30.1	38.0	31.9	1560
	Total
	Men Women		9.8	41.4	48.7	874
	Men Women		55.8	33.7	10.5	686

		Epsilon	-46.0	+7.7	+38.3

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

/cells mean count.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	Mean	N
Men	47.9339	726
Women	16.2730	381
Total	37.0370	1107

>>>Same tactics as above, but also note

1. CTABLES has a POSTCOMPUTE facility that can do calculations like this

2. OLAP CUBES, which is a Base command, includes some postcompute facilities

3. The STATS TABLE CALC extension command can do computations based on table cells and replace or (V21 or later) add new rows and columns for the results.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	%	N=100%
Men	48.7	874
Women	10.5	686
Total	31.9	1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

>>>This may depend on line thickness. There were also some bug fixes to Word exports in V21 FP1 if my memory is right.

*Q901a: Sex of respondent Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation**
% within Q901a: Sex of respondent
Social class of work			Q918b Gross income of R (if working) [3 groups]			Total
Social class of work			<£6000	<£12000	£12000 +	Total
Non-manual	Q901a: Sex of respondent	Men	3.7%	25.9%	70.5%	100.0%
	Q901a: Sex of respondent	Women	21.3%	56.3%	22.4%	100.0%
	Total		11.4%	39.3%	49.3%	100.0%
Manual	Q901a: Sex of respondent	Men	12.7%	61.4%	25.9%	100.0%
	Q901a: Sex of respondent	Women	61.8%	38.2%		100.0%
	Total		23.6%	56.2%	20.1%	100.0%
Total	Q901a: Sex of respondent	Men	8.2%	43.7%	48.1%	100.0%
	Q901a: Sex of respondent	Women	32.2%	51.5%	16.4%	100.0%
	Total		16.6%	46.4%	37.0%	100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

>>>If you don't like Times Roman, look at Edit > Options > Viewer depending on what text you are referring to.

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email: johnfhall@...

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.

John F Hall

Re: Decimal places and column headers in MEANS output

Jon

OLAP CUBES gets close to what I want. The FM doesn’t have any reference to having to use pivot tables, but the help menu does (if you scroll far enough down the case studies menu).

If I use ‘epsilon’ instead of ‘earndiff’ it would be almost perfect.

recode incr3 (3=100)(1,2=0)(else=sysmis) into earn12k.

OLAP CUBES earn12k BY edlevel

/CELLS=MEAN COUNT

/CREATE 'earndiff' gac (edlevel (1 3) )

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes on earnings differences for men and women'.

OLAP Cubes on earnings differences for men and women
edlevel Highest qualification level: Total
	Mean	N
earn12k	32.0463	1554

OLAP Cubes on earnings differences for men and women
edlevel Highest qualification level: earndiff
	Mean	N
earn12k	-38.9429	-148

Pivot table after processing:

OLAP Cube
	edlevel Highest qualification level	Mean	N
earn12k	1 A-level or above	54.1463	615
	2 O-level or CSE	19.9153	472
	3 None	15.2034	467
	earndiff	-38.9429	-148
	Total	32.0463	1554

If I use:

OLAP CUBES earn12k BY edlevel

/CELLS=MEAN COUNT (edlevel)

/CREATE 'earndiff' gac (edlevel (1 3) (2 3) (1 2) )

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes on earnings differences for men and women'.

The pivot table comes out:

OLAP Cubes on earnings differences for men and women
	edlevel Highest qualification level	Mean	N
earn12k	1 A-level or above	54.1463	615
	2 O-level or CSE	19.9153	472
	3 None	15.2034	467
	earndiff	-38.9429	-148
	3 None - 2 O-level or CSE	-4.7118	-5
	2 O-level or CSE - 1 A-level or above	-34.2311	-143
	Total	32.0463	1554

The label differences and the negative Ns could be confusing: also the Total row needs to be on top, but it’s close to a decent elaboration table with epsilons already calculated. For tutorial purposes I’ll have to go through the pivot table process step by step with screenshots. Still a couple of manual edits needed for column headers and to get decimals down to one place.

OLAP Cubes on earnings differences for men and women
	sex	%	n=100%
earn12k	Men	48.7	874
	Women	10.5	686
	epsilon	-38.2
	Total	31.9	1560

Thanks again for pointing me in the right direction.

John

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jon K Peck
Sent: 25 August 2013 15:15
To: [hidden email]
Subject: Re: Decimal places and column headers in MEANS output

Jon

Many thanks for this.

The OLAP suggestion seems very promising, especially the CREATE facility: I’ve never used OLAP before so will play around to see what it can do.

I can use:

recode incr3 (3=100)(1,2=0)(else=sysmis) into earn12k.

means earn12k by sex edlevel /cel mea cou.

. . to get % of men and women earning £12K+

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
Total	31.9231	1560

Percent difference here is 38.2, . .but when I do:

OLAP CUBES earn12k BY RSex

/CELLS=MEAN COUNT

/CREATE 'earnings' GAC (rsex (1 2) (2 1))

/HIDESMALLCOUNTS COUNT=5

/TITLE='OLAP Cubes'.

I expected CREATE to generate the % point difference between men and women, but this is all I get:

Case Processing Summary
	Cases
	Included		Excluded		Total
	N	Percent	N	Percent	N	Percent
earn12k * edlevel Highest qualification level	1554	51.4%	1471	48.6%	3025	100.0%

OLAP Cubes
edlevel Highest qualification level: Total
	Mean	N
earn12k	32.0463	1554

This only gives me the overall mean (actually a %). What I want to display is the percent difference between men and women for those earning £12K, as per table below:

*earn12k sex Q901a: Sex of respondent**
earn12k
sex Q901a: Sex of respondent	Mean = (%)	N
1 Men	48.7414	874
2 Women	10.4956	686
% difference Total	+38.2 31.9231	1560

What am I doing wrong? Or should I be using CTABLES instead?

I’m on v21, but it’s no good me downloading add-ons for SPSS as I’m writing for students who may have only the Gradpack, or no time to play around, or who have much earlier versions of SPSS.

John

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

From: Jon K Peck [[hidden email]]
Sent: 21 August 2013 19:05
To: John F Hall
Cc: [hidden email]
Subject: Re: [SPSSX-L] Decimal places and column headers in MEANS output

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total
			<£6000	<£12000	£12000+	Total
Q901a Sex of respondent	Men	Count	86	362	426	874
	Men	% within Q901a Sex of respondent	9.8%	41.4%	48.7%	100.0%
	Women	Count	383	231	72	686
	Women	% within Q901a Sex of respondent	55.8%	33.7%	10.5%	100.0%
Total		Count	469	593	498	1560
Total		% within Q901a Sex of respondent	30.1%	38.0%	31.9%	100.0%

Pivot tables can be edited manually (Cell properties) to get rid of % signs, but is there any way to request all cells in all tables to be displayed as ##.# rather than ##.#%?

>>>I don't see how removing the % is an improvement, but there are several ways to do this.

1. In V22 the new OUTPUT MODIFY command and equivalent dialog can do this.

2. If you use CTABLES, you can set the percentage format in the CTABLES TABLE subcommand

3. In general, you can use the SPSSINC MODIFY TABLES extension command for this

4. You can write a script or autoscript

*Q901a Sex of respondent Q918b Gross income of R (if working) [3 groups] Crosstabulation**
			Q918b Gross income of R (if working) [3 groups]			Total (n = 100%)
			<£6000 %	<£12000 %	£12000+ %	Total (n = 100%)
Q901a Sex of respondent	Total		30.1	38.0	31.9	1560
	Total
	Men Women		9.8	41.4	48.7	874
	Men Women		55.8	33.7	10.5	686

		Epsilon	-46.0	+7.7	+38.3

recode incr3 (3=100)(1,2 =0)(else=sysmis) into highinc.

var lab highinc 'Percent earning £12,000 or more'.

temp.

select if ( v271 eq 1 and workmode eq 2).

means highinc by sex tea edlevel class workage

/cells mean count.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	Mean	N
Men	47.9339	726
Women	16.2730	381
Total	37.0370	1107

>>>Same tactics as above, but also note

1. CTABLES has a POSTCOMPUTE facility that can do calculations like this

2. OLAP CUBES, which is a Base command, includes some postcompute facilities

3. The STATS TABLE CALC extension command can do computations based on table cells and replace or (V21 or later) add new rows and columns for the results.

*Percent earning £12,000 or more Q901a: Sex of respondent**
Percent earning £12,000 or more
Q901a: Sex of respondent	%	N=100%
Men	48.7	874
Women	10.5	686
Total	31.9	1560

I shall be producing dozens of such tables and don’t want to have to edit them all manually.

Finally, the tables in the spv have gridlines: these make the output easier to read, but they disappear when copied or exported to Word or Outlook. Is there any way of keeping them?

>>>This may depend on line thickness. There were also some bug fixes to Word exports in V21 FP1 if my memory is right.

*Q901a: Sex of respondent Q918b Gross income of R (if working) [3 groups] * Social class of work Crosstabulation**
% within Q901a: Sex of respondent
Social class of work			Q918b Gross income of R (if working) [3 groups]			Total
Social class of work			<£6000	<£12000	£12000 +	Total
Non-manual	Q901a: Sex of respondent	Men	3.7%	25.9%	70.5%	100.0%
	Q901a: Sex of respondent	Women	21.3%	56.3%	22.4%	100.0%
	Total		11.4%	39.3%	49.3%	100.0%
Manual	Q901a: Sex of respondent	Men	12.7%	61.4%	25.9%	100.0%
	Q901a: Sex of respondent	Women	61.8%	38.2%		100.0%
	Total		23.6%	56.2%	20.1%	100.0%
Total	Q901a: Sex of respondent	Men	8.2%	43.7%	48.1%	100.0%
	Q901a: Sex of respondent	Women	32.2%	51.5%	16.4%	100.0%
	Total		16.6%	46.4%	37.0%	100.0%

. . and how can I get rid of the abominable 12pt Times Roman which SPSS seems to use as default font immediately after every table?!!!

>>>If you don't like Times Roman, look at Edit > Options > Viewer depending on what text you are referring to.

Arial for ever

John F Hall (Mr)

[Retired academic survey researcher]

Email: [hidden email]

Website: www.surveyresearch.weebly.com

SPSS start page: www.surveyresearch.weebly.com/spss-without-tears.html

PS £12,000 pa may not seem much today, but it was the top third in 1989. This analysis will be repeated with much larger and later samples from 2000 onwards.