Hello @ccaudillo100,
If there are no duplicate "effective dates" within an ID, you can use a HAVING clause in PROC SQL to select the most recent FullName per ID in one step:
proc sql;
create table Name1 as
select ID, FullName
from xx.xx where NameID = 1
group by ID
having Effective_Date=max(Effective_Date);
quit;
However, if there are duplicate "effective dates" within an ID, two different cases are possible:
The names (FullName) in a group of records with the same ID and latest Effective_Date are always identical. In this case you could insert the DISTINCT keyword into the SELECT statement:
select distinct ID, FullName
to avoid duplicate observations in dataset Name1.
The names are not identical, i.e., they are inconsistent. In this case your existing DATA step code would select only one of the different names, you wouldn't even notice the inconsistencies and it would depend on the order of observations in dataset Name which of the names would be selected. Given that dataset Name is created by PROC SQL, the selection might be hardly predictable. In contrast, the PROC SQL step suggested above (with DISTINCT inserted) would select all of the inconsistent names, if any, so you could use, e.g., a (post-processing) DATA step like this
data incons;
set Name1;
by ID;
if ~(first.ID & last.ID);
run;
to examine the inconsistencies or to confirm that there are none.
... View more
