SPSSX Discussion

Use pivot tables from output as new input

Classic

List

Threaded

16 messages Options

John Test

Use pivot tables from output as new input

Hi!
We have some analyses which require to use the output of first statistical
analysis as new input for subsequent analyses.
how can it be done in SPSS? It's quite easy in Statistica, but I didn't
found a way to convert the pivot table in Output (SPSS19) into a new data
set for subsequent analysis.
Is there any option/script etc?
THANKS!!

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

ViAnn Beadle

Re: Use pivot tables from output as new input

The OMS command can be wrapped around a set of syntax to select and create
an SPSS dataset. Go to Help > Core System > Output Management System to
learn how to use it.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
John Test
Sent: Sunday, May 15, 2011 4:27 AM
To: [hidden email]
Subject: Use pivot tables from output as new input

Hi!
We have some analyses which require to use the output of first statistical
analysis as new input for subsequent analyses.
how can it be done in SPSS? It's quite easy in Statistica, but I didn't
found a way to convert the pivot table in Output (SPSS19) into a new data
set for subsequent analysis.
Is there any option/script etc?
THANKS!!

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

Art Kendall

Re: Use pivot tables from output as new input

In reply to this post by John Test

In addition to OMS as ViAnn mentioned, depending on what you have and what you want to do with it, you can EXPORT parts of an output file or you can use AGGREGATE.

Art Kendall
Social Research Consultants

On 5/15/2011 6:27 AM, John Test wrote:

Hi!
We have some analyses which require to use the output of first statistical
analysis as new input for subsequent analyses.
how can it be done in SPSS? It's quite easy in Statistica, but I didn't
found a way to convert the pivot table in Output (SPSS19) into a new data
set for subsequent analysis.
Is there any option/script etc?
THANKS!!

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

Art Kendall
Social Research Consultants

John Test

Re: Use pivot tables from output as new input

In reply to this post by John Test

Hi!
thanks so much, I've found kind of description about this OMS
capabilities.
Are there samples available how to do this in script?

Our question is:
If I understand it right, we cannot use the table just from the viewer
file, but have to create a new SPSS data file via OMS.
Thus, we will sometimes end-up in a huge number of files per analysis.
Is there any option in SPSS to wrap together in a kind of workbook like
e.g. in EXCEL what belongs together?
Furthermore, in our SPSS version, only the filename of the data file used
in analysis is displayed. Is it possible to include much more (kind of
version, time stamp etc.) in order to understand later on, which file was
really used? And/or is it possible to include a time stamp with each
analysis in the SPSS output file in order to see later on when (and maybe
which user) did the analysis?
THANKS again!!

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

John Test

Re: Use pivot tables from output as new input

In reply to this post by John Test

On Sun, 15 May 2011 10:22:53 -0400, Art Kendall <[hidden email]> wrote:

><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
><html>
> <head>
> <meta content="text/html; charset=ISO-8859-1"
> http-equiv="Content-Type">
> <title></title>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> In addition to OMS as ViAnn mentioned, depending on
> what you have and what you want to do with it,  you can
> EXPORT  parts of an output file or you can use AGGREGATE. 
> 
> Art Kendall 
> Social Research Consultants 
> 
> On 5/15/2011 6:27 AM, John Test wrote:
> <blockquote
> cite="mid:[hidden email]"
> type="cite">
> <pre wrap="">Hi!
>We have some analyses which require to use the output of first statistical
>analysis as new input for subsequent analyses.
>how can it be done in SPSS? It's quite easy in Statistica, but I didn't
>found a way to convert the pivot table in Output (SPSS19) into a new data
>set for subsequent analysis.
>Is there any option/script etc?
>THANKS!!
>
>=====================
>To manage your subscription to SPSSX-L, send a message to
><a class="moz-txt-link-abbreviated"

