topic Re: Delete all records until reaching first condition by group? in SAS Programming

Delete all records until reaching first condition by group?

Krueger — Fri, 15 Nov 2019 22:38:04 GMT

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;

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

novinosrin — Fri, 15 Nov 2019 22:51:41 GMT

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;

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

hashman — Fri, 15 Nov 2019 22:51:36 GMT

@Krueger:

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.

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

novinosrin — Fri, 15 Nov 2019 23:03:43 GMT

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!

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

Krueger — Fri, 15 Nov 2019 23:13:05 GMT

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