Calculate additional table column

DATA LIST FREE (",")/ sex age answer.

BEGIN DATA

1,1,2

1,2,2

1,1,2

1,2,1

1,1,2

2,2,1

2,1,1

2,2,2

2,1,1

2,2,1

END DATA.

VALUE LABELS sex 1 'Male' 2 'Female'.

VALUE LABELS age 1 'Young' 2 'Old'.

VALUE LABELS answer 1 'Yes' 2 'No'.

FORMATS sex age answer (F1).

CTABLES 

  /TABLE answer BY (sex + age) [COUNT 'N' F40, COLPCT '%' F40.1]

  /CATEGORIES VARIABLES = sex TOTAL = YES LABEL = 'Total' POSITION = BEFORE

  /CATEGORIES VARIABLES = h1 [OTHERNM] TOTAL = YES LABEL = 'Total' POSITION = BEFORE.

* In the table I want to insert columns that compute an index;

as an example it is calculated as last % value in the first row (60%) divided by % value of column "Total" (50%) times 100 = 120

Does anybody know how to do it?.

Thanks for any help.

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Jon Peck]

Postcomputes in CTABLES can only work within the categories of a variable, so they won't work here, but the STATS TABLE CALC extension command, which can be installed from Extensions > Extension Hub, can do this (and many other things).  Here is the code for your example.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="x9/x1 * 100" DIMENSION=COLUMNS LEVEL=-1  LOCATION=9 REPEATLOC=NO LABEL="Index" MODE=AFTER HIDEINPUTS=NO
/FORMAT CELLFORMAT="asis" DECIMALS=2  INVALID="".

This appears as Utilities > Calculate with Pivot Table  after installation.

The formula is actually Python code, but for simple arithmetic, it looks like Statistics code, but note that column numbers count from 0.  x is a standin for the value, so x9 refers to the value in the 10th column of the table.

For the example, I added the column at the end, but you could specify the LOCATION parameter to put it anywhere.

See the dialog or syntax help for more information.

On Wed, Aug 28, 2019 at 8:07 AM Mario Giesel <[hidden email]> wrote:

DATA LIST FREE (",")/ sex age answer.

BEGIN DATA

1,1,2

1,2,2

1,1,2

1,2,1

1,1,2

2,2,1

2,1,1

2,2,2

2,1,1

2,2,1

END DATA.

VALUE LABELS sex 1 'Male' 2 'Female'.

VALUE LABELS age 1 'Young' 2 'Old'.

VALUE LABELS answer 1 'Yes' 2 'No'.

FORMATS sex age answer (F1).

CTABLES 

  /TABLE answer BY (sex + age) [COUNT 'N' F40, COLPCT '%' F40.1]

  /CATEGORIES VARIABLES = sex TOTAL = YES LABEL = 'Total' POSITION = BEFORE

  /CATEGORIES VARIABLES = h1 [OTHERNM] TOTAL = YES LABEL = 'Total' POSITION = BEFORE.

* In the table I want to insert columns that compute an index;

as an example it is calculated as last % value in the first row (60%) divided by % value of column "Total" (50%) times 100 = 120

Does anybody know how to do it?.

Thanks for any help.

===================== 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, this is fantastic!

Thanks a lot for this easy solution!!

It will be used a lot now.

Am Mittwoch, 28. August 2019, 18:44:35 MESZ hat Jon Peck <[hidden email]> Folgendes geschrieben:

Postcomputes in CTABLES can only work within the categories of a variable, so they won't work here, but the STATS TABLE CALC extension command, which can be installed from Extensions > Extension Hub, can do this (and many other things).  Here is the code for your example.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="x9/x1 * 100" DIMENSION=COLUMNS LEVEL=-1  LOCATION=9 REPEATLOC=NO LABEL="Index" MODE=AFTER HIDEINPUTS=NO
/FORMAT CELLFORMAT="asis" DECIMALS=2  INVALID="".

This appears as Utilities > Calculate with Pivot Table  after installation.

The formula is actually Python code, but for simple arithmetic, it looks like Statistics code, but note that column numbers count from 0.  x is a standin for the value, so x9 refers to the value in the 10th column of the table.

