topic Join on first and last name in SAS Programming

Join on first and last name

Walternate — Tue, 04 Aug 2015 14:57:50 GMT

Hi,

I have two datasets (one from Excel, one from SAS) that each have first and last name variables. I am trying to join the two datasets on first name and last name:

proc sql;

create table want as

select * from have1 a

inner join have2 b

on (a.have1_first_name=b.have2_first_name) and (a.have1_last_name=b.have1_last_name);

quit;

The join worked on about 2/3 of the dataset, but not the other 1/3. The problem is that I can't figure out why the join isn't working on the remaining 1/3. I've looked some non-joiners up by hand and there are no initials or capitalization issues or anything like that--the values look identical to me, but still aren't recognized as identical by SAS. I need some ideas on how to troubleshoot and figure out the disparity between the values so I can complete the join.

Thanks!

Re: Join on first and last name

Reeza — Tue, 04 Aug 2015 15:05:25 GMT

Trim and set cases and recheck would be my suggestion:

proc sql;

create table want as

select * from have1 a

inner join have2 b

on (upper(trim(a.have1_first_name))=upper(trim(b.have2_first_name))) and (upper(trim(a.have1_last_name))=upper(trim(b.have1_last_name)));

quit;

Re: Join on first and last name

RW9 — Tue, 04 Aug 2015 15:12:02 GMT

I would agree, though

on upper(cats(a.have1_first_name,a.have1_last_name))=upper(cats(b.have1_first_name,b.have1_last_name))

Should also work.