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 |
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. |
Free forum by Nabble | Edit this page |