better formatting of CTABLES exports to Word, Excel

[William Peck]

I have 175 CTABLES that are output in 5 dimensions (overall, by gender, male teams, female teams, coed teams). I just spent 3 days formatting the output in Word and looking to make this more efficient. Next time I'll output to Excel.

- Is it possible to format CTABLE output to fit 8 1/2 x 11 landscape mode? I have hundreds of tables to fit into Word, and most of the time I spend fiddling with the table column widths and such.

- Is it possible to print blanks for N = 0, Percent = 0% in a CTABLE output? I find that it's a lot easier to process nothing than a whole bunch of 0's which you then have to look at and then ignore.

- Is it possible to add a "Section Header" so that if I have 8 related questions, I can add a cell for this?

So the output would be

The following set of questions relate to the academic program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

The following set of questions relate to the athletics program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

Here's my current syntax

*Setup.
define !freq_overall (Ylist = !cmdend)
!do !Y !in (!Ylist)
CTABLES
  /TABLE !Y [C] [COUNT F40.0, COLPCT.COUNT 'Percent' PCT40.0]
  /CATEGORIES VARIABLES=!Y TOTAL=YES.
!doend
!enddefine.

*Run the CTABLES.
!freq_overall Ylist = sports_team q2 q3 q4 q11 q12 q13 q14 q15.

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

*Pseudo logic to get "Section Headers"
!freq_overall Ylist = sports_team.

"print" ==>"The following set of questions relate to the academic program."
!freq_overall Ylist = q2 q3 q4 q11.

"print" ==>"The following set of questions relate to the athletics program."
!freq_overall Ylist = q12 q13 q14 q15.

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

There is a lot to be said here.  But for starters, note the following.

Tables output to Word are supposed to respect your SPSS PAGESIZE and margin settings, which you can control via Viewer settings.  The default behavior is to wrap where needed, but this may not be at a good place for presentation depending on the structure of the table.

You can set horizontal or vertical breakpoints in a table that would control the location of the breaks.  Unfortunately, there is no syntax for this, so you would have to set them by editing the pivot table and using Format > Breakpoints to set them.  Not a good choice for 175 tables.  However, there is a scripting api, SpssLabel.BreakHere(index), that could be applied programmatically using the SPSSINC MODIFY TABLES extension command with a small custom plugin function.

In CTABLES syntax, you can specify how empty or missing cells are displayed using syntax like   /FORMAT EMPTY=BLANK MISSING=''.

You can use the PRINT command to add text between objects, but it goes in the log block, so it isn't very readable and would be mixed in with other log text.  But, the TEXT extension command allows you to display plain or formatted text in the style of titles.  There is also a preference setting for building comments, but it applies to all tables and would be awkward to use here.

Finally, I have to wonder whether a set of 175 tables would be digested by anybody and, if so, whether Word or Excel is necessarily the right vehicle.  You can export Viewer items as html, so if you could do  your edits on the SPSS side, this whole process might be simplified.

On Wed, Jun 12, 2019 at 8:15 AM William Peck <[hidden email]> wrote:

I have 175 CTABLES that are output in 5 dimensions (overall, by gender, male teams, female teams, coed teams). I just spent 3 days formatting the output in Word and looking to make this more efficient. Next time I'll output to Excel.

- Is it possible to format CTABLE output to fit 8 1/2 x 11 landscape mode? I have hundreds of tables to fit into Word, and most of the time I spend fiddling with the table column widths and such.

- Is it possible to print blanks for N = 0, Percent = 0% in a CTABLE output? I find that it's a lot easier to process nothing than a whole bunch of 0's which you then have to look at and then ignore.

- Is it possible to add a "Section Header" so that if I have 8 related questions, I can add a cell for this?

So the output would be

The following set of questions relate to the academic program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

The following set of questions relate to the athletics program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

Here's my current syntax

*Setup.
define !freq_overall (Ylist = !cmdend)
!do !Y !in (!Ylist)
CTABLES
  /TABLE !Y [C] [COUNT F40.0, COLPCT.COUNT 'Percent' PCT40.0]
  /CATEGORIES VARIABLES=!Y TOTAL=YES.
!doend
!enddefine.

*Run the CTABLES.
!freq_overall Ylist = sports_team        q2     q3      q4      q11     q12     q13     q14     q15.

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

*Pseudo logic to get "Section Headers"
!freq_overall Ylist = sports_team.

"print" ==>"The following set of questions relate to the academic program."
!freq_overall Ylist = q2 q3     q4 q11.

"print" ==>"The following set of questions relate to the athletics program."
!freq_overall Ylist = q12       q13     q14     q15.

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

