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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.