Quantcast

The SPSSINC MODIFY TABLES extension command

classic Classic list List threaded Threaded
11 messages Options
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

The SPSSINC MODIFY TABLES extension command

Timothy Hennigar

(apologies if this is double posted – I tried this and got an error the first time when I included a screen shot, then got

an error when I reposted saying it was pulled because it was the same as already posted –this is the third attempt)

 

 

 

Ok – for various reasons I have been stuck in V14 land for what seems like forever … (12 years)

 

But with V24 and finally the STYLE=SIMPLE merge of significance test into CTables main table – I can now

try to move to V24 and forward for good.

 

With this I am similarly trying to move from my various old sbs scripts to take advantage of the many supplemental features

available in the extension commands and python functions contributed by many incl Jon. (THANKS SO MUCH!)

 

 

The first such application is

 

SPSSINC MODIFY TABLES with customstylefunctions.py – to remove the footnotes generated by the testing.

I copied the example in the function and modified Descriptives to 'Custom Table' and Process to ALL

and the following appears to work perfectly well.

 

*SPSSINC MODIFY TABLES subtype="'Custom Table'"

*SELECT=0

*DIMENSION= COLUMNS

*LEVEL = -1  PROCESS = ALL

*/STYLES  APPLYTO=DATACELLS

*CUSTOMFUNCTION="customstylefunctions.hideFootnotes".

 

 

After this the other functions I have tried are NOT working

 

 

The second application seems problematic.

I want to use the ‘reletter’ function to change the standard lettering provided by SPSS for the tests – again I copied the example

It already selected Custom Table so I Ieft that but changed Process to ALL (I was not sure of <<ALL>> - but left as is does not

seem to impact) and for the moment left the ‘xywz’ as is – while the command seems to process through all the tables – it does

not do anything ..

 

 

*SPSSINC MODIFY TABLES subtype="'Custom Table'"

*SELECT="<<ALL>>"

*DIMENSION= COLUMNS

*LEVEL = -1  PROCESS = ALL

*/STYLES  APPLYTO=BOTH

*CUSTOMFUNCTION="customstylefunctions.reletter(letters='xyzw')".

 

Idealy what I want as letters in the example would be replace

 

ABABCDABCDEABCABABABCD

 

with

 

BCDEFGHIJKLMNOPQRSTUVWX

 

but nothing is happening when run despite cycling through all the tables

 

 

 

Lastly for now I want to turn the text red for cells that include a significance letter (the letter, Ns, %s if there) – I tried the stock ‘colorIfEndsWithAtoZLetter’

thinking I could modify this to changes the text color if it works – but like the reletter function - while the command seems to process through all the tables

– it does not do anything ..

 

 

SPSSINC MODIFY TABLES subtype="'Custom Table'"

SELECT=0

DIMENSION = COLUMNS

LEVEL = -1  PROCESS = ALL

/STYLES APPLYTO=DATACELLS

CUSTOMFUNCTION="customstylefunctions.colorIfEndsWithAtoZLetter(r=255, g=0, b=0)".

 

 

I have noticed throughout the extension small differences in referecences like Custom Table vs customtable etc

wonder if there are some bugs in this command now??

 

 

 

 

 

 

Thanks!

 

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

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.

 

===================== 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
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command

John F Hall

I’m having exactly the same problem, so I now bcc myself to make sure the message is delivered as desired.  They always appear in Nabble, so somebody did a FUBAR.

 

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

 

 

 

===================== 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
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command

Jon Peck
In reply to this post by Timothy Hennigar
If you are using V24 with the merged test table, the reletter and endswith... functions do not apply.  The letters in the cells are not regular text and are not seen by these functions.  The good news, though, is that there is are new, very cool MODIFY TABLES keywords for this purpose: SIGCELLS, and SIGLEVELS.  Here is an example using the employee data.sav file.

