Removing header row in custom tables

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Removing header row in custom tables

Roger
I'm generating a custom table that later is exported to a specific Excel sheet in a specific location. All of this works, except the custom table generated by SPSS has a column header row that I cannot figure out how to remove and need it removed to get the formatting I want. What I need is all column headers on one row.

The below code generates sample data and then creates a custom table similar to what I'm working with.

data list list
 /id (f3) female (f1) race (f1) ses (f1) schtype (a3)
  prog (f1) read (f2) write (f2) math (f2) science (f2) socst (f2)
  FY (f4.0).
begin data.
 147 1 1 3 pub 1 47  62  53  53  61 2016
 108 0 1 2 pub 2 34  33  41  36  36 2017
  18 0 3 2 pub 3 50  33  49  44  36 2015
 153 0 1 2 pub 3 39  31  40  39  51 2015
  50 0 2 2 pub 2 50  59  42  53  61 2018
  51 1 2 1 pub 2 42  36  42  31  39 2018
 102 0 1 1 pub 1 52  41  51  53  56 2018
  57 1 1 2 pub 1 71  65  72  66  56 2016
 160 1 1 2 pub 1 55  65  55  50  61 2017
 136 0 1 2 pub 1 65  59  70  63  51 2018
end data.


CTABLES
  /VLABELS VARIABLES=socst FY math DISPLAY=NONE
  /TABLE socst [C] BY FY [C] > math [SUM COMMA40.0]
  /CATEGORIES VARIABLES=socst EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /CATEGORIES VARIABLES= socst FY ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /SLABELS VISIBLE= NO
  /TITLES CORNER = 'So CST'.


The column row below the years is currently blank (would say 'Sum' with /SLABELS VISIBLE = YES), but I need it removed and formatted such that the 'So CST' label and the year labels are all on the same row (when exported to Excel). Currently the top left title corner also has an extra space that becomes two merged rows when exported to Excel. Is there a way to get this formatted as I want? It would look something like this:

So CST | 2015 | 2016 | 2017 | 2018
36        | 40     | .       | 41     | .

Thanks for any help!
Roger

p.s. sorry for any re-posts. I originally posted this with an attachment and it was rejected.

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: Removing header row in custom tables

Jon Peck
You can do this using the SPSSINC MODIFY TABLES extension command, which can be installed from the Extensions > Extension Hub menu, with a small custom function.    
The custom function code should be saved as file
hidelabels.py
in a place where Python can find it.  To see a list of those locations, run SHOW EXT if you don't already know a place and pick a directory in the extensions section that you can write to.

The contents of the file are
def HideInnerColLabels(obj, i, j, numrows, numcols, section, more, custom):
    z = more.columnlabelarray
    z.HideAllLabelsInDimensionAt(2,1)
    return False

Note that indentation matters.  If you have trouble creating the file, send me an email and I will send you the module.

Then, after running the CTABLES command, run this
SPSSINC MODIFY TABLES subtype="customtable"
SELECT=1
DIMENSION= ROWS LEVEL = -1  
PROCESS = PRECEDING
/STYLES  CUSTOMFUNCTION="hidelabels.HideInnerColLabels".

BTW, the corner of the table containing So CST is actually a single double-height cell, but the vertical alignment is set to bottom, so  it looks like two cells.  




On Tue, Oct 8, 2019 at 10:30 PM Roger <[hidden email]> wrote:
I'm generating a custom table that later is exported to a specific Excel sheet in a specific location. All of this works, except the custom table generated by SPSS has a column header row that I cannot figure out how to remove and need it removed to get the formatting I want. What I need is all column headers on one row.

The below code generates sample data and then creates a custom table similar to what I'm working with.

data list list
 /id (f3) female (f1) race (f1) ses (f1) schtype (a3)
  prog (f1) read (f2) write (f2) math (f2) science (f2) socst (f2)
  FY (f4.0).
begin data.
 147 1 1 3 pub 1 47  62  53  53  61 2016
 108 0 1 2 pub 2 34  33  41  36  36 2017
  18 0 3 2 pub 3 50  33  49  44  36 2015
 153 0 1 2 pub 3 39  31  40  39  51 2015
  50 0 2 2 pub 2 50  59  42  53  61 2018
  51 1 2 1 pub 2 42  36  42  31  39 2018
 102 0 1 1 pub 1 52  41  51  53  56 2018
  57 1 1 2 pub 1 71  65  72  66  56 2016
 160 1 1 2 pub 1 55  65  55  50  61 2017
 136 0 1 2 pub 1 65  59  70  63  51 2018
end data.


CTABLES
  /VLABELS VARIABLES=socst FY math DISPLAY=NONE
  /TABLE socst [C] BY FY [C] > math [SUM COMMA40.0]
  /CATEGORIES VARIABLES=socst EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /CATEGORIES VARIABLES= socst FY ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /SLABELS VISIBLE= NO
  /TITLES CORNER = 'So CST'.


The column row below the years is currently blank (would say 'Sum' with /SLABELS VISIBLE = YES), but I need it removed and formatted such that the 'So CST' label and the year labels are all on the same row (when exported to Excel). Currently the top left title corner also has an extra space that becomes two merged rows when exported to Excel. Is there a way to get this formatted as I want? It would look something like this:

So CST | 2015 | 2016 | 2017 | 2018
36        | 40     | .       | 41     | .

Thanks for any help!
Roger

p.s. sorry for any re-posts. I originally posted this with an attachment and it was rejected.

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