Look Up

[SASPhile]

Hi ,
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.

Thanks for your time.
SASPhile,

[sbb]

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.


Scott Barry
SBBWorks, Inc.