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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.