CTABLES
  /VLABELS VARIABLES=jobcat gender DISPLAY=DEFAULT
  /TABLE jobcat [C][COUNT F40.0] BY gender [C]
  /CATEGORIES VARIABLES=jobcat gender ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE
  /CRITERIA CILEVEL=95
  /COMPARETEST TYPE=PROP ALPHA=0.05,.01 ADJUST=BONFERRONI ORIGIN=COLUMN INCLUDEMRSETS=YES 
    CATEGORIES=ALLVISIBLE MERGE=YES STYLE=SIMPLE SHOWSIG=NO.


* all sig cells.
SPSSINC MODIFY TABLES subtype="'Custom Table'"
SELECT="<<ALL>>" sigcells=allsig siglevels=both
DIMENSION= COLUMNS
LEVEL = -1  PROCESS = PRECEDING 
/STYLES  APPLYTO=DATACELLS 
BACKGROUNDCOLOR=200 200 0. 

The command has the ability to selectively color only certain categories.  Here's an example where only the A, B, and C categories are colored.
* with subtables.
CTABLES
  /VLABELS VARIABLES=minority jobcat gender DISPLAY=DEFAULT
  /TABLE minority BY jobcat [COUNT F40.0] + gender [COUNT F40.0]
  /CATEGORIES VARIABLES=minority jobcat gender ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE
  /CRITERIA CILEVEL=95
  /COMPARETEST TYPE=PROP ALPHA=0.05,.01 ADJUST=BONFERRONI ORIGIN=COLUMN INCLUDEMRSETS=YES 
    CATEGORIES=ALLVISIBLE MERGE=YES STYLE=SIMPLE SHOWSIG=NO.

SPSSINC MODIFY TABLES subtype="'Custom Table'"
SELECT="<<ALL>>" sigcells=abc siglevels=both
DIMENSION= COLUMNS
LEVEL = -1  PROCESS = PRECEDING 
/STYLES  APPLYTO=DATACELLS 
BACKGROUNDCOLOR=128 128 255.

The dialog box has a new subdialog: Significance.  See the dialog help (on Utilities) and/or the syntax help for the command.  To see that, put the cursor in an instance of the command and press F1 (just as you would for syntax help on a built-in command).

If you have an older version of MODIFY TABLES, you will need to update it from the new Extensions menu.

If you have trouble with this send me a description and sample output off list ([hidden email])

On Thu, May 26, 2016 at 12:55 PM, Timothy Hennigar <[hidden email]> wrote:

(apologies if this is double posted – I tried this and got an error the first time when I included a screen shot, then got

an error when I reposted saying it was pulled because it was the same as already posted –this is the third attempt)

 

 

 

Ok – for various reasons I have been stuck in V14 land for what seems like forever … (12 years)

 

But with V24 and finally the STYLE=SIMPLE merge of significance test into CTables main table – I can now

try to move to V24 and forward for good.

 

With this I am similarly trying to move from my various old sbs scripts to take advantage of the many supplemental features

available in the extension commands and python functions contributed by many incl Jon. (THANKS SO MUCH!)

 

 

The first such application is

 

SPSSINC MODIFY TABLES with customstylefunctions.py – to remove the footnotes generated by the testing.

I copied the example in the function and modified Descriptives to 'Custom Table' and Process to ALL

and the following appears to work perfectly well.

 

*SPSSINC MODIFY TABLES subtype="'Custom Table'"

*SELECT=0

*DIMENSION= COLUMNS

*LEVEL = -1  PROCESS = ALL

*/STYLES  APPLYTO=DATACELLS

*CUSTOMFUNCTION="customstylefunctions.hideFootnotes".

 

 

After this the other functions I have tried are NOT working

 

 

The second application seems problematic.

I want to use the ‘reletter’ function to change the standard lettering provided by SPSS for the tests – again I copied the example

It already selected Custom Table so I Ieft that but changed Process to ALL (I was not sure of <<ALL>> - but left as is does not

seem to impact) and for the moment left the ‘xywz’ as is – while the command seems to process through all the tables – it does

