I have data as follows:
ID,date 1,27-08-2020 1,28-08-2020 1,30-08-2020 2,05-08-2020 3,06-08-2020 3,10-08-2020
I have to delete first occurrence for each ID group. so that data becomes
ID,date 1,28-08-2020 1,30-08-2020 2,05-08-2020 3,10-08-2020
How to do that in sas/sql.
Advertisement
Answer
You can use a correlated subquery:
select t.* from t where t.date > (select min(t2.date) from t t2 where t2.id = t.id);
You can also incorporate this idea into a delete
:
delete from t where t.date = (select min(t2.date) from t t2 where t2.id = t.id);