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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.