not do anything ..

 

 

*SPSSINC MODIFY TABLES subtype="'Custom Table'"

*SELECT="<<ALL>>"

*DIMENSION= COLUMNS

*LEVEL = -1  PROCESS = ALL

*/STYLES  APPLYTO=BOTH

*CUSTOMFUNCTION="customstylefunctions.reletter(letters='xyzw')".

 

Idealy what I want as letters in the example would be replace

 

ABABCDABCDEABCABABABCD

 

with

 

BCDEFGHIJKLMNOPQRSTUVWX

 

but nothing is happening when run despite cycling through all the tables

 

 

 

Lastly for now I want to turn the text red for cells that include a significance letter (the letter, Ns, %s if there) – I tried the stock ‘colorIfEndsWithAtoZLetter’

thinking I could modify this to changes the text color if it works – but like the reletter function - while the command seems to process through all the tables

– it does not do anything ..

 

 

SPSSINC MODIFY TABLES subtype="'Custom Table'"

SELECT=0

DIMENSION = COLUMNS

LEVEL = -1  PROCESS = ALL

/STYLES APPLYTO=DATACELLS

CUSTOMFUNCTION="customstylefunctions.colorIfEndsWithAtoZLetter(r=255, g=0, b=0)".

 

 

I have noticed throughout the extension small differences in referecences like Custom Table vs customtable etc

wonder if there are some bugs in this command now??

 

 

 

 

 

 

Thanks!

 

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

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.

 

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



--
Jon K Peck
[hidden email]

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

emma78
Hi,
I use the SPSSINC MERGE TABLES syntax to merge a ctable with the table for significance.
Does anybody know if it is possible to add a new column for the significant letters with SPSSINC MODIFY TABLES? So that these are not directly in the column but in a new colum beside the percent values?


Thank you!
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

Jon Peck
That would be difficult.  In Statistics v24, significance indicators can be displayed in the cells as a special marker rather than as contents.

I did write some code a while ago that moves those markers to a separate row.

Please explain the ultimate goal here.  There might be other solutions that would be easier.
On Tue, Mar 7, 2017 at 9:46 AM emma78 <[hidden email]> wrote:
Hi,

I use the SPSSINC MERGE TABLES syntax to merge a ctable with the table for

significance.

Does anybody know if it is possible to add a new column for the significant

letters with SPSSINC MODIFY TABLES? So that these are not directly in the

column but in a new colum beside the percent values?





Thank you!







--

View this message in context: http://spssx-discussion.1045642.n5.nabble.com/The-SPSSINC-MODIFY-TABLES-extension-command-tp5732254p5733962.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.



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

To manage your subscription to SPSSX-L, send a message to

[hidden email] (not to SPSSX-L), with no body text except the

command. To leave the list, send the command

SIGNOFF SPSSX-L

For a list of commands to manage subscriptions, send the command

INFO REFCARD

===================== 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
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

emma78
Hi,

I would need
first column absolute n
second column in %
and in the third the sign. letters

n       %       sig
36  47   b c d
14  18
12  16
6  8


At the moment the letters are also in column two, which is better than in a separate table as earlier but in another separate column would be better  :-)
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

Jon Peck
The easy thing to do would be to use MERGE TABLES with a blank as the separator and attach to the % column.  E. g.,
SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HALIGN=LEFT SEPARATOR=" ".

This is visually similar to what you want, but the significance indicators are still in the percent column.  I specified left alignment so that the values would line up regardless of the number of significance markers.

If you really need a separate column, this could be done with the STATS TABLE CALC extension command using a custom Python function to get the significance markers and put them in a new column and erase them from the previous column.   New columns would require version 21 or later.

Documentation on writing custom functions for TABLE CALC is included with the command.

On Tue, Mar 7, 2017 at 10:02 AM, emma78 <[hidden email]> wrote:
Hi,

I would need
first column absolute n
second column in %
and in the third the sign. letters

