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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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