Search code examples
datesasretain

Calculate duration between dates by group using SAS

I am trying to calculate how long a child has been in foster care. However, I am having some issues. My data should look like something below:

enter image description here

For each individual (ID) I need to calculate the duration (end_date-start_date). However, I also need to apply a rule that states that if there are less than 5 days between the end date and the start date within the same type of foster care, it should be considered as one consecutively placement. If there are more than five days between the end date end the start date within the same type of foster care for the same individual, it is a new placement. If it is a new type of foster care, it is a new placement. The variable “duration” is how, it is supposed to be calculated.

I have tried the following code, but it doesn't work the proper way + I don't know how to apply my "five day"-rule.

Proc sort data=have out=want;
by id type descending start_date;
run;

Data want;
set want;
by id type;
retain last_date;
if first.id or first.type then do;
   last_date=end_date;
end;  
if last.id or last.type then duration=(end_date-start_date);
run;

Any help is much appreciated!

Solution

  • Using a bunch of retain statements here to achieve this:

    data want;
  set have;

  by id ;

  retain true_sd prev_ed prev_type;

  if first.id then call missing(prev_type);

  if type ~= prev_type then do;
     true_sd = sd;
     call missing(prev_ed);
     call missing(prev_type);
  end;

  if sd - prev_ed > 5 then true_sd = sd;

  duration = ed - true_sd;
  output;

  prev_type = type;
  prev_ed = ed;

  format sd ed true_sd prev_ed date.;


 run;

    (assuming type and id are numeric here. ed is end_date, sd is start_date)