SPSSX Discussion

Distance calculation and Output

Classic

List

Threaded

12 messages Options

Josh-57

Distance calculation and Output

Hi,
I hope someone can help me with this.

I have information of two groups and I wish to calculate all Euclidian
distances between the obs.
Example:

Data

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

So, for each combination of ID from Group 1 and ID from Group 2 I wish to
calculate the Euclidean distance
Distance = SQRT{ (V1(1)-V1(2))^2 + (V2(1)-V2(2))^2 + (V3(1)-V3(2))^2}

The type of Output I wish to have is :

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

(Column 2 and 3 -(Group) are not necessary)

thanks in advance!

JJ.

=====================
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: Distance calculation and Output

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

Josh-57

Re: Distance calculation and Output

From: Art Kendall <[hidden email]>
To: Josh <[hidden email]>
Cc: [hidden email]
Sent: Thu, 3 June, 2010 15:49:34
Subject: Re: [SPSSX-L] Distance calculation and Output

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
>

	Thank you very much for the reply. However, as I noted in my posting the output format is equally important for me (see below)- any ideas? thanks again. JJ
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

Josh-57

Re: Distance calculation and Output

In reply to this post by Josh-57

Thank you very much for the reply.
However, as I noted in my posting the output format is equally important for
me (see below)- any ideas?

thanks again. JJ

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

On Thu, 3 Jun 2010 10:49:34 -0400, Art Kendall <[hidden email]> 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
>[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

Josh-57

Re: Distance calculation and Output

In reply to this post by Josh-57

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 <[hidden email]> wrote:

Langston, Eric

Re: Distance calculation and Output

In reply to this post by Josh-57

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:[hidden email]]
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 <[hidden email]>
wrote:

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

Jon K Peck

Re: 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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 09:16 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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@...] 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 <[hidden email]> 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 >[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

Langston, Eric

Re: Distance calculation and Output

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?

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’?

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.

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:[hidden email]]
Sent: Friday, June 04, 2010 10:26 AM
To: Langston, Eric
Cc: [hidden email]
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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 09:16 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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 [[hidden email]]
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 <[hidden email]>
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
>[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

Langston, Eric

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:[hidden email]]
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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 11:31 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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 [[hidden email]]
Sent: Friday, June 04, 2010 10:26 AM
To: Langston, Eric
Cc: [hidden email]
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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 09:16 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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 [[hidden email]]
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 <[hidden email]>
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
>[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

ViAnn Beadle

Re: Distance calculation and Output

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:[hidden email]] On Behalf Of Langston, Eric
Sent: Friday, June 04, 2010 1:57 PM
To: [hidden email]
Subject: Re: Distance calculation and Output

Jon-

Thanks! I hadn’t opened up the customfunctions file yet …

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:[hidden email]]
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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 11:31 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 09:16 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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 [[hidden email]]
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 <[hidden email]>
wrote:

Langston, Eric

Re: Distance calculation and Output

Jon- That’s it! I’m on v17.0.2. But we’re upgrading over the summer, so that’s good. I’d been meaning to play around with that command for a while. Thank you for your help today!!

ViAnn- I actually ended up commandeering this thread from the original poster and using it to better understand the modify tables command. I just linked the listserv back in for documentation purposes.

Thank you for all your input! And have a great weekend.

-Eric

From: ViAnn Beadle [mailto:[hidden email]]
Sent: Friday, June 04, 2010 4:12 PM
To: Langston, Eric; [hidden email]
Subject: RE: Distance calculation and Output

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:[hidden email]] On Behalf Of Langston, Eric
Sent: Friday, June 04, 2010 1:57 PM
To: [hidden email]
Subject: Re: Distance calculation and Output

Jon-

Thanks! I hadn’t opened up the customfunctions file yet …

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:[hidden email]]
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
[hidden email]
312-651-3435

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 11:31 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

From:	"Langston, Eric" <[hidden email]>
To:	[hidden email]
Date:	06/04/2010 09:16 AM
Subject:	Re: [SPSSX-L] Distance calculation and Output
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

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 [[hidden email]]
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 <[hidden email]>
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
>[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

Genevieve Odoom

Automatic reply: Distance calculation and Output

Hello,
Thank you for your email. I will be out of the office on Monday, June 7th, returning on Thursday, June 10th and will respond to your email upon my return.

Thanks!
Genevieve Odoom
Policy and Program Analyst
OANHSS
Suite 700 - 7050 Weston Rd. Woodbridge,
ON L4L 8G7
Tel: (905) 851-8821 x 241 Fax: (905) 851-0744
[hidden email]
www.oanhss.org<https://mail.oanhss.org/ecp/Organize/www.oanhss.org>

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