**Date:** Fri, 4 Jun 2010 15:11:37 -0600
**Reply-To:** ViAnn Beadle <vab88011@gmail.com>
**Sender:** "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
**From:** ViAnn Beadle <vab88011@gmail.com>
**Subject:** Re: Distance calculation and Output
**In-Reply-To:** <AE707B620D4A294A94C584A1F81D55A4105E94D3@XVS02.dpu.depaul.edu>
**Content-Type:** multipart/alternative;
If this is just a one-time thing, you can manually do all of it the pivot
table editor. Just activate the table by double-clicking on it and start
experimenting.

From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of
Langston, Eric
Sent: Friday, June 04, 2010 1:57 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: Distance calculation and Output

Jon-

Thanks! I hadn't opened up the customfunctions file yet .

I was able to get the pivoting to occur successfully and follow it up with
hiding the correct rows. As I was playing around with it, I ended up
running into a problem . It seems that if I invoke the modify tables command
one to hide some rows and then another time to hide some columns, then the
rows that were hidden in the first case come back! Is that the expected
behavior? How would I go about changing the syntax to make this work
better?

Here's what I'm trying to do in this case:

1. Delete the last 3 rows.

2. Delete the first 3 columns.

3. Pivot the table (ColToRow).

data list list/

ID Group V1 V2 V3 (5f2).

begin data

1 1 98 16 90

2 1 41 49 78

3 1 26 89 47

4 2 3 71 68

5 2 52 32 94

6 2 87 57 86

end data.

PROXIMITIES V1 V2 V3

/VIEW=CASE

/MEASURE=EUCLID

/STANDARDIZE=NONE.

SPSSINC MODIFY TABLES subtype="'Proximity Matrix'"

SELECT=3 4 5

DIMENSION= ROWS

LEVEL = -1 PROCESS = PRECEDING HIDE=TRUE

/STYLES APPLYTO=DATACELLS.

SPSSINC MODIFY TABLES subtype="'Proximity Matrix'"

SELECT=0 1 2

DIMENSION= COLUMNS

LEVEL = -1 PROCESS = PRECEDING HIDE=TRUE

/STYLES APPLYTO=DATACELLS.

SPSSINC MODIFY TABLES subtype="'Proximity Matrix'"

SELECT=0

DIMENSION= COLUMNS

LEVEL = -1 PROCESS = PRECEDING

/STYLES APPLYTO=DATACELLS

CUSTOMFUNCTION="customstylefunctions.moveColumnsToRows(fromdim=0,todim=0)".

Thanks again for your help,

-Eric

From: Jon K Peck [mailto:peck@us.ibm.com]
Sent: Friday, June 04, 2010 12:53 PM
To: Langston, Eric
Subject: Re: [SPSSX-L] Distance calculation and Output

See below.
Jon Peck
SPSS, an IBM Company
peck@us.ibm.com
312-651-3435

From:

"Langston, Eric" <ELANGSTO@depaul.edu>

To:

SPSSX-L@LISTSERV.UGA.EDU

Date:

06/04/2010 11:31 AM

Subject:

Re: [SPSSX-L] Distance calculation and Output

Sent by:

"SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>

_____

Jon-

I have a question about the command and it's help, and if my memory serves
me, I believe you either created this or had a substantial hand in creating
it?
>>>All the blame is mine (except for slow performance) :-)

I read through the SPSS MODIFY TABLES /HELP and was confused by something.
The syntax diagram doesn't make reference to a COLUMNS subcommand, but you
use it in 2 or your 4 examples. It looks like SELECT=2 3 'Upper' and
COLUMNS=2 3 'Upper' would do the same thing. Is there a ROWS subcommand?
And if so, would "SELECT=1 3 'Lower' DIMENSION=ROWS" be the same as ROWS=1 3
'Lower'?
>>>Sorry. The original version of the command used COLUMNS, and when I
generalized the dimensions, I never changed those examples. You should use
SELECT and DIMENSION=ROWS. It's easier to use the dialog box interface to
generate the syntax, though. If you don't have a recent version of the
command, you might want to refresh it from Dev Central. I redesigned the
dialog a while back to make it more usable and support a few extra features.

I was able to figure out how to do the hiding with the command, but I'm
really at a loss as to how the command does the pivoting. I've read through
the readme and the /HELP and the htm file but I can't find any references to
doing the pivoting.
>>>
I thought you might need more explanation here, but I didn't want to go into
details unless you were going to try this route.

Pivoting is accomplished by using the custom functions feature of MODIFY
TABLES, so you have to look at customstylefunctions.py for the details on
the extras supplied with the command. These custom functions are little
bits of Python code that extend the command. You will find a bunch of these
for pivoting. Depending on exactly what you need to do, use one of these,
transpose
moveLayersToColumns
moveLayersToRows
moveColumnsToLayers
moveRowsToLayers
moveColumnsToRows
moveRowsToColumns

You will see example syntax for most of these in that file. It can be
tricky to figure out the level numbering for these functions, so if you get
stuck, send me an example table and exactly how you want to change it, and
I'll figure it out.

Regards,
Jon

Thanks for your help,
-Eric

Syntax

data list list/
ID Group V1 V2 V3 (5f2).
begin data
1 1 98 16 90
2 1 41 49 78
3 1 26 89 47
4 2 3 71 68
5 2 52 32 94
6 2 87 57 86
end data.

PROXIMITIES V1 V2 V3
/VIEW=CASE
/MEASURE=EUCLID
/STANDARDIZE=NONE.

