script to delete Cumulative Percent column from frequency tables?

[p0tr...@xemaps.com]

Hi,

I recall some mention in this newsgroup about a script that can be
used to scrub an output file to remove particular columns, such as
Cumulative Percent, from frequency tables. I will be eternally
grateful if somebody could send me the script, or point me to a
location from which I can download it.

Lest anybody is tempted to suggest that I generate my output via Basic
Tables or CTables: I am well aware that I can do that, but there are
plenty of times that I want the speed that I get with basic
frequencies, and I like the format except would rather get rid of the
Cumulative Percent column.

Thanks in advance for your help.

Shelley Martin

[Bruce Weaver]

On May 6, 11:42 am, "p0tr-1...@xemaps.com" <p0tr-1...@xemaps.com>
wrote:

Raynald's website has some scripts that do this, or at least something
similar. See items 16 & 17 here, for example:

http://pages.infinit.net/rlevesqu/SampleScripts.htm#PivotTables

--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
"When all else fails, RTFM."

[p0tr...@xemaps.com]

On May 6, 2:19 pm, Bruce Weaver <bwea...@lakeheadu.ca> wrote:
> On May 6, 11:42 am, "p0tr-1...@xemaps.com" <p0tr-1...@xemaps.com>
> wrote:
>
>
>
>
>
> > Hi,
>
> > I recall some mention in this newsgroup about a script that can be
> > used to scrub an output file to remove particular columns, such as
> > Cumulative Percent, from frequency tables.  I will be eternally
> > grateful if somebody could send me the script, or point me to a
> > location from which I can download it.
>

> > Shelley Martin
>
> Raynald's website has some scripts that do this, or at least something
> similar.  See items 16 & 17 here, for example:
>
>    http://pages.infinit.net/rlevesqu/SampleScripts.htm#PivotTables
> --
> Bruce Weaver

Thanks for the speedy reply, Bruce. I remembered Raynald's site a few
minutes after I posted to the newsgroup, and had already found the
scripts that you suggest might work. However, I'm not having any
luck: when I pasted the relevant commands into a new script and then
ran the script, nothing happened other than a default beep from my
computer.

Given the fact that I know pretty much nothing about scripting in
SPSS, perhaps I've made a mistake. This is the text that I copied
from Raynald's example:

Sub Main
Dim objPivot As PivotTable
Dim objItem As ISpssItem

Dim bolFoundOutput As Boolean, bolFoundPivot As Boolean

GetFirstSelectedPivot objPivot, objItem, _
bolFoundOutput, bolFoundPivot
If Not (bolFoundOutput And bolFoundPivot) Then
Exit Sub
End If

HideColumn objPivot, "Frequency"
objItem.Deactivate
End Sub

Would you expect this script to run correctly in SPSS v. 15? I just
keep getting beeps from my computer!

Shelley

[Bruce Weaver]

On May 6, 3:26 pm, "p0tr-1...@xemaps.com" <p0tr-1...@xemaps.com>
wrote:

> Thanks for the speedy reply, Bruce. I remembered Raynald's site a few
> minutes after I posted to the newsgroup, and had already found the
> scripts that you suggest might work. However, I'm not having any
> luck: when I pasted the relevant commands into a new script and then
> ran the script, nothing happened other than a default beep from my
> computer.
>
> Given the fact that I know pretty much nothing about scripting in
> SPSS, perhaps I've made a mistake. This is the text that I copied
> from Raynald's example:

--- example snipped ---

I'm no expert on scripting either, so I'll let someone else advise
you. (Being on old Pascal programmer, I find visual basic abominable
and very difficult to follow.)

[Valli]

Hi Shelley Martin,

The below script will do what you want.

Public objPivotTable As PivotTable
Public objRowLabels As ISpssLabels ' Row Label array.
Public intRow As Integer ' Number of rows in label
array
Public objColumnLabels As ISpssLabels ' Column label arrays
Public intCol As Integer ' Number of columns in
label array.

Sub Main

Dim objDocuments As ISpssDocuments ' SPSS documents.
Dim objOutputDoc As ISpssOutputDoc ' Output document
Dim objItems As ISpssItems ' Output Navigator items
Dim i As Integer

'Get list of documents in SPSS.
Set objDocuments = objSpssApp.Documents

' Get designated document only if there is at least one output
document.
' Omitting this test results in a error message.
If objDocuments.OutputDocCount > 0 Then
'Get the currently designated output document.
Set objOutputDoc = objSpssApp.GetDesignatedOutputDoc
Else
'If no navigator window exists, quit the script.
'comment the following line out and the script will go away
silently.
MsgBox "Please open an output window before running this script.",
vbExclamation, "Script Error"
Exit Sub
End If

' Get the outline tree from the Navigator.
Set objItems = objOutputDoc.Items

' Get each item in the Navigator.
For i = 0 To objItems.Count - 1
Set objItem = objItems.GetItem(i) 'Get each item in turn.
If objItem.SPSSType = SPSSPivot Then 'Check to see if it's a
PivotTable
Set objPivotTable = objItem.ActivateTable() 'Activate the pivot
table.
objPivotTable.UpdateScreen = False 'Defer drawing until later.

'affects only tables created by Frequencies
'hides rows for missing cases, columns for percent and cumulative
percent
Call Freq_Concise 'Taken from "Freq_Concise.sbs"

'do all the drawing at once
objPivotTable.UpdateScreen = True

