Identify back-to-back dates in SAS

user_13142644 0
Viewed 37

I have a dataset that looks like this:

ID   start_date   end_date
1    01/01/2022   01/02/2022
1    01/02/2022   01/05/2022
1    01/06/2022   01/07/2022
2    01/09/2019   01/22/2022
2    06/07/2014   09/10/2015
3    11/10/2012   02/01/2013

I am trying to create a dummy indicator to show events that are back-to-back. So far, I have been able to do the following:

data df_1;
    set df_2;
    by ID end_date;
    lag_epi_e = lag(end_date);
    if not (first.ID) then do;
    date_diff= start_date- lag(end_date);
    end;
    format lag_epi_e date9.;
run;

The issue with this code is that it will create an indicator to show that events are back to back but is does not create an indicator for the first event, only the follow up events. Here is an example of how it looks below:

ID   start_date   end_date     b2b_ind
1    01/01/2022   01/02/2022   0
1    01/02/2022   01/05/2022   1
1    01/06/2022   01/07/2022   1

How can I rewrite the code so that all events take on an indicator of 1 when they are back-to-back?

sas
3 Answers

Do you want 1 at first record as well?

If so you can set that, but what happens if the next record set is not back to back? May help to show your expected output.

Note you should also use the calculated lag variable outside the IF statement otherwise you'll get unexpected results.

data df_1;
    set df_2;
    by ID end_date;
    lag_epi_e = lag(end_date);
    if not (first.ID) then do;
    date_diff= start_date- lag_epi_e;
    end;
    else if first.id then date_diff=1;
    format lag_epi_e date9.;
run;
user_19195830

In your case, you'll want to check if both a leading and lagging event are butted up together. Since lead is not a function in SAS, you can use one of the many ways to accomplish it. My favorite is from this SGF paper: Calculating Leads (and Lags) in SAS®: One Problem, Many Solutions

Let's add a lead to your data. This code is doing three things:

  1. Opening up your dataset df_1 in the "background"
  2. Fetching the n + 1th observation of start_date and saving it to a variable
  3. Setting it to missing if we're on the last id

Code:

data want;
    set df_1;
    by ID end_date;
    retain _dsid_;

    if(_N_ = 1) then _dsid_ = open("have");
    _lead_rc_ = fetchobs(_dsid_, _N_+1);
    
    lead_start_date = getvarn(_dsid_, varnum(_dsid_, "start_date"));
    lag_end_date    = lag(end_date);

    if(first.id) then call missing(lag_end_date);
    if(last.id) then call missing(lead_start_date);

    b2b_ind = (   (0 LE (lead_start_date - end_date) LE 1) 
               OR (0 LE (start_date - lag_end_date) LE 1)
              );
    
    drop _lead_rc_ _dsid_;

    format lead_start_date lag_end_date mmddyy10.;
run;

Output:

id start_date   end_date    lead_start_date lag_end_date    b2b_ind
1  01/01/2022   01/02/2022  01/02/2022      .               1
1  01/02/2022   01/05/2022  01/06/2022      01/02/2022      1
1  01/06/2022   01/07/2022  .               01/05/2022      1
2  06/07/2014   09/10/2015  01/09/2019      .               0
2  01/09/2019   01/22/2022  .               09/10/2015      0
3  11/10/2012   02/01/2013  .               .               0

You can optionally do this in two passes if you have SAS/ETS:

proc expand data=df_1 out=df1_lead(drop=time);
    by id;
    convert start_date = lead_start_date / transform=(lead 1);
run;
user_53427000 
data df_2; input ID $  start_date : mmddyy10.  end_date : mmddyy10.;
format start_date end_date date9.;
 pk=_n_;
cards;
1    01/01/2022   01/02/2022
1    01/02/2022   01/05/2022
1    01/06/2022   01/07/2022
2    01/09/2019   01/22/2022
2    06/07/2014   09/10/2015
3    11/10/2012   02/01/2013
;
run;
proc sql;
 create table df_1(drop=pk) as select distinct d1.*, 
  abs(start_date2-end_date)<=1 or abs(start_date-end_date2)<=1 as b2b_ind 
  from df_2 d1 cross join df_2(rename=(start_date=start_date2 end_date=end_date2 
   pk=pk2)) d2
 having b2b_ind=1 and pk^=pk2
order by ID,start_date,end_date;
quit;
user_194553960
Related