merge

[Joris Kregting]

I want to merge 2 files. In the first one, each case has one zip code and one house number. In the second files, each case has a zip code and a range of house numbers, and a variable which I a want to add at the first file.

For example:
A case in the first file has 1011AP (zip code) and 17 (house number).
This case has to match/merge with the case in the second file with 1011AP (zip code) and the range of house numbers 1-23.

Is there a syntax for this merge-question?

[Andy W]

See http://spssx-discussion.1045642.n5.nabble.com/Merge-from-1-point-on-a-road-to-a-stretch-of-road-td5715019.html for basically the same problem and a few working solutions posted.

[David Marso]

Please post examples of EXACTLY how the range appears in the second data file.

Joris Kregting wrote

I want to merge 2 files. In the first one, each case has one zip code and one house number. In the second files, each case has a zip code and a range of house numbers, and a variable which I a want to add at the first file.

For example:
A case in the first file has 1011AP (zip code) and 17 (house number).
This case has to match/merge with the case in the second file with 1011AP (zip code) and the range of house numbers 1-23.

Is there a syntax for this merge-question?

[Jon K Peck]

There is an interval lookup function that can be used with the STATS TRANS extension command to do this.  It requires the Python Essentials and the extendedTransforms.py module available from the SPSS Community website.  Here is an example from the documentation in that module.

# interval-based lookups including a subgroup
# Lookups are performed against a dataset containing
# - one or more variables that define groups
# - two variables that define the lower and upper bounds of each interval
# - a variable whose value is returned for the interval
# The dataset used for the lookup must be sorted by the group variables and the lower bound
# No check is made that the order is correct
# Example with one group variable
#* lookup dataset.
#data list list /group (a8) keylow keyhigh value.
#begin data.
#groupone 1 3 1.5
#groupone 4 6 5
#groupone 7 10 100
#grouptwo 1 2 1.5
#grouptwo 5 10 7.5
#groupone 50 50 -50
#end data.
#dataset name lookup.

#data list list /group (a8) values.
#begin data.
#grouptwo 6
#groupone 1
#groupone 2
#groupone 3
#groupone 100
#grouptwo 1.5
#grouptwo 10
#grouptwo 6
#groupthree 99
#groupone 7
#grouptwo 20
#groupone 10
#groupone 50
#end data.
#dataset name looktheseup.
#dataset active looktheseup.
#spssinc trans result = result
#/initial "extendedTransforms2.vlookupgroupinterval('lookup', 'group', 'keylow', 'keyhigh', 'value')"
#/formula func(group, values).


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        Joris Kregting <[hidden email]>
To:        [hidden email],
Date:        02/03/2014 06:53 AM
Subject:        [SPSSX-L] merge
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

I want to merge 2 files. In the first one, each case has one zip code and one
house number. In the second files, each case has a zip code and a /range/ of
house numbers, and a variable which I a want to add at the first file.

For example:
A case in the first file has 1011AP (zip code) and 17 (house number).
This case has to match/merge with the case in the second file with 1011AP
(zip code) and the range of house numbers 1-23.

Is there a syntax for this merge-question?



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/merge-tp5724301.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