Pivot table pivot query

[Mark Webb-5]

Is it possible to pivot a table using syntax?

Regards

Mark

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

[Andy W]

You can using python. Example below.


******************************************************.
DATA LIST FREE / Cat1 Cat2 W.
BEGIN DATA
1 1 5
1 2 10
2 1 15
2 2 20.
END DATA.
DATASET NAME Sim.
WEIGHT BY W.
*Orig table.
CROSSTABS Cat1 BY Cat2.
*Table we will edit.
CROSSTABS Cat1 BY Cat2.


*Pivot table using python.
BEGIN PROGRAM Python.
import SpssClient, spss

#start the client, grab the items in the output
SpssClient.StartClient()
OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputItemList = OutputDoc.GetOutputItems()

#Grab the last table, 0 based index
lastTab = OutputItemList.Size() - 2
OutputItem = OutputItemList.GetItemAt(lastTab) #down the rabbit hole
PivotTable = OutputItem.GetSpecificType()
SpssPivotMgr = PivotTable.PivotManager()
SpssPivotMgr.TransposeRowsWithColumns() #where the magic happens
SpssClient.StopClient()
END PROGRAM.
******************************************************.

I was looking this morning to see if there was an easy way to answer John Hall's question about sorting a table. I couldn't find a way to do that easily, but this is possible. The python reference guide pdf has examples of moving layers to rows/columns as well.

[Jon Peck]

Recent versions of Statistics provide the ability to sort the rows of a pivot table.  See the OUTPUT MODIFY command.  Prior to that, sorting can be done with SPSSINC MODIFY TABLES using one of the custom functions provided with that command.  OUTPUTMODIFY also provides transposition.

On Mon, Jan 18, 2016 at 7:21 AM, Andy W <[hidden email]> wrote:

You can using python. Example below.


******************************************************.
DATA LIST FREE / Cat1 Cat2 W.
BEGIN DATA
1 1 5
1 2 10
2 1 15
2 2 20.
END DATA.
DATASET NAME Sim.
WEIGHT BY W.
*Orig table.
CROSSTABS Cat1 BY Cat2.
*Table we will edit.
CROSSTABS Cat1 BY Cat2.


*Pivot table using python.
BEGIN PROGRAM Python.
import SpssClient, spss

#start the client, grab the items in the output
SpssClient.StartClient()
OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputItemList = OutputDoc.GetOutputItems()

#Grab the last table, 0 based index
lastTab = OutputItemList.Size() - 2
OutputItem = OutputItemList.GetItemAt(lastTab) #down the rabbit hole
PivotTable = OutputItem.GetSpecificType()
SpssPivotMgr = PivotTable.PivotManager()
SpssPivotMgr.TransposeRowsWithColumns() #where the magic happens
SpssClient.StopClient()
END PROGRAM.
******************************************************.

I was looking this morning to see if there was an easy way to answer John
Hall's question about sorting a table. I couldn't find a way to do that
easily, but this is possible. The python reference guide pdf has examples of
moving layers to rows/columns as well.



-----
Andy W
[hidden email]
http://andrewpwheeler.wordpress.com/
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Pivot-table-pivot-query-tp5731322p5731323.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[Andy W]

Thank you Jon, was not aware of that command. For the both the pivot and sorting it is easy.


****************************************.
CROSSTABS Cat1 BY Cat2.
OUTPUT MODIFY
  /SELECT TABLES
  /IF SUBTYPES=['Crosstabulation']
  /TABLE TRANSPOSE = YES.

FREQ Cat2.
OUTPUT MODIFY
  /SELECT TABLES
  /IF SUBTYPES=['Frequencies']
  /TABLE SORT = COLPOSITION(1).
****************************************.