BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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.

Q1983
Lapis Lazuli | Level 10

Thanks

Q1983
Lapis Lazuli | Level 10

thanks

Haikuo
Onyx | Level 15

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1942 views
  • 1 like
  • 3 in conversation