left join (match) to fill in variables

[progster]

dear all I have to match two db, in order to obtain a left join.

I'm going to create some fake data in order to create an easy example.

*my aim is obtaining a final a dataset like this:
1,00 Beth
2,00 Bob
3,00 Barb
4,00 Andy
5,00 Al
6,00 Ann
7,00 Pete
8,00 Pam
9,00 Phil.

I would like to know if there is a more efficient way to obtain it, may be in a single step?

*what I managed:

data list list / id * name (A4).
begin data
1 "Beth"
2 "Bob"
3 "Barb"
4
5
6
7 "Pete"
8 "Pam"
9 "Phil"
end data.
dataset name dsT1.
sort cases by ID.


data list list / id * name (A4).
begin data
4 "Andy"
5 "Al"
6 "Ann"
end data.
dataset name dsT2.
sort cases by ID.



rename variables name=name2.


MATCH FILES /FILE=*
  /TABLE='dsT2'
  /BY id.
EXECUTE.


MISSING VALUES name (" ").

do if name=(" ").
compute name=name2.
end if.
EXECUTE.

DELETE VARIABLES name2.

[Andy W]

In this particular example you could use the UPDATE command.

**************************************************.
data list list / id * name (A4).
begin data
1 "Beth"
2 "Bob"
3 "Barb"
4 ""
5 ""
6 ""
7 "Pete"
8 "Pam"
9 "Phil"
end data.
dataset name dsT1.
sort cases by ID.

data list list / id * name (A4).
begin data
4 "Andy"
5 "Al"
6 "Ann"
end data.
dataset name dsT2.
sort cases by ID.

DATASET ACTIVATE dsT1.
UPDATE FILE = *
  /FILE = "dsT2"
  /BY id.
**************************************************.

[Bruce Weaver]

I believe the UPDATE command is your friend!  Try this:

data list list / id * name (A4).
begin data
1 "Beth"
2 "Bob"
3 "Barb"
4
5
6
7 "Pete"
8 "Pam"
9 "Phil"
end data.
dataset name dsT1.
sort cases by ID.

data list list / id * name (A4).
begin data
4 "Andy"
5 "Al"
6 "Ann"
end data.
dataset name dsT2.
sort cases by ID.

UPDATE FILE='dsT1'
 /FILE='dsT2'
 /BY=ID.
EXECUTE.
DATASET NAME dsT1updated.
LIST.

*my aim is obtaining a final a dataset like this:
1,00 Beth
2,00 Bob
3,00 Barb
4,00 Andy
5,00 Al
6,00 Ann
7,00 Pete
8,00 Pam
9,00 Phil.

progster wrote

dear all I have to match two db, in order to obtain a left join.

I'm going to create some fake data in order to create an easy example.

*my aim is obtaining a final a dataset like this:
1,00 Beth
2,00 Bob
3,00 Barb
4,00 Andy
5,00 Al
6,00 Ann
7,00 Pete
8,00 Pam
9,00 Phil.

I would like to know if there is a more efficient way to obtain it, may be in a single step?

*what I managed:

data list list / id * name (A4).
begin data
1 "Beth"
2 "Bob"
3 "Barb"
4
5
6
7 "Pete"
8 "Pam"
9 "Phil"
end data.
dataset name dsT1.
sort cases by ID.


data list list / id * name (A4).
begin data
4 "Andy"
5 "Al"
6 "Ann"
end data.
dataset name dsT2.
sort cases by ID.



rename variables name=name2.


MATCH FILES /FILE=*
  /TABLE='dsT2'
  /BY id.
EXECUTE.


MISSING VALUES name (" ").

do if name=(" ").
compute name=name2.
end if.
EXECUTE.

DELETE VARIABLES name2.