topic Re: Multiple Merge in SAS Procedures

Multiple Merge

KDang — Fri, 11 Mar 2011 15:03:59 GMT

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!

Re: Multiple Merge

Ksharp — Mon, 14 Mar 2011 03:43:15 GMT

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

Re: Multiple Merge

KDang — Tue, 15 Mar 2011 14:25:31 GMT

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.

Re: Multiple Merge

Ksharp — Wed, 16 Mar 2011 01:40:46 GMT

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

Re: Multiple Merge

KDang — Wed, 16 Mar 2011 14:41:07 GMT

Perfect, thanks again for your help