For the example, I added the column at the end, but you could specify the LOCATION parameter to put it anywhere.

See the dialog or syntax help for more information.

On Wed, Aug 28, 2019 at 8:07 AM Mario Giesel <[hidden email]> wrote:

DATA LIST FREE (",")/ sex age answer.

BEGIN DATA

1,1,2

1,2,2

1,1,2

1,2,1

1,1,2

2,2,1

2,1,1

2,2,2

2,1,1

2,2,1

END DATA.

VALUE LABELS sex 1 'Male' 2 'Female'.

VALUE LABELS age 1 'Young' 2 'Old'.

VALUE LABELS answer 1 'Yes' 2 'No'.

FORMATS sex age answer (F1).

CTABLES 

  /TABLE answer BY (sex + age) [COUNT 'N' F40, COLPCT '%' F40.1]

  /CATEGORIES VARIABLES = sex TOTAL = YES LABEL = 'Total' POSITION = BEFORE

  /CATEGORIES VARIABLES = h1 [OTHERNM] TOTAL = YES LABEL = 'Total' POSITION = BEFORE.

* In the table I want to insert columns that compute an index;

as an example it is calculated as last % value in the first row (60%) divided by % value of column "Total" (50%) times 100 = 120

Does anybody know how to do it?.

Thanks for any help.

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

--
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Jon Peck]

YW.  

This extension can be used with any pivot table (with a few minor exceptions), not just CTABLES, and can even be used to copy values from one table to another, although that requires writing a small Python plugin function to use in the command.

The syntax help has a number of examples of more complicated calculations.

On Thu, Aug 29, 2019 at 12:35 AM Mario Giesel <[hidden email]> wrote:

Jon, this is fantastic!

Thanks a lot for this easy solution!!

It will be used a lot now.

Am Mittwoch, 28. August 2019, 18:44:35 MESZ hat Jon Peck <[hidden email]> Folgendes geschrieben:

Postcomputes in CTABLES can only work within the categories of a variable, so they won't work here, but the STATS TABLE CALC extension command, which can be installed from Extensions > Extension Hub, can do this (and many other things).  Here is the code for your example.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="x9/x1 * 100" DIMENSION=COLUMNS LEVEL=-1  LOCATION=9 REPEATLOC=NO LABEL="Index" MODE=AFTER HIDEINPUTS=NO
/FORMAT CELLFORMAT="asis" DECIMALS=2  INVALID="".

This appears as Utilities > Calculate with Pivot Table  after installation.

The formula is actually Python code, but for simple arithmetic, it looks like Statistics code, but note that column numbers count from 0.  x is a standin for the value, so x9 refers to the value in the 10th column of the table.

For the example, I added the column at the end, but you could specify the LOCATION parameter to put it anywhere.

See the dialog or syntax help for more information.

On Wed, Aug 28, 2019 at 8:07 AM Mario Giesel <[hidden email]> wrote:

DATA LIST FREE (",")/ sex age answer.

BEGIN DATA

1,1,2

1,2,2

1,1,2

1,2,1

1,1,2

2,2,1

2,1,1

2,2,2

2,1,1

2,2,1

END DATA.

VALUE LABELS sex 1 'Male' 2 'Female'.

VALUE LABELS age 1 'Young' 2 'Old'.

VALUE LABELS answer 1 'Yes' 2 'No'.

FORMATS sex age answer (F1).

CTABLES 

  /TABLE answer BY (sex + age) [COUNT 'N' F40, COLPCT '%' F40.1]

  /CATEGORIES VARIABLES = sex TOTAL = YES LABEL = 'Total' POSITION = BEFORE

  /CATEGORIES VARIABLES = h1 [OTHERNM] TOTAL = YES LABEL = 'Total' POSITION = BEFORE.

* In the table I want to insert columns that compute an index;

as an example it is calculated as last % value in the first row (60%) divided by % value of column "Total" (50%) times 100 = 120

Does anybody know how to do it?.

Thanks for any help.

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

--
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

--
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD