topic Re: Delete rows based on condition of previous in SAS Programming

Delete rows based on condition of previous

Q1983 — Wed, 04 May 2016 19:07:15 GMT

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

Re: Delete rows based on condition of previous

FreelanceReinh — Wed, 04 May 2016 20:44:11 GMT

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:

"If BBB ..." should read "If BBBB ..." etc.
"before" and "after" refer to the order of observations in dataset HAVE.
Dataset HAVE is already sorted appropriately.
"before" and "after" do not mean "immediately before" and "immediately after", resp.
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.

Re: Delete rows based on condition of previous

Haikuo — Wed, 04 May 2016 22:48:13 GMT

Another alternative ( input data shamelessly stolen from @FreelanceReinh). 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;

Re: Delete rows based on condition of previous

Q1983 — Thu, 05 May 2016 02:23:27 GMT

Thanks

Re: Delete rows based on condition of previous

Q1983 — Thu, 05 May 2016 02:24:03 GMT

thanks