CODEWITHSUNDEEP
sasretain
PinkyL
PinkyL

Reputation: 361

SAS how to carry over or retain value from row to row for comparison based on condition

I have some data that looks like this:

ID   linker    dis_date    lag_disdate  days_supply disdiff  length
A    000 ddd   2/7/2014                   30           .     62
A    000 ddd   3/7/2014     2/7/2014      30           26    62 
A    000 ddd   4/16/2014    3/7/2014      30           38    62
A    000 ddd   5/19/2014    4/16/2014     30           31    62
A    000 ddd   7/17/2014    5/19/2014     30           57    62
A    000 ddd   9/11/2014    7/17/2014     30           54    62

The logic is that if the disdiff is less than length, the date1 would be the previous disdate or lag_disdate but date2 would be the current dis_date minus one. However this is compared from each row to the next row. So the ideal outcome would look like this:

ID  date1      date2       disdate
A                          2/7/2014                        
A   2/7/2014   3/6/2014    3/7/2014
A   2/7/2014   4/15/2014   4/16/2014
A   2/7/2014   5/18/2014   5/19/2014
A   2/7/2014   7/16/2014   7/17/2014
A   2/7/2014   9/10/2014   9/11/2014

I tried to use a DoW loop to achieve this:

data test5;

do until (last.ID);

set test4;
by flag ID;
if first.ID=0 then do;
if disdiff <= length then date1=lag_disdate;
end;
output;
end;
run;

But I'm not sure how to edit it so that the 2/7/2014 is retained for each row. I did try to use retain but all the date1 still were equal to each lag_disdate, not the 2/7/2014.

Update: if disdiff is > length, date1 would be the dis_date for the record, not the previous date1.

Upvotes: 0

Views: 1060

Answers (1)

Chris J
Chris J

Reputation: 7779

You haven't said what the desired outcome is when disdiff > length. Nonetheless, you're almost there, try this :

data want ;
  set have ;
  by ID ;
  retain lag_disdate ;
  if first.ID then lag_disdate = dis_date ;
  else do ;
    if disdiff <= length then date1 = lag_disdate ;
    date2 = dis_date - 1 ;
  end ;

  format date1 date2 mmddyy10. ;
run ;

Upvotes: 1

Related Questions