n       %       sig
36        47       b c d
14        18
12        16
6         8


At the moment the letters are also in column two, which is better than in a
separate table as earlier but in another separate column would be better
:-)



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/The-SPSSINC-MODIFY-TABLES-extension-command-tp5732254p5733965.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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



--
Jon K Peck
[hidden email]

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

Jon Peck
Here is how to use STATS TABLE CALC after the significance merge to move the significance markers to the adjacent cell in a new column for each category.  It uses a custom function as the TABLE CALC formula.

First, a table (using employee data.sav shipped with Statistics).  Each column category has two statistics - count and column percent.  There are three categories.

CTABLES
  /TABLE educ [COUNT F40.0, COLPCT.COUNT PCT40.1] BY jobcat
  /CATEGORIES VARIABLES=educ jobcat ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE
  /CRITERIA CILEVEL=95
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=BONFERRONI ORIGIN=COLUMN

Second, the significance table is merged into the main table.  The sig markers go in the percent column, and the separator between the value and markers is a single blank.  (The default separator is a line break.)  The target column labels are modified to include the letter code identifiers used as the significance markers.

SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HIDE=YES APPENDTITLE=YES APPENDCAPTION=YES ADDLABELLEAF=YES
HALIGN=RIGHT SEPARATOR=" ".

Third, TABLE CALC is used to blank out the significance markers in the percent column and insert them in the column to the right.  It operates on the latest table of type customtable.  It uses a small custom Python function named movesig in a module named calccustom.  Critically, the columns to operate on are specified by number counting from zero (1 3 5).  Those are where the sig markers are put by MERGE TABLES.  This requires Statistics version 21 or later.

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="calccustom.movesig(datacells, roworcol, datacells.GetValueAt(roworcol, int(currentloc)), int(currentloc))"
    DIMENSION=COLUMNS LEVEL = -1  LOCATION=1 3 5  LABEL="Sig." MODE=AFTER
    CUSTOMMODULE="calccustom".

Here is the custom function.  It first finds the markers in the current cell, if any, and just returns a blank if there are none.
Otherwise it overwrites the value in the percent column without the markers and then returns the markers,
TABLE CALC will put the returned value in the new column.

def movesig(datacells, roworcol, priorvalue, currentloc):
    """get sig markers and move to adjacent column
   
    datacells is the data cell array of the table
    roworcol is the row number currently being processed
    priorvalue is the value in the current cell
    currentloc is the column number"""
   
    s = priorvalue.split()  # split the value on a blank
    if len(s) <= 1:
        return " "
    else:
        # replace the current cell value with the part preceding the sig marker
        datacells.SetValueAt(roworcol, currentloc, s[0])
        # return the sig marker text for insertion into the target column cell
        return " ".join(s[1:])
       
It is critical to list all the source column numbers to operate on, which requires knowing the number of column categories and the number of statistics or columns per category.  Usually one would just specify the column label and use the REPEAT keyword to do the iteration, but in this case, the custom function needs to know the actual input column number, because it has to change the value in that cell.  This could be made to work without specifying all those values at the cost of extra complexity.

The calccustom.py file needs to be created with the function code above and saved in a location where Python can find it such as the python\lib\site-packages directory under the Statistics installation.  STATS TABLE CALC is installed as part of the Python Essentials and appears on the Utilities menu.  If SPSSINC MERGE TABLES is not already installed, it can be installed from the Utilities or Extensions (v24+) menu.

On Tue, Mar 7, 2017 at 11:04 AM, Jon Peck <[hidden email]> wrote:
The easy thing to do would be to use MERGE TABLES with a blank as the separator and attach to the % column.  E. g.,
SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HALIGN=LEFT SEPARATOR=" ".

This is visually similar to what you want, but the significance indicators are still in the percent column.  I specified left alignment so that the values would line up regardless of the number of significance markers.

If you really need a separate column, this could be done with the STATS TABLE CALC extension command using a custom Python function to get the significance markers and put them in a new column and erase them from the previous column.   New columns would require version 21 or later.