* Need to pivot first.
SPSSINC MODIFY TABLES subtype="'Proximity Matrix'"
SELECT=0 1 2
DIMENSION= ROWS
LEVEL = -1 PROCESS = PRECEDING HIDE=TRUE
/STYLES APPLYTO=DATACELLS.

From: Jon K Peck [ <mailto:peck@us.ibm.com> mailto:peck@us.ibm.com]
Sent: Friday, June 04, 2010 10:26 AM
To: Langston, Eric
Cc: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: [SPSSX-L] Distance calculation and Output

Note that the pivoting and hiding operations described below can be done
with the SPSSINC MODIFY TABLES extension command.

Jon Peck
SPSS, an IBM Company
peck@us.ibm.com
312-651-3435

From:

"Langston, Eric" <ELANGSTO@depaul.edu>

To:

SPSSX-L@LISTSERV.UGA.EDU

Date:

06/04/2010 09:16 AM

Subject:

Re: [SPSSX-L] Distance calculation and Output

Sent by:

"SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>

_____

Josh-

I think I've got a solution for you. After the proximities command
runs, it gives you a square matrix of output. Double click it and pivot
it so that both Case and Case 2 are on the column shelf. That will give
you the 3 columns that you are looking for and the only remaining issue
is there are a few too many rows -- because as the command is currently
written, it gives you the Euclidean distance between ALL of the points
rather than just the points in the Cartesian join of the groups.

Again, double click the output (or just do this after you do the pivots
and you're still editing the table). Right click on the 1 in the second
column -- all of the other 6s should also get highlighted. Then from
the menu choose Select -> Data and Label Cells. Then press the delete
key -- the backspace key won't do anything. Repeat that for 2 and 3.
The top nine rows are what you are looking for.

If you have a lot of these to run and/or you have several more cases,
then I think Excel would come in handy for you. After you pivot the
table, copy and paste it into Excel.

In D3, enter the formula "=B3*1" -- this will 'convert' the number
stored as text to a regular number.
In E3, enter the formula "=IF(D3<4,0,C3)" -- this zeros out the values
you don't want.
In F3, enter the formula "=IF(E3=0,F2,F2+1)" -- this is the lookup key
to a future VLOOKUP reference.
In G3, enter the formula "=IF(ISBLANK(A3),G2,A3)" -- this copies the
first column and fills in the missing values.
In H3, enter the formula "=B3" -- this is because the VLOOKUP command
can only reference values to the right of the lookup key.
In I3, enter the formula "=E3" -- same reason as above.

Starting in K3, enter the numbers 1 thru 9 -- or through however many
rows your final table should have (3 cases in group 1) X (3 cases in
group 2) = (9 cases in final table).
In L3, enter the formula "=VLOOKUP(K3,$F$3:$I$20,2,0)" -- this is the
first column of your final table.
In L3, enter the formula "=VLOOKUP(K3,$F$3:$I$20,3,0)" -- this is the
second column of your final table.
In L3, enter the formula "=VLOOKUP(K3,$F$3:$I$20,4,0)" -- this is the
third column of your final table.

Once you get that bit in Excel setup, then you can paste as many SPSS
outputs as needed and it will immediately generate the final table you
are looking for.

If you would prefer to have the whole thing done in SPSS, then you'll
need to use a Python script. That would have the ability to
programmatically take the proximities output and save it as an SPSS
dataset. Then you could write some syntax to delete out the rows you
don't want.

In the future, one follow up email to the listserv would likely suffice.
I only get the daily digest, so this morning was the first time I saw
any of your messages.

I hope that helps and please let me know if I haven't explained
something well.

Cheers,
-Eric

-----Original Message-----
From: Josh [ <mailto:student_t@YMAIL.COM> mailto:student_t@YMAIL.COM]
Sent: Thursday, June 03, 2010 12:36 PM
Subject: Re: Distance calculation and Output

Thank you very much. However, this doesn't give the output I was looking
for-
ID1 (Group) ID2 (Group) Distance
1 -1 4 -2 67.0373
1 -1 5 -2 63.25346
1 -1 6 -2 62.68174
2 -1 4 -2 78.05127
2 -1 5 -2 63
2 -1 6 -2 106.4472
3 -1 4 -2 66.16646
3 -1 5 -2 59.74111
3 -1 6 -2 99.42334

any ideas?

thanks in advance. JJ

On Thu, 3 Jun 2010 10:49:34 -0400, Art Kendall <Art@DrKendall.org>
wrote:

>see PROXIMITIES under <help>
>
>data list list/
>ID Group V1 V2 V3 (5f2).
>begin data
>1 1 98 16 90
>2 1 41 49 78
>3 1 26 89 47
>4 2 3 71 68
>5 2 52 32 94
>6 2 87 57 86
>end data.
>
>PROXIMITIES V1 V2 V3
> /VIEW=CASE
> /MEASURE=EUCLID
> /STANDARDIZE=NONE.
>
>
>Art Kendall
>Social Research Consultants
>
>On 6/3/2010 8:06 AM, Josh wrote:
>> ID Group V1 V2 V3
>> 1 1 98 16 90
>> 2 1 41 49 78
>> 3 1 26 89 47
>> 4 2 3 71 68
>> 5 2 52 32 94
>> 6 2 87 57 86
>>
>
>=====================
>To manage your subscription to SPSSX-L, send a message to
>LISTSERV@LISTSERV.UGA.EDU (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
LISTSERV@LISTSERV.UGA.EDU (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

[text/html]