Skip to content
Advertisement

how to delete first occurrence of each group in sas table?

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);
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement