topic Re: Join two files based on name in SAS Programming

Join two files based on name

abdulla — Tue, 16 Mar 2021 09:30:15 GMT

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.

Re: Join two files based on name

PeterClemmensen — Tue, 16 Mar 2021 10:10:27 GMT

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.

Re: Join two files based on name

Sajid01 — Tue, 16 Mar 2021 13:51:45 GMT

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;

Re: Join two files based on name

abdulla — Tue, 16 Mar 2021 14:53:18 GMT

Thank you very much Sajid01.