Pyrite | Level 9

## Delete all records until reaching first condition by group?

Hi all, I have a far more complex dataset but below is some sample data. Basically I am needing to iterate through the list of ID's and determine the FIRST instance of ACTION  = 2. I need all data after this first instance BY ID to be retained and anything before that deleted. Below is what I've tried any pointers on where I'm going wrong?

Note* My real data is already sorted and the dates actually appear, I don't believe in this instance they hold any relevance but you'll realize they do not populate correctly.

``````
DATA HAVE;
INPUT ID DATE ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;

DATA WANT;
INPUT ID DATE ACTION;
DATALINES;
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;
RUN;

DATA WANT;
SET HAVE;
DO UNTIL (ID = ID AND ACTION = 2);
DELETE;
END;
RUN;``````

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Delete all records until reaching first condition by group?

``````DATA HAVE;
INPUT ID DATE :\$10. ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;

data want;
do _n_=0 by 0 until(last.id);
set have;
by id;
if ACTION=2 then _n_=1;
if _n_ then output;
end;
run;``````

/*Or*/

``````data want;
do until(last.id);
set have;
by id;
_iorc_+ ACTION=2;
if _iorc_ then output;
end;
_iorc_=0;
run;
``````

4 REPLIES 4
Tourmaline | Level 20

## Re: Delete all records until reaching first condition by group?

``````DATA HAVE;
INPUT ID DATE :\$10. ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;

data want;
do _n_=0 by 0 until(last.id);
set have;
by id;
if ACTION=2 then _n_=1;
if _n_ then output;
end;
run;``````

/*Or*/

``````data want;
do until(last.id);
set have;
by id;
_iorc_+ ACTION=2;
if _iorc_ then output;
end;
_iorc_=0;
run;
``````

Pyrite | Level 9

## Re: Delete all records until reaching first condition by group?

Thank you, I've only tested this on ~10 records of my live data but it seems to be holding up so far.

Ammonite | Level 13

## Re: Delete all records until reaching first condition by group?

``````data have ;
input id date: \$9. action ;
cards ;
1  jan012019  1
1  feb012019  1
1  mar012019  2
2  jan012019  2
2  feb012019  1
;
run ;

data want ;
do until (last.id) ;
set have ;
by id ;
if action = 2 then _n_ = 0 ;
if _n_ = 0 then output ;
end ;
run ;
``````

Kind regards

Paul D.

Tourmaline | Level 20

## Re: Delete all records until reaching first condition by group?

Hi @Krueger  One more fun before I catch the bus to get home, If your dates are numeric SAS dates and it looks like some fun to use them in proc sql 🙂

``````
DATA HAVE;
INPUT ID DATE :date9. ACTION;
format date date9.;
DATALINES;
1 01JAN2019 1
1 01FEB2019 1
1 01MAR2019 2
2 01JAN2019 2
2 01FEB2019 1
;RUN;

proc sql;
create table want as
select *
from have
group by id
having date>=min(ifn(action=2,date,.))
order by id,date;
quit;``````

Have a good one and a great weekend. Ciao!

Discussion stats
• 4 replies
• 1436 views
• 2 likes
• 3 in conversation