The prescribers data has physician's field where the physicians names prescribing the drug are present. We get this data in excel.At times a physician's name is given in a different way than usual.for example, John Smith is same as J Smith or James Hanna is given as James Hanna,MD.
In excel on a new work sheet , I have two columns old and new, old is John Smith and new is J Smith.
Now When I convert excel data to SAS, I would like to do a look up on the old physicians names and if there is mis match , I would like to update them with the old ones.If there is a new physician's name present for the current data, i would like to have him included in my data.
This type of data validation topic has been discussed previously - check the various forums archives. But basically you really need to have some "key variable" to uniquely identify a subject, given that there may be multiple John Smith individuals and what if, by chance, you have some who changes their name, maybe "Michael Smith" becomes "Michel Smith". This condition happens - really it does.
What you are proposing is somewhat unreasonable, regardless of the power in SAS for data manipulation and interpretation.
One option though is to come up with a "tag" identification column/variable which is derived from possibly the Last_Name and First_Name components, compressing out blanks, in order to build a comparison "key" for new, incoming data.
The COMPRESS function comes to mind, to be used with a data-scrubbing process when your incoming data is parsed and interpreted for the first time.