DATA Step, Macro, Functions and more

Delete rows based on condition of previous

Reply
Frequent Contributor
Posts: 143

Delete rows based on condition of previous

The enclosed spreadsheet contains sample data.  For example row 1 is QQQQ and row 3 is BBBB.  I want to do the following

 

1. If BBB occurs after QQQ then delete BBB

2. if CCC occurs before DDD then delete CCC

 

I am looking for logic to delete rows based on condtions instead of the usual columns

 

Sample enclosed

Trusted Advisor
Posts: 1,118

Re: Delete rows based on condition of previous

Hello @Q1983,

 

Try this:

 

data have;
input Loan Code $ Date :mmddyy.;
format date mmddyy10.;
cards;
1 QQQQ 5/1/2015 
2 AAAA 2/1/2016 
3 BBBB 1/15/2016 
4 CCCC 1/16/2016 
5 DDDD 1/17/2016
;

data want;
do _n_=1 by 1 until(eof);
  set have end=eof;
  if code='DDDD' then last_d=_n_; /* remember obs. no. of last 'DDDD' */
end;
eof=0;
do _n_=1 by 1 until(eof);
  set have end=eof;
  if code='QQQQ' then del_b=1;    /* set flag "delete 'BBBB'" */
  if ~(code='CCCC' & _n_<last_d | code='BBBB' & del_b) then output;
end;
drop last_d del_b;
run;

Assumptions:

 

  1. "If BBB ..." should read "If BBBB ..." etc.
  2. "before" and "after" refer to the order of observations in dataset HAVE.
  3. Dataset HAVE is already sorted appropriately.
  4. "before" and "after" do not mean "immediately before" and "immediately after", resp.
  5. Your real dataset HAVE does not contain BY groups only within which the rules were to be applied.

Please adapt the code appropriately if any of these assumptions do not hold and post further questions if you encounter difficulties.

Frequent Contributor
Posts: 143

Re: Delete rows based on condition of previous

Posted in reply to FreelanceReinhard

Thanks

Frequent Contributor
Posts: 143

Re: Delete rows based on condition of previous

Posted in reply to FreelanceReinhard

thanks

Respected Advisor
Posts: 3,156

Re: Delete rows based on condition of previous

Another alternative ( input data shamelessly stolen from @FreelanceReinhard). This approach does not required data to be sorted, however, it assumes 'before'/'after' is chronological instead of positional. 

data have;
input Loan Code $ Date :mmddyy.;
format date mmddyy10.;
cards;
1 QQQQ 5/1/2015 
2 AAAA 2/1/2016 
3 BBBB 1/15/2016 
4 CCCC 1/16/2016 
5 DDDD 1/17/2016
;

data want;
set have(in=up) have(in=down);
retain last_d first_q;
if up and code='QQQQ' then first_q=min(date,first_q);
if up and code='DDDD' then last_d=max(date,last_d);
if not up;
if not (code='BBBB' and date > first_q);
if not (code='CCCC' and date < last_d);
drop last_d first_q;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 847 views
  • 1 like
  • 3 in conversation