SAS - Data Step using Retain?

0
votes

I have the following data...

acct    seq    start            end
1111     A     01/01/2014       01/31/2014
1111     A     02/01/2014       02/28/2014

I need to exclude records where the account has a start date that is 1 day after the end date. So the above record would be excluded because it has an end date of 01/31/2014 on the first observation and a begin date of 02/01/2014 on the follwoing observation.

I think I need to use Retain to accomplish this but I'm not quite sure how to write it. Any help would be appreciated...

2
sasretaindatastep
Do you want to exclude all records where acct = 1111 or just the individual record where start date and end date are 1 day apart? - Zfunk

2 Answers

1
votes

Proc SQL solution... assuming you are using SAS dates...

proc sql;
    create table excludes as
    select distinct acct
    from data as one
    left join data as two
    on one.acct=two.acct and one.end=two.start-1
    where two.start is not null;

    create table filtered as
    select *
    from data
    where acct not in
    (
        select *
        from excludes
    );
quit;
0
votes

Datastep solution, assuming your data is grouped by acct and seq (and your dates are SAS-dates)

data want ;
  set have ;
  by acct seq ;

  retain prevdt . ;

  prevdt = end ;

  if first.seq then output ;
  else do ;
    if start > sum(prevdt,1) then output ;
    prevdt = end ;
  end ;

  drop prevdt ;
run ;