Join two files based on name

[abdulla]

Hi, I have attached two files. I have thousands of data. I want to join two files based on the name. In one file full name is given and in another file names are split based on forename, surname etc. Can any one please help me? I have given here some examples of my data. There are a lot of matches in my original complete dataset. In the example files, matches may not be high as I trimmed the files. 

[PeterClemmensen]

Please post sample data in a usable form, preferably as a data step. Also, please provide a clear description of your problem and your desired result.

Most users here will not download files from the community.

[Sajid01 · Accepted solution]

1.Based upon your data I have created a sample code for join. Modify depending upon your needs.

2.Created two variable ename1 and ename2 solely to serve the purpose of join. They can be deleted later on.
2..In the proc sql  step I have retained the new variables ename1 and ename2 to show that the match works.
  I expect that it will be modified to include the variable that are needed and drop the ename's .
Let me know if you have questions.

proc import file="path_to_avs.xls" out=avs dbms=xls replace;
getnames=yes;
run;
proc import file="path_to_avs1.xls" out=avs1 dbms=xls replace;
getnames=yes;
run;
/*Remove the dots and commas*/
data avs01;
set avs;
ename1=compress(translate(EXEC_FULLNAME,'','.,'));
run;

data avs10;
set avs1;
ename2=compress(cat(Forename1,forename2,Surname));
run;

proc sql;
create table joined_table as
select a.ename1,b.ename2 from avs01 a, avs10 b
where a.ename1=b.ename2;
quit;

[abdulla]

Thank you very much Sajid01.