Export spss custom tables

Question

I want to export several spss custom tables to excel. I want to export just the tables and exclude the syntax. I tried to select all and exclude if, but I am still getting all of the output.

mirirai · Answer 1 · 2015-05-13T20:48:02.903

You can export the output with the OMS command. Within this command you can specify which output elements you want to export.

If you want to export just the custom tables, you can run the following command.

OMS /SELECT TABLES 
    /IF SUBTYPES = 'Custom Table'
    /DESTINATION FORMAT = XLSX 
     OUTFILE = '/mydir/myfile.xlsx'. 

... Some CTABLES Commands ...

OMSEND.

Every custom table (generated from CTABLES commands) between OMS and OMSEND will be exported to a single .xlsx file specified by the outfile option.

See the SPSS Command Syntax Reference for more information on the OMS command.

score 3 · Answer 2 · answered May 13 '15 at 04:53

Here is an complete example of Output Management System (OMS) in xlsx with Ctable using SPSS Syntax. Here I have run custom table between Month and A1A variables. I have used VIEWER=NO is OMS Syntax which does not display CTables in SPSS output window but create xlsx output with desired tables.

   OMS
      /SELECT TABLES
      /IF COMMANDS=['CTables'] SUBTYPES=['Custom Table']
      /DESTINATION  FORMAT=XLSX
       OUTFILE ='...\Custom Tables.xlsx'
     VIEWER=NO.


 CTABLES 
    /VLABELS VARIABLES=A1A MONTH DISPLAY=LABEL 
    /TABLE A1A [C] BY MONTH [C][COLPCT.COUNT PCT40.1] 
    /CATEGORIES VARIABLES=A1A MONTH ORDER=A KEY=VALUE EMPTY=INCLUDE
    /SLABELS VISIBLE=NO 
    /TITLES
     TITLE='[UnAided Brand Awareness] A1A TOM.'
     CAPTION= ')DATE)TIME'.

 OMSEND.

score 2 · Answer 3 · answered May 12 '15 at 18:02

Try something like this, for which you will need the SPSSINC MODIFY OUTPUT extension:

get file="C:\Program Files\IBM\SPSS\Statistics\23\Samples\English\Employee data.sav".
/* Swich printback on to demo how to exclude printback in export */.
set printback on.

ctables /table jobcat[c] /titles title="Table:  Job cat".
ctables /table gender[c] /titles title="Table:  Gender".

spssinc modify output logs charts headings notes page texts warnings trees model /if process=all  /visibility visible=false.
/* Exclude the Custom Table titles */.
spssinc modify output titles /if itemtitle="Custom Tables" process=all /visibility visible=false.
output export
  /contents  export=visible  layers=visible  modelviews=printsetting
  /xlsx  documentfile="C:/Temp/Test.xlsx"
  operation=createfile sheet='CTables'
  location=lastcolumn  notescaptions=yes.

score 0 · Answer 4 · answered Jan 04 '17 at 14:41

These are good answers, but I wanted to get the simple solution on the record:

Unless there's some reason you need a script (e.g. for automated processes), you can copy and paste the tables straight into excel.

In the output window, right-click on the table, select "copy", and it will paste into Excel without issue.

Nick Ziebert · Answer 5 · 2017-02-26T02:10:41.430

Another solution is to use some .sps script written by a smart guy named Reynolds, located here:

http://www.spsstools.net/en/scripts/577/

Simply download this as .sps on right hand side of screen and save it out into your SPSS folder. At the end of your ctables syntax you will write this simple 1 line syntax that calls this file and will do all the work for you.

script 'N:\WEB\SPSS19\FILENAME.sps'.

It loops through the output window, deletes all syntax/titles and keeps the ctables right before your eyes. It works very well, saves me lots of time at work.

Export spss custom tables

5 Answers5