topic Re: Flagging Records Based on Date Criteria in SAS in SAS Programming

Flagging Records Based on Date Criteria in SAS

SP01 — Mon, 01 Jul 2024 21:42:26 GMT

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;

Re: Flagging Records Based on Date Criteria in SAS

andreas_lds — Tue, 02 Jul 2024 09:01:04 GMT

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;

Re: Flagging Records Based on Date Criteria in SAS

Kurt_Bremser — Tue, 02 Jul 2024 07:56:40 GMT

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

Re: Flagging Records Based on Date Criteria in SAS

SP01 — Wed, 03 Jul 2024 08:31:26 GMT

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