BookmarkSubscribeRSS Feed
KDang
Fluorite | Level 6
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!
4 REPLIES 4
Ksharp
Super User
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
Fluorite | Level 6
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
Super User
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
Fluorite | Level 6
Perfect, thanks again for your help

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1549 views
  • 0 likes
  • 2 in conversation