How to write SPSS code that "looks up" the rural-urban designations of a zip code

[amorrow10]

Hi all,

I hope you're having a nice day! I am writing because I have two questions
about zipcodes and SPSS. Thank you so much for creating this forum and
posting advice--I really appreciate that Dr. Peck provided code on how to
calculate the distance between zipcodes on the forum previously:

http://spssx-discussion.1045642.n5.nabble.com/distance-between-zipcodes-td5730565.html#a5730562

First, I was able to get his code to work (yay!), but my question is: what
unit of measurement is the distance calculation (miles or km)??

Second, I also was hoping to look up the urban-rural designations of the
zipcodes (I found the USDA excel file with all designations), but I can't
figure out how to do that. That is, I have a list of 1,000 zip codes from my
study, I have the file of every designation possible for every zipcode in
the U.S., but I don't know how to write a line of code that will
automatically help me with this. Does anyone have any suggestions for me?

Best wishes,
Annie

SPSS Version 25 user





--
Sent from: http://spssx-discussion.1045642.n5.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

[Jon Peck]

The details of the calculation are given in the documentation of the ellipseDist function in the extendedTransforms.py module.

"Return the distance in miles between the two sets of coordinates  using an ellipsoidal approximation to the earth"

As for lookup, if you make your Excel file into an SPSS dataset containing the zip code and the urban/rural variable (or others if needed), you can use another function in the extendedTransforms module with SPSS TRANS to retrieve this.

The vlookup function in that module will read this dataset and return the values as needed.  Here's an example from that module (ignore the #s).  The INITIAL subcommand reads that dataset and generates a function to access it, and the FORMULA subcommand does the lookup and returns the requested value.

# Example using the SPSSINC TRANS extension command

#* The lookup table.

#data list free/ value(F8.0) akey(A1).

#begin data

#10 'a'

#20 'b'

#100 'z'

#end data.

#dataset name lookup.

#* The main dataset.

#data list free/x(f8.0) y(A2).

#begin data

#1 'a'

#2 'b'

#5 'a '

#10 ''

#1 'b'

#end data.

#dataset name main.

#dataset activate main.

#spssinc trans result = resultcodealpha

#/initial "extendedTransforms.vlookup('akey', 'value', 'lookup')"

#/formula func(y).

See the module for more details.

On Wed, Aug 15, 2018 at 12:28 PM amorrow10 <[hidden email]> wrote:

Hi all,

I hope you're having a nice day! I am writing because I have two questions
about zipcodes and SPSS. Thank you so much for creating this forum and
posting advice--I really appreciate that Dr. Peck provided code on how to
calculate the distance between zipcodes on the forum previously:

http://spssx-discussion.1045642.n5.nabble.com/distance-between-zipcodes-td5730565.html#a5730562

First, I was able to get his code to work (yay!), but my question is: what
unit of measurement is the distance calculation (miles or km)??

Second, I also was hoping to look up the urban-rural designations of the
zipcodes (I found the USDA excel file with all designations), but I can't
figure out how to do that. That is, I have a list of 1,000 zip codes from my
study, I have the file of every designation possible for every zipcode in
the U.S., but I don't know how to write a line of code that will
automatically help me with this. Does anyone have any suggestions for me?

Best wishes,
Annie

SPSS Version 25 user





--
Sent from: http://spssx-discussion.1045642.n5.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

[amorrow10]

Good morning Dr. Peck,

Thank you so much for your reply. I really appreciate it!

Best wishes,
Annie



--
Sent from: http://spssx-discussion.1045642.n5.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

[Ki Park]

Hi Jon,

I tried your code, and I was able to 'lookup' the numeric information and
bring the FIPS code, but when I tried to 'bring' the names (strings), it
gave me errors.



**I have a dataset like this with all the zip and fips match:


data list free/ fips(F8.0) zip(F5.0) fip_name(A20).
begin data
15 50010 'Boone'
53 50103 'Decatur'
11 50020 'Adair'
153 50124 'Polk'
end data.
dataset name lookup.

**I want to attach to respondents who gave the zip using your code

* The main dataset.
data list free/x(f8.0) y(F5.0).
begin data
1 50265
2 50124
3 50020
4 50124
5 50010
6 50103
7 99999
8 50103
end data.
dataset name main.
dataset activate main.


**This one attach the fips nicely.

spssinc trans result = fips
/initial "extendedTransforms.vlookup('zip', 'fips', 'lookup')"
/formula func(y).
execute.

**This one does not attach the string name.
*I tried to define that fips_name is a string, but the module is still
creating a numeric variable (see figure).
*So, is this means that this will work only for a numeric variable? I am
missing something, but can't figure it out. Thanks!

string fips_name(a20).
spssinc trans result = fips_name
/initial "extendedTransforms.vlookup('zip', 'fip_name', 'lookup')"
/formula func(y).
execute.

Ki

<http://spssx-discussion.1045642.n5.nabble.com/file/t340420/lookup.png>



--
Sent from: http://spssx-discussion.1045642.n5.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

[Jon Peck]

SPSSINC TRANS assumes by default that the result is numeric.  For a string result,  you need to tell it the result type like this.

spssinc trans result = fips_name type=20

/initial "extendedTransforms.vlookup('zip', 'fip_name', 'lookup')"

/formula func(y).

There is no need to declare fips_name as a string with the STRING command.  SPSSINC TRANS will take care of that using the type information.

1 50,265

2 50,124 Polk

3 50,020 Adair

4 50,124 Polk

5 50,010 Boone

6 50,103 Decatur

7 99,999

8 50,103 Decatur

On Thu, Aug 16, 2018 at 11:22 AM Ki Park <[hidden email]> wrote:

Hi Jon,

I tried your code, and I was able to 'lookup' the numeric information and
bring the FIPS code, but when I tried to 'bring' the names (strings), it
gave me errors.



**I have a dataset like this with all the zip and fips match:


data list free/ fips(F8.0) zip(F5.0) fip_name(A20).
begin data
15 50010 'Boone'
53 50103 'Decatur'
11 50020 'Adair'
153 50124 'Polk'
end data.
dataset name lookup.

**I want to attach to respondents who gave the zip using your code

* The main dataset.
data list free/x(f8.0) y(F5.0).
begin data
1 50265
2 50124
3 50020
4 50124
5 50010
6 50103
7 99999
8 50103
end data.
dataset name main.
dataset activate main.


**This one attach the fips nicely.

spssinc trans result = fips
/initial "extendedTransforms.vlookup('zip', 'fips', 'lookup')"
/formula func(y).
execute.

**This one does not attach the string name.
*I tried to define that fips_name is a string, but the module is still
creating a numeric variable (see figure).
*So, is this means that this will work only for a numeric variable? I am
missing something, but can't figure it out. Thanks!

string fips_name(a20).
spssinc trans result = fips_name
/initial "extendedTransforms.vlookup('zip', 'fip_name', 'lookup')"
/formula func(y).
execute.

Ki

<http://spssx-discussion.1045642.n5.nabble.com/file/t340420/lookup.png>



--
Sent from: http://spssx-discussion.1045642.n5.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