Flagging Records Based on Date Criteria in SAS

[SP01]

I have a dataset have with variables id, date_var, and tp_2. I need to flag records where tp_2 = 0. For multiple records with the same id, I want to use the last occurring date_var for each id to check if other records with the same id are within 10 months of this date. If they are within 10 months, they should be flagged.

data have;
    informat id $20. date_var date9.;
    format date_var date9. tp_2 1.;
    input id $ date_var tp_2;
    datalines;
23417 09May2021 0
23417 25Jul2022 0
23417 01Aug2022 0
27539 12Oct2021 0
27539 03Nov2021 0
73830 19Nov2021 0
;
run;

data want;
    informat id $20. date_var date9.;
    format date_var date9. tp_2 1. flag 1.;
    input id $ date_var tp_2 flag;
    datalines;
23417 09May2021 0  0
23417 25Jul2022 0  1
23417 01Aug2022 0  0
27539 12Oct2021 0  1
27539 03Nov2021 0  0
73830 19Nov2021 0  0
;
run;

[andreas_lds · Accepted solution]

Is the data sorted by id and date_var?

Are there any obs with tp_2 ^= 0 in your data?

Try:

proc summary data=work.have nway;
    class id;
    var date_var;
    output out=work.last(drop= _type_ _freq_) max=last_date;
run;

data work.want;
    merge work.have work.last;
    by id;
    
    n = intck("month", date_var, last_date, 'd');
    flag = (not last.id) and n <= 10;
    
    drop n last_date;
run;

[SP01]

@andreas_lds Yes, there are observations where tp_2^= 0 in the data.

[Kurt_Bremser]

Run a double DO loop:

data want;
do until (last.id);
  set have (where=(tp_2 = 0));
  by id;
end;
ref = date;
do until (last.id);
  set have;
  by id;
  flag = (tp_2 = 0 and intck("month",date,ref,"s") le 10);
  output;
end;
drop ref;
run;

Assumes that

• have is sorted by id and date
• there is at least one tp_2 = 0 for every id