Get names

[SASPhile]

I have a table phy1 where I have physicians names column is pnames

I have a another table phy2 where I have two columns pnames and pnames_valid.


say for example:
Table phy1:(column Pname)

James Bolton,MD
James Bolton
James H Bolton, MD
J Bolton
J H Bolton, MD
Huang Ho


Table Phy2:(pname and pname_valid)

James Bolton,MD J Bolton
James Bolton J Bolton
James H Bolton, MD J Bolton
J H Bolton J Bolton
J H Bolton, MD J Bolton


So the task is: phy1 is my master table and phy2 is look up table.
if phy1.pname is same as phy2.pname then we need to return phy2.pname_valid names else retain values from phy1.pname.

[sbb]

Your prior post on 4/24 appears similar to this one, although at least the quest is a bit more specific. Some opportunities are a DATA step approach using PROC FORMAT for your look-up, or PROC SQL.

There were several search-matches I found on the SAS support http://support.sas.com/ website when using the Google advanced argument below:

master table look up site:sas.com


Scott Barry
SBBWorks, Inc.

http://support.sas.com/forums/thread.jspa?messageID=20256

[deleted_user]

You could use the hash object. Here is some code. Note this looks for an exact character match.

data one;
length pname $30;
pname="James Bolton,MD";
output;
pname="James Bolton";
output;
pname="James H Bolton, MD";
output;
pname="J Bolton";
output;
pname="J H Bolton, MD";
output;
pname="Huang Ho";
output;
;
run;

data two;
length pname $30 pname_valid $30;
pname="James Bolton,MD";
pname_valid="J Bolton";
output;
pname="James Bolton";
pname_valid="J Bolton";
output;
pname="James H Bolton MD";
pname_valid="J Bolton";
output;
pname="J H Bolton";
pname_valid="J Bolton";
output;
pname="J H Bolton, MD";
pname_valid="J Bolton";
output;
run;

data final (drop=k rename=(pname_valid=final_list));
length pname_valid $30 ;
if _n_=1 then do;
declare hash h(dataset: "two"); /* Define the Hash */
h.definekey("pname"); /* Define the Key */
h.definedata("pname_valid");
h.defineDone(); /* Loads data into Hash Object */
call missing (pname_valid);
end;
set one;
if missing(pname_valid) then pname_valid=pname;
k=h.find();
run;

proc print;
run;