[William Peck]

Thanks Jon, good assessment ...

>>Tables output to Word are supposed to respect your SPSS PAGESIZE and margin settings. The default behavior is to wrap where needed

- - yes, the wrap around is the killer. But for next year I will just output to Excel, which is nicely formatted.

>>You can set horizontal or vertical breakpoints in a table that would control the location of the breaks ... there is a scripting api, SpssLabel.BreakHere(index), that could be applied programmatically using the SPSSINC MODIFY TABLES 

- - good to know, thx.

>>In CTABLES syntax, you can specify how empty or missing cells are displayed using syntax like   /FORMAT EMPTY=BLANK MISSING=''.

- - Whoa ... nice .... makes looking at a table of output MUCH better ...

>>The TEXT extension command allows you to display plain or formatted text in the style of titles.  

- - Whoa x2 ... very nice, will be a big time saver

>>Finally, I have to wonder whether a set of 175 tables would be digested by anybody and, if so, whether Word or Excel is necessarily the right vehicle. You can export Viewer items as html, so if you could do  your edits on the SPSS side, this whole process might be simplified.

- - totally agree on digesting 175 tables!!!! It's an NCAA required thing, so might be required. But yes, hard to digest.

- - good on exporting as HTML, I will definitely investigate that.

Thanks! 

Case closed

On Wed, Jun 12, 2019 at 1:30 PM Jon Peck <[hidden email]> wrote:

There is a lot to be said here.  But for starters, note the following.

Tables output to Word are supposed to respect your SPSS PAGESIZE and margin settings, which you can control via Viewer settings.  The default behavior is to wrap where needed, but this may not be at a good place for presentation depending on the structure of the table.

You can set horizontal or vertical breakpoints in a table that would control the location of the breaks.  Unfortunately, there is no syntax for this, so you would have to set them by editing the pivot table and using Format > Breakpoints to set them.  Not a good choice for 175 tables.  However, there is a scripting api, SpssLabel.BreakHere(index), that could be applied programmatically using the SPSSINC MODIFY TABLES extension command with a small custom plugin function.

In CTABLES syntax, you can specify how empty or missing cells are displayed using syntax like   /FORMAT EMPTY=BLANK MISSING=''.

You can use the PRINT command to add text between objects, but it goes in the log block, so it isn't very readable and would be mixed in with other log text.  But, the TEXT extension command allows you to display plain or formatted text in the style of titles.  There is also a preference setting for building comments, but it applies to all tables and would be awkward to use here.

Finally, I have to wonder whether a set of 175 tables would be digested by anybody and, if so, whether Word or Excel is necessarily the right vehicle.  You can export Viewer items as html, so if you could do  your edits on the SPSS side, this whole process might be simplified.

On Wed, Jun 12, 2019 at 8:15 AM William Peck <[hidden email]> wrote:

I have 175 CTABLES that are output in 5 dimensions (overall, by gender, male teams, female teams, coed teams). I just spent 3 days formatting the output in Word and looking to make this more efficient. Next time I'll output to Excel.

- Is it possible to format CTABLE output to fit 8 1/2 x 11 landscape mode? I have hundreds of tables to fit into Word, and most of the time I spend fiddling with the table column widths and such.

- Is it possible to print blanks for N = 0, Percent = 0% in a CTABLE output? I find that it's a lot easier to process nothing than a whole bunch of 0's which you then have to look at and then ignore.

- Is it possible to add a "Section Header" so that if I have 8 related questions, I can add a cell for this?

So the output would be

The following set of questions relate to the academic program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

The following set of questions relate to the athletics program.

[CTABLE OUTPUT 1]

[CTABLE OUTPUT 2]

[CTABLE OUTPUT n]

Here's my current syntax

*Setup.
define !freq_overall (Ylist = !cmdend)
!do !Y !in (!Ylist)
CTABLES
  /TABLE !Y [C] [COUNT F40.0, COLPCT.COUNT 'Percent' PCT40.0]
  /CATEGORIES VARIABLES=!Y TOTAL=YES.
!doend
!enddefine.

*Run the CTABLES.
!freq_overall Ylist = sports_team        q2     q3      q4      q11     q12     q13     q14     q15.

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

*Pseudo logic to get "Section Headers"
!freq_overall Ylist = sports_team.

"print" ==>"The following set of questions relate to the academic program."
!freq_overall Ylist = q2 q3     q4 q11.

"print" ==>"The following set of questions relate to the athletics program."
!freq_overall Ylist = q12       q13     q14     q15.

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