Help using Base SAS procedures

Multiple Merge

Reply
Contributor
Posts: 27

Multiple Merge

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!
Super User
Posts: 9,681

Re: Multiple Merge

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

Contributor
Posts: 27

Re: Multiple Merge

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.
Super User
Posts: 9,681

Re: Multiple Merge

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
Contributor
Posts: 27

Re: Multiple Merge

Perfect, thanks again for your help
Ask a Question
Discussion stats
  • 4 replies
  • 108 views
  • 0 likes
  • 2 in conversation