href="mailto:[hidden email]">[hidden email]</a> (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
>
></pre>
> </blockquote>
> </body>
></html>
>
>=====================
>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

hi,
thanks so much for your help. I understand the first option, but what do
you mean with AGGREGATE? Are there any demo samples for it?
Best!

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

John Test

Re: Use pivot tables from output as new input

In reply to this post by John Test

hi
short additional question about OMS:
I've understand (hopefully) how to create this OMS request to export
tables to SPSS data file.
However, there is no special example given in the help and my question is:
does the OMS only affect further analyses in future, or can I also export
existing tables in the output file for further analysis, even when the
data file is not available anymore, thus the analyis cannot be rerun?
Thanks!

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

Art Kendall

Re: Use pivot tables from output as new input

In reply to this post by John Test

To see about AGGREGATE, click <help> <topics> type "AGGREGATE" in the
edit box.
Alternatively, type "AGGREGATE" in a syntax window, put your cursor on
it and hit the F1 key.
AGGREGATE can summarize data by a series of break variables. There are
many kind of summaries, means, totals, counts, SDs, etc.
That data can be placed in a new data set or be added as new variables.

Other programs can save things like residuals, ranks, n-tiles, z-scores,
autorecodes, etc.

There are many ways to save data from part of an analysis for use in a
later part of the analysis.
Please describe in more detail what you would like to do. What do you
have as the original data, what to you want out of it, and then what do
you want to do with it.

Art Kendall
Social Research Consultants

On 5/15/2011 10:33 AM, John Test wrote:

> On Sun, 15 May 2011 10:22:53 -0400, Art Kendall<[hidden email]> wrote:
>
>> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>> <html>
>> <head>
>> <meta content="text/html; charset=ISO-8859-1"
>> http-equiv="Content-Type">
>> <title></title>
>> </head>
>> <body bgcolor="#ffffff" text="#000000">
>> In addition to OMS as ViAnn mentioned, depending on
>> what you have and what you want to do with it,  you can
>> EXPORT  parts of an output file or you can use AGGREGATE. 
>> 
>> Art Kendall 
>> Social Research Consultants 
>> 
>> On 5/15/2011 6:27 AM, John Test wrote:
>> <blockquote
>> cite="mid:[hidden email]"
>> type="cite">
>> <pre wrap="">Hi!
>> We have some analyses which require to use the output of first statistical
>> analysis as new input for subsequent analyses.
>> how can it be done in SPSS? It's quite easy in Statistica, but I didn't
>> found a way to convert the pivot table in Output (SPSS19) into a new data
>> set for subsequent analysis.
>> Is there any option/script etc?
>> THANKS!!
>>
>> =====================
>> To manage your subscription to SPSSX-L, send a message to
>> <a class="moz-txt-link-abbreviated"
> href="mailto:[hidden email]">[hidden email]</a> (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
>>
>> </pre>
>> </blockquote>
>> </body>
>> </html>
>>
>> =====================
>> 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
> hi,
> thanks so much for your help. I understand the first option, but what do
> you mean with AGGREGATE? Are there any demo samples for it?
> Best!
>
> =====================
> 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

Art Kendall
Social Research Consultants

drfg2008

Re: Use pivot tables from output as new input

In reply to this post by John Test

Why don't you simply use python (within SPSS)? With python (running within SPSS Syntax) you can easily read in your table results. This is how the syntax looks like (german version SPSS17):

BEGIN PROGRAM.
import spss,spssaux
spss.Submit("GET FILE='C:/<path>/file.sav'.")

cmd="DESCRIPTIVES VARIABLES=Schicht,Geschlecht,Alter,Bewertung."

desc_table,errcode=spssaux.CreateXMLOutput(
cmd,
omsid="Descriptives")
meansal=spssaux.GetValuesFromXMLWorkspace(
desc_table,
tableSubtype="Descriptive Statistics",
rowCategory="Bewertung",
colCategory="Mittelwert",
cellAttrib="text")
if meansal:
print "Der Mittelwert von Bewertung ist: ", meansal[0]
END PROGRAM.

You simply have to tell the programm the row and the column of the table. That's it!
Frank

Dr. Frank Gaeth

Rick Oliver-3

Re: Use pivot tables from output as new input

In reply to this post by John Test

OMS requests remain in effect until an OMSEND command is issued. If you are using the OMS Control Panel, the End button ends the selected OMS request.

The help contains several examples of the results of OMS commands in a topic called "Routing output to IBM SPSS Statistics data files", but it does not show the related command syntax. Here is the syntax for two of the examples:

oms /select tables /if commands=['Means'] subtypes=['Report']
/destination format=sav outfile='c:\temp\temp.sav'
/tag='oms_means'.

MEANS
TABLES=salbegin salary jobtime BY gender
/CELLS MEAN MEDIAN MIN MAX .

omsend tag=['oms_means'].

oms /select tables /if subtypes=['Frequencies']
/destination format=sav outfile='c:\temp\temp.sav'
/tag='oms_freq'.

freq var=gender jobcat.

omsend tag=['oms_freq'].

In these examples, the tag is not actually necessary, since there is no overlap between the two OMS commands. OMS requests can be more sophisticated than this. You can have multiple OMS requests in effect at the same time, and you can rotate the contents of the pivot tables with the COLUMNS subcommand.

From: John Test <[hidden email]>
To: [hidden email]
Date: 05/15/2011 10:00 AM
Subject: Re: Use pivot tables from output as new input
Sent by: "SPSSX(r) Discussion" <[hidden email]>

hi short additional question about OMS: I've understand (hopefully) how to create this OMS request to export tables to SPSS data file. However, there is no special example given in the help and my question is: does the OMS only affect further analyses in future, or can I also export existing tables in the output file for further analysis, even when the data file is not available anymore, thus the analyis cannot be rerun? Thanks! ===================== 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

Re: Use pivot tables from output as new input

In reply to this post by John Test

As Rick explained, oms is a general facility for capturing output. Think of it as listening for tables of the type you specify and turning them into named datasets or files of various types. You can then activate the dataset and use it directly, or you can do more complex things using programmability. It can capture and process any pivot table or set of tables.

Regards,

Jon Peck
Senior Software Engineer, IBM
[hidden email]
new phone: 720-342-5621

From: John Test <[hidden email]>
To: [hidden email]
Date: 05/15/2011 05:00 PM
Subject: Re: [SPSSX-L] Use pivot tables from output as new input
Sent by: "SPSSX(r) Discussion" <[hidden email]>

hi short additional question about OMS: I've understand (hopefully) how to create this OMS request to export tables to SPSS data file. However, there is no special example given in the help and my question is: does the OMS only affect further analyses in future, or can I also export existing tables in the output file for further analysis, even when the data file is not available anymore, thus the analyis cannot be rerun? Thanks! ===================== 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

John Test

Re: Use pivot tables from output as new input

In reply to this post by John Test

Hi Jon,
thanks!
with activate dataset you mean load?
Just an idea:
As far as we learned, there is no easy possibility to tag the used dataset
with timestamp version etc. in the output viewer to see easily after years
which dataset was used for analysis.
Is it possible to use OMS or something else to ouput the whole dataset (if
not too lage...) to the viewer for documentation?
Thanks!

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

Re: Use pivot tables from output as new input

A dataset is not realized as a sav file until you actually save it. OMS can create datasets or sav files. DATASET ACTIVATE switches the active dataset to another open dataset.

The Notes table identifies which dataset (or file) was used for a procedure. The journal file records all your syntax history.

If you want to document the dataset further, you can use Utilities>Data File Comments (ADD DOCUMENT) to create text that will be saved with the file. You can also use custom file attributes (DATAFILE ATTRIBUTE) for documentation. These attributes can be read using programmability for automation purposes.

If you want to add text to the Viewer contents, you can use the COMMENT command to write to the log block, or you can use the TEXT extension command to produce more visible text blocks.

HTH,

Jon Peck
Senior Software Engineer, IBM
[hidden email]
new phone: 720-342-5621

From: John Test <[hidden email]>
To: [hidden email], Jon K Peck/Chicago/IBM@IBMUS
Date: 05/16/2011 08:59 AM
Subject: Re: Use pivot tables from output as new input

Hi Jon, thanks! with activate dataset you mean load? Just an idea: As far as we learned, there is no easy possibility to tag the used dataset with timestamp version etc. in the output viewer to see easily after years which dataset was used for analysis. Is it possible to use OMS or something else to ouput the whole dataset (if not too lage...) to the viewer for documentation? Thanks!

John Test

Re: Use pivot tables from output as new input

In reply to this post by John Test

Hi and thanks!

Great idea!
Question: Is it also possible to use a table in an output viewer without
re running the analysis for use in such a python script?
Is there maybe a script available which uses a selected table in the
output viewer as input and transfer all the rows and column to a new
dataset as a more general approach?
THANKS!!!
P.S.: you wrote: python (running within syntax): thus, is it possible to
run python in the syntax window, too, or do we need the script window
(spss19)? Thanks again

Why don't you simply use python (within SPSS)? With python (running within
SPSS Syntax) you can easily read in your table results. This is how the
syntax looks like (german version SPSS17):

BEGIN PROGRAM.
import spss,spssaux
spss.Submit("GET FILE='C:/<path>/file.sav'.")

cmd="DESCRIPTIVES VARIABLES=Schicht,Geschlecht,Alter,Bewertung."

desc_table,errcode=spssaux.CreateXMLOutput(
cmd,

omsid="Descriptives")
meansal=spssaux.GetValuesFromXMLWorkspace(

desc_table,

tableSubtype="Descriptive Statistics",

rowCategory="Bewertung",

colCategory="Mittelwert",

cellAttrib="text")
if meansal:
print "Der Mittelwert von Bewertung ist: ", meansal[0]
END PROGRAM.

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

drfg2008

Re: Use pivot tables from output as new input

Question: Is it also possible to use a table in an output viewer without
re running the analysis for use in such a python script?

I don't think so. Python is embedded in the syntax (see example).

Dr. Frank Gaeth

Jon K Peck

Re: Use pivot tables from output as new input

In reply to this post by John Test

Yes, you can retrieve a pivot table and create a dataset from it using scripting, but it's more efficient to do this with OMS. The script would locate the table, query the data cells, and use the programmability apis to build the cases for a new dataset. Not hard to do, but I don't know of any full examples, since it is an unusual way of proceeding.

You don't need to use the scripting window at all in order to use Python. Just put the program code between BEGIN PROGRAM and END PROGRAM statements.

Jon Peck
Senior Software Engineer, IBM
[hidden email]
new phone: 720-342-5621

From: John Test <[hidden email]>
To: [hidden email]
Date: 05/16/2011 03:55 PM
Subject: Re: [SPSSX-L] Use pivot tables from output as new input
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Hi and thanks! Great idea! Question: Is it also possible to use a table in an output viewer without re running the analysis for use in such a python script? Is there maybe a script available which uses a selected table in the output viewer as input and transfer all the rows and column to a new dataset as a more general approach? THANKS!!! P.S.: you wrote: python (running within syntax): thus, is it possible to run python in the syntax window, too, or do we need the script window (spss19)? Thanks again Why don't you simply use python (within SPSS)? With python (running within SPSS Syntax) you can easily read in your table results. This is how the syntax looks like (german version SPSS17): BEGIN PROGRAM. import spss,spssaux spss.Submit("GET FILE='C:/<path>/file.sav'.") cmd="DESCRIPTIVES VARIABLES=Schicht,Geschlecht,Alter,Bewertung." desc_table,errcode=spssaux.CreateXMLOutput( cmd, omsid="Descriptives") meansal=spssaux.GetValuesFromXMLWorkspace( desc_table, tableSubtype="Descriptive Statistics", rowCategory="Bewertung", colCategory="Mittelwert", cellAttrib="text") if meansal: print "Der Mittelwert von Bewertung ist: ", meansal[0] END PROGRAM. ===================== 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

ViAnn Beadle

Re: Use pivot tables from output as new input

I think we need more info from John Test on exactly what his scenario is. He appears to want to be able to create a data set long after the pivot table has been created and in the absence of the data used to create that pivot table. Why no access to the data used to create the table?

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jon K Peck
Sent: Monday, May 16, 2011 4:04 PM
To: [hidden email]
Subject: Re: Use pivot tables from output as new input

Hi and thanks!

Great idea!
Question: Is it also possible to use a table in an output viewer without
re running the analysis for use in such a python script?
Is there maybe a script available which uses a selected table in the
output viewer as input and transfer all the rows and column to a new
dataset as a more general approach?
THANKS!!!
P.S.: you wrote: python (running within syntax): thus, is it possible to
run python in the syntax window, too, or do we need the script window
(spss19)? Thanks again

Why don't you simply use python (within SPSS)? With python (running within
SPSS Syntax) you can easily read in your table results. This is how the
syntax looks like (german version SPSS17):

BEGIN PROGRAM.
import spss,spssaux
spss.Submit("GET FILE='C:/<path>/file.sav'.")

cmd="DESCRIPTIVES VARIABLES=Schicht,Geschlecht,Alter,Bewertung."

desc_table,errcode=spssaux.CreateXMLOutput(
cmd,

omsid="Descriptives")
meansal=spssaux.GetValuesFromXMLWorkspace(

desc_table,

tableSubtype="Descriptive Statistics",

rowCategory="Bewertung",

colCategory="Mittelwert",

cellAttrib="text")
if meansal:
print "Der Mittelwert von Bewertung ist: ", meansal[0]
END PROGRAM.

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