topic Re: Help with merging via names in SAS Programming

Help with merging via names

SeanZ — Sat, 12 Oct 2013 22:58:40 GMT

Hi,

I have two data sets. I want to merge them with company names. But there are some variations in the company names. For example, mgmt vs. management, company vs. co, limited vs. ltd, and vs. &, etc. Also, there might be extra blanks in the names. Can someone tell me how to deal with this issue? And some reference I can use to do this merge? THanks.

Re: Help with merging via names

DR_Majeti — Sun, 13 Oct 2013 08:02:43 GMT

Hi SeanZ,

there will be a supporting variable for company name in numeric (i think) if it is in this cases. check for that variable which will have some code for each company;

if there no such thing just make it through hard coding like,

if companyname='management' then companycode=1

in both datasets then you can merge them by this new variable created.

Durga.

Re: Help with merging via names

Scott_Mitchell — Sun, 13 Oct 2013 22:18:20 GMT

I would suggest you undertake some analysis to identify and replace strings such as 'co' with 'company' and the like. SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition may help you replace these.

As far as merging by name is concerned you could sing a fuzzy merge using COMPGED http://www.nesug.org/proceedings/nesug07/ap/ap23.pdf. This will require some analysis and feel for your data, but I have has success with it in the past.

Re: Help with merging via names

Mit — Sun, 13 Oct 2013 23:37:38 GMT

You need to clean the data. But that is not easy. So there are two ways.

1. sort and summarise the data by the company name. So you should be able to see the differences and then use this result to clean the company names using tranwrd function applying to the main data.

company=tranwrd(compay,'mgmt','Management');

company=tranwrd(compay,'Mgmt','Management');

company=tranwrd(compay,'co','Comapny');

company=tranwrd(compay,'Co','Comapny');

You can use all of the above in the same data step.

after the cleaning again summarise and check and clean. You might have to summarise few times until the entire data is cleaned.

2. Depending on you data, you can also follow the above procedure but before doing that you can split the company name in two or three parts using the scan function and then summarise on those words. This way you should be able to find the different spellings and clean them.