CTABLES cell format problem

[Mario Giesel]

Hello, SPSS friends,
  I've got a problem concerning cell format of percentages in custom tables.
Here's an example:

DATA LIST /v01 1 v02 2.
BEGIN DATA
15
23
14
22
14
21
15
22
13
END DATA.
VAR LAB v01 "Group".
VAL LAB v01 1 "Group 1" 2 "Group 2".
VAR LAB v02 "Number".

CTABLES
  /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
  /TABLE v02 [COLPCT.COUNT '%' PCT40.1] BY v01
  /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

The table shows percentages. When I export it to excel percentages are formatted as numbers within range 0 to 1.
The problem:
In excel I need these numbers multiplied by 100, so instead of 0.20 I need 20.
This would be an easy job in excel for one table. However, I've got repeated exports with hundreds of xls files with several tables each.
Question: Is there an easy way to already "multiply" percentages with 100 within the SPSS custom table or just delete the percentage sign behind the numbers?

Thanks for any comment!
 Mario

[Jon K Peck]

Excel defines percentages as 1/100 of what SPSS uses.  You could change your SPSS tables to avoid this by
- choose a non % format in your TABLE subcommand, e,g,  ROWPCT.COUNT F40.1
- use SPSSINC MODIFY TABLES to change the format ex post
- use STATS TABLE CALC to multiply the values by 100.

HTH,
Jon Peck

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




From:        Mario Giesel <[hidden email]>
To:        [hidden email]
Date:        07/31/2012 08:08 AM
Subject:        [SPSSX-L] CTABLES cell format problem
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello, SPSS friends,
 I've got a problem concerning cell format of percentages in custom tables.
Here's an example:

DATA LIST /v01 1 v02 2.
BEGIN DATA
15
23
14
22
14
21
15
22
13
END DATA.
VAR LAB v01 "Group".
VAL LAB v01 1 "Group 1" 2 "Group 2".
VAR LAB v02 "Number".

CTABLES
 /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
 /TABLE v02 [COLPCT.COUNT '%' PCT40.1] BY v01
 /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
 /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

The table shows percentages. When I export it to excel percentages are formatted as numbers within range 0 to 1.
The problem:
In excel I need these numbers multiplied by 100, so instead of 0.20 I need 20.
This would be an easy job in excel for one table. However, I've got repeated exports with hundreds of xls files with several tables each.
Question: Is there an easy way to already "multiply" percentages with 100 within the SPSS custom table or just delete the percentage sign behind the numbers?

Thanks for any comment!
Mario

[David Marso]

"just delete the percentage sign behind the numbers?"...
CTABLES
  /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
  /TABLE v02 [COLPCT.COUNT F40.1] BY v01
  /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

Mario Giesel wrote

Hello, SPSS friends,
  I've got a problem concerning cell format of percentages in custom tables.
Here's an example:

DATA LIST /v01 1 v02 2.
BEGIN DATA
15
23
14
22
14
21
15
22
13
END DATA.
VAR LAB v01 "Group".
VAL LAB v01 1 "Group 1" 2 "Group 2".
VAR LAB v02 "Number".

CTABLES
  /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
  /TABLE v02 [COLPCT.COUNT '%' PCT40.1] BY v01
  /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

The table shows percentages. When I export it to excel percentages are formatted as numbers within range 0 to 1.
The problem:
In excel I need these numbers multiplied by 100, so instead of 0.20 I need 20.
This would be an easy job in excel for one table. However, I've got repeated exports with hundreds of xls files with several tables each.
Question: Is there an easy way to already "multiply" percentages with 100 within the SPSS custom table or just delete the percentage sign behind the numbers?

Thanks for any comment!
 Mario

[Mario Giesel]

Great & quick help!!

Thanks to Jon and David. :))

Mario

"just delete the percentage sign behind the numbers?"...
CTABLES
  /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
  /TABLE v02 [COLPCT.COUNT *F40.1*] BY v01
  /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

---

Von: Jon K Peck <[hidden email]>
An: [hidden email]
Gesendet: 17:18 Dienstag, 31.Juli 2012
Betreff: Re: CTABLES cell format problem

Excel defines percentages as 1/100 of what SPSS uses.  You could change your SPSS tables to avoid this by
- choose a non % format in your TABLE subcommand, e,g,  ROWPCT.COUNT F40.1
- use SPSSINC MODIFY TABLES to change the format ex post
- use STATS TABLE CALC to multiply the values by 100.

HTH,
Jon Peck

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




From:        Mario Giesel <[hidden email]>
To:        [hidden email]
Date:        07/31/2012 08:08 AM
Subject:        [SPSSX-L] CTABLES cell format problem
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hello, SPSS friends,
 I've got a problem concerning cell format of percentages in custom tables.
Here's an example:

DATA LIST /v01 1 v02 2.
BEGIN DATA
15
23
14
22
14
21
15
22
13
END DATA.
VAR LAB v01 "Group".
VAL LAB v01 1 "Group 1" 2 "Group 2".
VAR LAB v02 "Number".

CTABLES
 /VLABELS VARIABLES=v01 v02 DISPLAY=DEFAULT
 /TABLE v02 [COLPCT.COUNT '%' PCT40.1] BY v01
 /CATEGORIES VARIABLES=v01 ORDER=A KEY=VALUE EMPTY=INCLUDE
 /CATEGORIES VARIABLES=v02 EMPTY=EXCLUDE.

The table shows percentages. When I export it to excel percentages are formatted as numbers within range 0 to 1.
The problem:
In excel I need these numbers multiplied by 100, so instead of 0.20 I need 20.
This would be an easy job in excel for one table. However, I've got repeated exports with hundreds of xls files with several tables each.
Question: Is there an easy way to already "multiply" percentages with 100 within the SPSS custom table or just delete the percentage sign behind the numbers?

Thanks for any comment!
Mario