Documentation on writing custom functions for TABLE CALC is included with the command.

On Tue, Mar 7, 2017 at 10:02 AM, emma78 <[hidden email]> wrote:
Hi,

I would need
first column absolute n
second column in %
and in the third the sign. letters

n       %       sig
36        47       b c d
14        18
12        16
6         8


At the moment the letters are also in column two, which is better than in a
separate table as earlier but in another separate column would be better
:-)



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/The-SPSSINC-MODIFY-TABLES-extension-command-tp5732254p5733965.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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



--
Jon K Peck
[hidden email]




--
Jon K Peck
[hidden email]

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AW: [SPSSX-L] The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

emma78

Hi,

I have installed V24 with ctbals and there is a problem with the calccustom module.

 

Do I have to import it?

 


Von: Jon Peck <[hidden email]>
Gesendet: Mittwoch, 8.
März 2017 04:09
An: Claudia Weitkowitz
Cc: SPSS List
Betreff: Re: [SPSSX-L] The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

 

Here is how to use STATS TABLE CALC after the significance merge to move the significance markers to the adjacent cell in a new column for each category.  It uses a custom function as the TABLE CALC formula.

 

First, a table (using employee data.sav shipped with Statistics).  Each column category has two statistics - count and column percent.  There are three categories.

 

CTABLES
  /TABLE educ [COUNT F40.0, COLPCT.COUNT PCT40.1] BY jobcat
  /CATEGORIES VARIABLES=educ jobcat ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE
  /CRITERIA CILEVEL=95
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=BONFERRONI ORIGIN=COLUMN

 

Second, the significance table is merged into the main table.  The sig markers go in the percent column, and the separator between the value and markers is a single blank.  (The default separator is a line break.)  The target column labels are modified to include the letter code identifiers used as the significance markers.

 

SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HIDE=YES APPENDTITLE=YES APPENDCAPTION=YES ADDLABELLEAF=YES
HALIGN=RIGHT SEPARATOR=" ".

 

Third, TABLE CALC is used to blank out the significance markers in the percent column and insert them in the column to the right.  It operates on the latest table of type customtable.  It uses a small custom Python function named movesig in a module named calccustom.  Critically, the columns to operate on are specified by number counting from zero (1 3 5).  Those are where the sig markers are put by MERGE TABLES.  This requires Statistics version 21 or later.

 

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="calccustom.movesig(datacells, roworcol, datacells.GetValueAt(roworcol, int(currentloc)), int(currentloc))"
    DIMENSION=COLUMNS LEVEL = -1  LOCATION=1 3 5  LABEL="Sig." MODE=AFTER
    CUSTOMMODULE="calccustom".

 

Here is the custom function.  It first finds the markers in the current cell, if any, and just returns a blank if there are none.

Otherwise it overwrites the value in the percent column without the markers and then returns the markers,

TABLE CALC will put the returned value in the new column.

 

def movesig(datacells, roworcol, priorvalue, currentloc):
    """get sig markers and move to adjacent column
   
    datacells is the data cell array of the table
    roworcol is the row number currently being processed
    priorvalue is the value in the current cell
    currentloc is the column number"""
   
    s = priorvalue.split()  # split the value on a blank
    if len(s) <= 1:
        return " "
    else:
        # replace the current cell value with the part preceding the sig marker
        datacells.SetValueAt(roworcol, currentloc, s[0])
        # return the sig marker text for insertion into the target column cell
        return " ".join(s[1:])
       

It is critical to list all the source column numbers to operate on, which requires knowing the number of column categories and the number of statistics or columns per category.  Usually one would just specify the column label and use the REPEAT keyword to do the iteration, but in this case, the custom function needs to know the actual input column number, because it has to change the value in that cell.  This could be made to work without specifying all those values at the cost of extra complexity.

 

