topic Re: how to delete the first observations by group in SAS Programming

how to delete the first observations by group

AmirSari — Mon, 17 Aug 2020 08:32:40 GMT

I am trying to delete the first observations by group. Here is a sample of my dataset.

ID	year
1	1995
1	1995
1	1996
1	1996
1	1997
1	1997
1	1997
2	2000
2	2000
2	2000
2	2000
2	2001
2	2001
2	2001
2	2001

What I want:

ID	year
1	1996
1	1996
1	1997
1	1997
1	1997
2	2001
2	2001
2	2001
2	2001

Thank you!

Re: how to delete the first observations by group

andreas_lds — Mon, 17 Aug 2020 08:49:16 GMT

Assuming the data is sorted by ID and year:

data want;
  set have;
  by  id;

  if not first.id;
run;

Code is untested.

EDIT:

The code won't create the requested dataset, i must have skipped that id and year combined identify groups. Please try:

data want;
   set have;
   by id;
   
   retain drop_year;
   drop drop_year;
   
   if first.id then drop_year = year;
   if year ^= drop_year;
run;

Re: how to delete the first observations by group

PeterClemmensen — Mon, 17 Aug 2020 09:28:47 GMT

data have;
input ID year;
datalines;
1 1995
1 1995
1 1996
1 1996
1 1997
1 1997
1 1997
2 2000
2 2000
2 2000
2 2000
2 2001
2 2001
2 2001
2 2001
;

data want;
   set have;
   by id;
   if first.id then _iorc_ = year;
   if year ne _iorc_;
run;

Result:

Re: how to delete the first observations by group

Ksharp — Mon, 17 Aug 2020 11:39:45 GMT

data have;
input ID year;
datalines;
1 1995
1 1995
1 1996
1 1996
1 1997
1 1997
1 1997
2 2000
2 2000
2 2000
2 2000
2 2001
2 2001
2 2001
2 2001
;
data want;
 set have;
 by id year;
 if first.id then n=0;
 n+first.year;
 if n ne 1;
run;

Re: how to delete the first observations by group

novinosrin — Mon, 17 Aug 2020 11:40:51 GMT


data have;
input ID year;
datalines;
1 1995
1 1995
1 1996
1 1996
1 1997
1 1997
1 1997
2 2000
2 2000
2 2000
2 2000
2 2001
2 2001
2 2001
2 2001
;

proc sql;
 create table want as
 select * 
 from have
 group by id
 having min(year) ne year
 order by id,year;
quit;

Re: how to delete the first observations by group

AmirSari — Mon, 17 Aug 2020 16:48:22 GMT

Thanks everyone for your help!