CODEWITHSUNDEEP
sasretaindatastep
user2941280
user2941280

Reputation: 295

SAS - Data Step using Retain?

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...

Upvotes: 0

Views: 246

Answers (2)

Chris J
Chris J

Reputation: 7769

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 ;

Upvotes: 0

John Chrysostom
John Chrysostom

Reputation: 3973

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;

Upvotes: 1

Related Questions