The calccustom.py file needs to be created with the function code above and saved in a location where Python can find it such as the python\lib\site-packages directory under the Statistics installation.  STATS TABLE CALC is installed as part of the Python Essentials and appears on the Utilities menu.  If SPSSINC MERGE TABLES is not already installed, it can be installed from the Utilities or Extensions (v24+) menu.

 

On Tue, Mar 7, 2017 at 11:04 AM, Jon Peck <[hidden email]> wrote:

The easy thing to do would be to use MERGE TABLES with a blank as the separator and attach to the % column.  E. g.,

SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HALIGN=LEFT SEPARATOR=" ".

 

This is visually similar to what you want, but the significance indicators are still in the percent column.  I specified left alignment so that the values would line up regardless of the number of significance markers.

 

If you really need a separate column, this could be done with the STATS TABLE CALC extension command using a custom Python function to get the significance markers and put them in a new column and erase them from the previous column.   New columns would require version 21 or later.

 

Documentation on writing custom functions for TABLE CALC is included with the command.

 

On Tue, Mar 7, 2017 at 10:02 AM, emma78 <[hidden email]> wrote:

Hi,

I would need
first column absolute n
second column in %
and in the third the sign. letters

n       %       sig
36        47       b c d
14        18
12        16
6         8


At the moment the letters are also in column two, which is better than in a
separate table as earlier but in another separate column would be better
:-)



--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/The-SPSSINC-MODIFY-TABLES-extension-command-tp5732254p5733965.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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



--

Jon K Peck
[hidden email]




--

Jon K Peck
[hidden email]

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

Jon Peck
Hi Claudia,

The instructions were to save the Python function code in the email as calccustom.py.  I did it that way, because the listserve can be unfriendly to attachments, but I have attached it to this message.  Save it somewhere that Python will find it such as the python\lib\site-packages directory under your Statistics installation.

-Jon



On Fri, Mar 10, 2017 at 8:44 AM, Claudia Weitkowitz <[hidden email]> wrote:

Hi,

I have installed V24 with ctbals and there is a problem with the calccustom module.

 

Do I have to import it?

 


Von: Jon Peck <[hidden email]>
Gesendet: Mittwoch, 8.
März 2017 04:09
An: Claudia Weitkowitz
Cc: SPSS List
Betreff: Re: [SPSSX-L] The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

 

Here is how to use STATS TABLE CALC after the significance merge to move the significance markers to the adjacent cell in a new column for each category.  It uses a custom function as the TABLE CALC formula.

 

First, a table (using employee data.sav shipped with Statistics).  Each column category has two statistics - count and column percent.  There are three categories.

 

CTABLES
  /TABLE educ [COUNT F40.0, COLPCT.COUNT PCT40.1] BY jobcat
  /CATEGORIES VARIABLES=educ jobcat ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE
  /CRITERIA CILEVEL=95
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=BONFERRONI ORIGIN=COLUMN

 

Second, the significance table is merged into the main table.  The sig markers go in the percent column, and the separator between the value and markers is a single blank.  (The default separator is a line break.)  The target column labels are modified to include the letter code identifiers used as the significance markers.

 

SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HIDE=YES APPENDTITLE=YES APPENDCAPTION=YES ADDLABELLEAF=YES
HALIGN=RIGHT SEPARATOR=" ".

 

Third, TABLE CALC is used to blank out the significance markers in the percent column and insert them in the column to the right.  It operates on the latest table of type customtable.  It uses a small custom Python function named movesig in a module named calccustom.  Critically, the columns to operate on are specified by number counting from zero (1 3 5).  Those are where the sig markers are put by MERGE TABLES.  This requires Statistics version 21 or later.

 

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING
/TARGET FORMULA="calccustom.movesig(datacells, roworcol, datacells.GetValueAt(roworcol, int(currentloc)), int(currentloc))"
    DIMENSION=COLUMNS LEVEL = -1  LOCATION=1 3 5  LABEL="Sig." MODE=AFTER
    CUSTOMMODULE="calccustom".

 

