Removing rows between two values in SAS

Same as Richard, I don't quite understand what the filtering criteria are.

I could see one problem with your join. you used a.* in your select statement, but "b" and "f" as your dataset aliases. this would not work as no dataset have been assigned to alias "a".

Proper way would be as follow:

proc sql;
  create table all as 
  select b.* from B as b
  inner join F as f
  on b.id=f.id
  and b.date >= f.date;
quit;

However, even then, I don't believe inner join is the proper way to solve your problem. Do let us your filtering condition please?

I have a solution but it is not the most elegant (and might not cover corner cases.) If anyone else has a better solution please share.

First, to create the dataset in-case anyone else want to try it out:

Data work.have;
  input @01 ID 3.
        @05 Type $1.
        @07 Date date7.
        @18 Value $1.;
  format ID 3.
         Type $1.
         Date date11.
         Value $1.; 
  datalines;
001 F '02Jan18'n Y
001 B '03Jan18'n  
001 B '04Jan18'n Y
001 B '05Jan18'n  
001 B '06Jan18'n 
001 F '06Jan18'n Y
001 B '06Jan18'n 
001 B '07Jan18'n 
001 B '08Jan18'n Y
001 B '08Jan18'n 
001 B '09Jan18'n 
002 F '02Jan18'n Y
002 B '03Jan18'n 
002 B '04Jan18'n 
;
run;

Solution: I based on your edited suggestion of creating a flag variable.

Data Flag;
  set work.have;

  if Type = 'B' and Value = 'Y' then
    flag + 1;
  if Type = 'F' then
    flag = 0;

  if Value ne 'Y' and flag = 1 then delete;
run;

The flag variable is 0 by default.

The first IF-Then condition identifies the Type B ='Y' rows and flag them as 1, as well as retaining this flag for the subsequent rows.

The second IF-Then condition identifies the type='F' row and resets the Flag to 0

The Last If-Then condition drops all rows with Flag=1 except the first occurrence which are the Type B ='Y' rows.

I hope this applies to your problem.

The criteria for computing the state of a row as part of a contiguous sub-group (call it a 'run' of rows) within group ID are relatively simple, but a compromised state might occur or be indicated if some funny cases of data occur:

• two or more B Y before a F Y (extra 'run ending')
• two or more F Y before a B Y ('run starting' within a run)
• first row in group not F Y ('run starting' not first in group)

data want(drop=run_:);
  SET have;
  BY id;

  run_first = (type='F' and value='Y');
  run_final = (type='B' and value='Y');

  * set flag state at criteria for start of contiguous sub-group criteria;
  run_flag + run_first;

  if first.id and NOT run_flag then
    put 'WARNING: first row in group ' id= ' is not F Y, this may be incorrect';

  if run_flag > 1 and run_first then 
    put 'WARNING: an additional F Y before a B Y at row ' _n_;

  if run_flag then
    OUTPUT;

  if run_flag = 0 and run_final then 
    put 'WARNING: an additional B Y before a F Y at row ' _n_;

  * reset flag at criteria for contiguous sub-group;
  if last.id or run_final then 
    run_flag = 0;
run;