Is there any way to run a command similar to excel vlookup function in SPSS

[Javier Meneses]

Is there any way to run a command similar to excel vlookup function in SPSS. I need to identify in the code column A the intermediate products that are defined in column b and c-


a       b       c
1       1       intermedios
2       5       intermedios
3       8       intermedios
4       15      intermedios
5       12      intermedios
6       33      intermedios
7       45      intermedios
8       20      intermedios
9       30      intermedios
10      100     intermedios
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
etc              ThanK!!

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

There is an equivalent function available through Python programmability that can be conveniently used with the SPSSINC TRANS extension command.

 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).


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




From:        Javier Meneses <[hidden email]>
To:        [hidden email],
Date:        10/19/2013 09:09 PM
Subject:        [SPSSX-L] Is there any way to run a command similar to excel              vlookup              function in SPSS
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Is there any way to run a command similar to excel vlookup function in SPSS. I need to identify in the code column A the intermediate products that are defined in column b and c-


a       b       c
1       1       intermedios
2       5       intermedios
3       8       intermedios
4       15      intermedios
5       12      intermedios
6       33      intermedios
7       45      intermedios
8       20      intermedios
9       30      intermedios
10      100     intermedios
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
etc              ThanK!!

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

[Richard Ristow]

At 11:14 PM 10/19/2013, Javier Meneses wrote:

>Is there any way to run a command similar to excel vlookup function
>in SPSS. I need to identify in the code column A the intermediate
>products that are defined in column b and c-
>
>a       b       c
>1       1      intermedios
>2       5       intermedios
>3       8       intermedios
>4       15      intermedios
>5       12      intermedios
>6       33      intermedios
>7       45      intermedios
>8       20      intermedios
>9       30      intermedios
>10      100     intermedios

What does column b do? You write as if the intermediate product is
defined by the *pair* of values in columns b and c. If so, Jon's
Python solution wouldn't work as given; Excel's VLOOKUP wouldn't,
either, because it looks in only one column.

If you want to look up a numeric code for each value on column c, and
store it in column a, here are two solutions, plus a possible third,
in native SPSS:

1.) If you have a small number of values to look up -- RECODE has the
advantage of being very fast, and the disadvantage of putting the
entire lookup table in the code:

RECODE c
    ('nut'    =  1)
    ('bolt    =  2)
    ('washer' =  3)
    (ELSE     = -1)
    INTO a.


2.) If you have a larger number of values, you can use MATCH FILES
lookup. This will handle any table size; the disadvantage is that you
must sort your data twice, and to do this your data must have some
variable (I'll call it 'Record_Key') that gives the order you want
your cases in. The following assumes that SPSS file or dataset
'LOOKUP' contains a variable 'c' with the names of all intermediate
products, and 'a' with their corresponding codes.

SORT CASES BY c.

MATCH FILES
    /TABLE=LOOKUP
    /FILE =*
    /BY c.

SORT CASES BY Record_Key.


3.) Not guaranteed to work, though I've succeeded with it: Create the
equivalent of an AUTORECODE template file with your codes and values,
and use AUTORECODE with that template. This also is very fast, but it
may be more difficult than the Python solution.

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

[David Marso]

And the result should look like what?
SPSS has MATCH FILES with a /TABLE subcommand.
You can also SORT and LAG.

Javier Meneses wrote

Is there any way to run a command similar to excel vlookup function in SPSS. I need to identify in the code column A the intermediate products that are defined in column b and c-


a       b       c
1       1       intermedios
2       5       intermedios
3       8       intermedios
4       15      intermedios
5       12      intermedios
6       33      intermedios
7       45      intermedios
8       20      intermedios
9       30      intermedios
10      100     intermedios
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
etc              ThanK!!

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

[Richard Ristow]

(Re-post -- I posted this with a shorter subject line, but of course
that broke thet thread)

At 11:14 PM 10/19/2013, Javier Meneses wrote:

>Is there any way to run a command similar to excel vlookup function
>in SPSS. I need to identify in the code column A the intermediate
>products that are defined in column b and c-
>
>a       b       c
>1       1      intermedios
>2       5       intermedios
>3       8       intermedios
>4       15      intermedios
>5       12      intermedios
>6       33      intermedios
>7       45      intermedios
>8       20      intermedios
>9       30      intermedios
>10      100     intermedios

What does column b do? You write as if the intermediate product is
defined by the *pair* of values in columns b and c. If so, Jon's
Python solution wouldn't work as given; Excel's VLOOKUP wouldn't,
either, because it looks in only one column.

If you want to look up a numeric code for each value on column c, and
store it in column a, here are two solutions, plus a possible third,
in native SPSS:

1.) If you have a small number of values to look up -- RECODE has the
advantage of being very fast, and the disadvantage of putting the
entire lookup table in the code:

RECODE c
    ('nut'    =  1)
    ('bolt    =  2)
    ('washer' =  3)
    (ELSE     = -1)
    INTO a.


2.) If you have a larger number of values, you can use MATCH FILES
lookup. This will handle any table size; the disadvantage is that you
must sort your data twice, and to do this your data must have some
variable (I'll call it 'Record_Key') that gives the order you want
your cases in. The following assumes that SPSS file or dataset
'LOOKUP' contains a variable 'c' with the names of all intermediate
products, and 'a' with their corresponding codes.

SORT CASES BY c.

MATCH FILES
    /TABLE=LOOKUP
    /FILE =*
    /BY c.

SORT CASES BY Record_Key.


3.) Not guaranteed to work, though I've succeeded with it: Create the
equivalent of an AUTORECODE template file with your codes and values,
and use AUTORECODE with that template. This also is very fast, but it
may be more difficult than the Python solution.

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