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
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:
Please adapt the code appropriately if any of these assumptions do not hold and post further questions if you encounter difficulties.
Thanks
thanks
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.