'Clean-up time: Always remember to Deactivate when finished.
'note that it's the Item, not the Pivot Table, which is
deactivated,
'just as it was the Item that was Activated.
objItem.Deactivate
End If
Next

End Sub

Sub Freq_Concise

Set objColumnLabels = objPivotTable.ColumnLabelArray
Set objRowLabels = objPivotTable.RowLabelArray
intRow = objRowLabels.NumRows
intCol = objColumnLabels.NumColumns

'Check to see if this is a Frequencies Pivot table
If objColumnLabels.ValueAt(1, 0) <> "Frequency" Then
Exit Sub
End If

Call SelectColLabelsByText("Percent")
Call SelectColLabelsByText("Cumulative Percent")
Call SelectRowLabelsByText("Missing")

'Hide the last row, columns total for all valid cases
If objRowLabels.ValueAt(intRow-1,2) = "Total" Then
objRowLabels.HideLabelsWithDataAt(intRow-1, 2)
End If

'Hide "Valid", the pivot table label
'derived from post to comp.soft-sys.stat.spss
'by Raynald Levesque, March 1 2001
'See also SPSS Solution 100006374
If Not IsNull(objRowLabels.ValueAt(0, 1)) Then
If objRowLabels.ValueAt(0, 1) = "Valid" Then
objRowLabels.SelectLabelAt(0, 1)
objPivotTable.Ungroup
End If
End If

End Sub

Sub SelectRowLabelsByText (strText As String)
Dim intR As Integer ' Loop Counter
' RowLabelArray is a 2-dimensional array.
' Loop through the first column to find an exact match of the search
text
For intR = 0 To intRow - 1
If objRowLabels.ValueAt(intR,1) = strText Then
objRowLabels.HideLabelsWithDataAt(intR, 1)
'Exit Sub
End If
Next intR
End Sub

Sub SelectColLabelsByText (strText As String)
Dim intC As Integer ' Loop Counter
'ColumnLabelArray is a 2-dimensional array.
'Loop through the first row containing column headings
'to find an exact match of the search text
For intC = 0 To intCol - 1
If objColumnLabels.ValueAt(1,intC) = strText Then
objColumnLabels.HideLabelsWithDataAt(1, intC)
Exit Sub
End If
Next intC
End Sub

Valli

[p0tr...@xemaps.com]

thanks, Valli. I'll give this a try and will report back to the
newsgroup.

Shelley

On May 9, 4:37 am, Valli <nayagamva...@gmail.com> wrote:
> Hi Shelley Martin,
>
> The below script will do what you want.
>
> Public objPivotTable As PivotTable
> Public objRowLabels As ISpssLabels         ' Row Label array.
> Public intRow As Integer                   ' Number of rows in label
> array
> Public objColumnLabels As ISpssLabels      ' Column label arrays
> Public intCol As Integer                   ' Number of columns in
> label array.
>

(snip)

> Valli

[thesilverim...@gmail.com]

Hello all,

If this group is still active, I hae a question about the above script that errors when I run in SPSS 19.

I have noted below where the error "occurs.

Any help would be appreciated.

Thanks

Public objPivotTable As PivotTable
Public objRowLabels As ISpssLabels ' Row Label array.
Public intRow As Integer ' Number of rows in label array
Public objColumnLabels As ISpssLabels ' Column label arrays
Public intCol As Integer ' Number of columns in label array.

Sub Main

Dim objDocuments As ISpssDocuments ' SPSS documents.
Dim objOutputDoc As ISpssOutputDoc ' Output document
Dim objItems As ISpssItems ' Output Navigator items
Dim i As Integer

Set objDocuments = objSpssApp.Documents 'Get list of documents in SPSS.

' Get designated document only if there is at least one output document.
' Omitting this test results in a error message.
If objDocuments.OutputDocCount > 0 Then

Set objOutputDoc = objSpssApp.GetDesignatedOutputDoc 'Get the currently designated output document.

Else
'If no navigator window exists, quit the script.
'comment the following line out and the script will go away silently.
MsgBox "Please open an output window before running this script.", vbExclamation, "Script Error"
Exit Sub
End If

Set objItems = objOutputDoc.Items ' Get the outline tree from the Navigator.

' Get each item in the Navigator.
For i = 0 To objItems.Count - 1
Set objItem = objItems.GetItem(i) 'Get each item in turn.
If objItem.SPSSType = SPSSPivot Then 'Check to see if it's a PivotTable
Set objPivotTable = objItem.ActivateTable() 'Activate the pivot table.
objPivotTable.UpdateScreen = False 'Defer drawing until later.

'affects only tables created by Frequencies
'hides rows for missing cases, columns for percent and cumulative percent
Call Freq_Concise 'Taken from "Freq_Concise.sbs"

objPivotTable.UpdateScreen = True 'do all the drawing at once

'Clean-up time: Always remember to Deactivate when finished.
'note that it's the Item, not the Pivot Table, which is deactivated,
'just as it was the Item that was Activated.
objItem.Deactivate
End If
Next

End Sub

Sub Freq_Concise

Set objColumnLabels = objPivotTable.ColumnLabelArray
Set objRowLabels = objPivotTable.RowLabelArray
intRow = objRowLabels.NumRows
intCol = objColumnLabels.NumColumns

'ERROR OCCURS NEXT LINE 'Check to see if this is a Frequencies Pivot table