Here is the custom function.  It first finds the markers in the current cell, if any, and just returns a blank if there are none.

Otherwise it overwrites the value in the percent column without the markers and then returns the markers,

TABLE CALC will put the returned value in the new column.

 

def movesig(datacells, roworcol, priorvalue, currentloc):
    """get sig markers and move to adjacent column
   
    datacells is the data cell array of the table
    roworcol is the row number currently being processed
    priorvalue is the value in the current cell
    currentloc is the column number"""
   
    s = priorvalue.split()  # split the value on a blank
    if len(s) <= 1:
        return " "
    else:
        # replace the current cell value with the part preceding the sig marker
        datacells.SetValueAt(roworcol, currentloc, s[0])
        # return the sig marker text for insertion into the target column cell
        return " ".join(s[1:])
       

It is critical to list all the source column numbers to operate on, which requires knowing the number of column categories and the number of statistics or columns per category.  Usually one would just specify the column label and use the REPEAT keyword to do the iteration, but in this case, the custom function needs to know the actual input column number, because it has to change the value in that cell.  This could be made to work without specifying all those values at the cost of extra complexity.

 

The calccustom.py file needs to be created with the function code above and saved in a location where Python can find it such as the python\lib\site-packages directory under the Statistics installation.  STATS TABLE CALC is installed as part of the Python Essentials and appears on the Utilities menu.  If SPSSINC MERGE TABLES is not already installed, it can be installed from the Utilities or Extensions (v24+) menu.

 

On Tue, Mar 7, 2017 at 11:04 AM, Jon Peck <[hidden email]> wrote:

The easy thing to do would be to use MERGE TABLES with a blank as the separator and attach to the % column.  E. g.,

SPSSINC MERGE TABLES MATCHLABEL="Column N %" ATTACH=COLUMNS
MODE=MERGE
/OPTIONS HALIGN=LEFT SEPARATOR=" ".

 

This is visually similar to what you want, but the significance indicators are still in the percent column.  I specified left alignment so that the values would line up regardless of the number of significance markers.

 

If you really need a separate column, this could be done with the STATS TABLE CALC extension command using a custom Python function to get the significance markers and put them in a new column and erase them from the previous column.   New columns would require version 21 or later.

 

Documentation on writing custom functions for TABLE CALC is included with the command.

 

On Tue, Mar 7, 2017 at 10:02 AM, emma78 <[hidden email]> wrote:

Hi,

I would need
first column absolute n
second column in %
and in the third the sign. letters

n       %       sig
36        47       b c d
14        18
12        16
6         8


At the moment the letters are also in column two, which is better than in a
separate table as earlier but in another separate column would be better
:-)



--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/The-SPSSINC-MODIFY-TABLES-extension-command-tp5732254p5733965.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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



--

Jon K Peck
[hidden email]




--

Jon K Peck
[hidden email]




--
Jon K Peck
[hidden email]

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

calccustom.py (870 bytes) Download Attachment
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: The SPSSINC MODIFY TABLES extension command/SPSSINC MERGE TABLES

emma78
Hi,
I have a large table which is consisting of a lot if subtables like these:
1 "applies very much"
2
3
4 "do not apply"
Total
1 "applies very much"
2
3
4 "do not apply"
Total
1 "applies very much"
2
3
4 "do not apply"
Total

I'm trying to delete all rows from 1 "applies very much" to 4 "do not apply" in all subtables.
I know that there are other ways to get only the total row but I try to understand the modify tables command ;-)


I used this syntax:

SPSSINC MODIFY TABLES subtype="Custom Table"
SELECT= "1 = trifft voll und ganz zu"  "2" "3" "4" "5 = trifft überhaupt nicht zu"
DIMENSION= rows
LEVEL = -1  PROCESS = PRECEDING  HIDE=TRUE
/STYLES  APPLYTO=BOTH.

But only strange things are happening, only the first and last is deeted and not for all rows.

What am I doing wrong?
Loading...