05-09-2015 02:22 PM
I am new to SAS and have had no training, so please be specific in your response.
I have a table of 46,610 Terminated employees, but some employee’s are listed multiple times with different Termination Dates due to rehire situations.
I only want the record with the most recent Termination Date.
I've tried for hours to write a procedure, but just haven't been able to figure out how. Any help will be appreciated.
I've created the SAS table from a tab-limited file. Here's my code to create the table.
I've sorted the table by Person_Number and Termination_Date (in decending order)
Now I need the code to select only the unique Person_Number records with the most recent Termination_Date.
All fields are required in the output.
Thanks in advance for your help.
create table ORtmpTerm as
05-09-2015 02:59 PM
It looks like it worked, but do I need to be concerned with the message "NOTE: The query requires remerging summary statistics back with the original data"?
2136 Proc Sort Data = ORwfdTerm;
2137 By Person_Number decending Termination_Date;
NOTE: There were 46610 observations read from the data set WORK.ORWFDTERM.
NOTE: The data set WORK.ORWFDTERM has 46610 observations and 173 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 2.04 seconds
cpu time 1.34 seconds
2140 proc sql;
2141 select *
2142 from ORwfdTerm
2143 group by Person_Number
2144 having max(Termination_Date);
2147 /* Select Max Date */
2148 proc sql;
2149 create table ORtmpTerm2 as
2150 select *
2151 from ORwfdTerm
2152 group by Person_Number
2153 having Termination_Date = max(Termination_Date);
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.ORTMPTERM2 created, with 45681 rows and 173 columns.
05-09-2015 02:43 PM
Without sample data hard to suggest. Try this
create table want as
select * from ORwfdTerm
group by Person_Number
05-11-2015 06:41 AM
Hi , you can also use the first. last. approach as below .
proc sort data = ORwfdTerm ;
By Person_Number decending Termination_Date;
data want ;
set have ;
By Person_Number decending Termination_Date;;
if first.Person_Number ;
Need further help from the community? Please ask a new question.