Multiple Merge

[KDang]

Hi,

This is a slightly complex merge i'm having trouble with.

File1: contains Postalcode and City
- main file i'd like to attach a Segment to.
File2: contains PostalCode, City, Segment
File3: contains PostalCode, Segment

1. I need to merge Segment to File1 with File2 by PostalCode AND (sounds like) City
2. For everyone that DID NOT match up with File2 I would need to match up with File3 by PostCode only.
3. My final file should be the base of File1 containing PostalCodes, City and Segment

Thanks for your help!

[Ksharp]

data file1;

 post=1;city='a';output;

 post=2;city='b';output;

run;

data file2;

 post=1;city='a';seg='x';

run;

data file3;

 post=2; seg='y';

run;

proc sql ;

 create table temp as

  select a.post as post ,a.city as city,b.seg as seg

   from file1 as a left join file2 as b on a.post=b.post and a.city=b.city;

 create table want as

  select a.post as post ,a.city as city,coalesce(a.seg,b.seg) as seg

   from temp as a left join file3 as b on a.post=b.post;

quit;
[pre]




Ksharp
[/pre] Message was edited by: Ksharp

[KDang]

thanks,
is there away to match on city that 'soundslike'?
File1 is a customer file the variable City is an open-ended response, so there are many spelling errors. While File2 is the actual correct spelling.

[Ksharp]

Hi.
Yes. You need special operator
[pre]
where Name =* ’Peter’;
[/pre]
which means name sound likes Peter.

Or need function spedis( ) which also can fuzz match variable.



Ksharp

[KDang]

Perfect, thanks again for your help