Conditional on interval of dates

[Dunne]

ID	route	date	ddif	Keep
1	OP	200	0	0
1	OT	207	7	1
1	OT	240	33	1
2	OT	200	0	1
2	OP	250	50	1
2	OT	290	40	1
2	OT	297	7	0
3	OP	200	0	1
3	OP	250	50	0
3	OT	270	20	1
3	OP	290	20	0
3	OT	500	210	1

Hello,

I want to create a variable keep as the above table with the condition as below

Within the same ID, if date is apart 30 days from the row before and after then keep=1,

else put the priority to OT, it means if OP has date apart from OT <30 days then keep=0.

If OP and OP next to each other and days apart <30, the keep the first (keep=1) and delete the later (keep=0),

similar if 2 OT next to each other and days apart <30.

Many thanks!

[Patrick]

Please provide sample data in the form a SAS data step.

What have you done so far? Please post your code whether fully working yet or not. That not only demonstrates that you're not just after having done all the work for you for free but it also gives us a pointer on which SAS coding level you are so we can provide answers on an appropriate level.

Have you already tried to search the communities here? Similar questions have been asked and solved before.

[Dunne]

Thank you for your suggestion!

What I did is kind of long and use simple code.

My idea is split data into a smaller pie to deal with each part of the problem and then combine all of them back.

here are half of my codes, I don't post all because.....it's too long and I am on a secured server so I can't copy but screenshot (please see the attachment).

[Patrick]

I couldn't fully work out the logic you want implemented so below doesn't return your desired result. It does demonstrate though how you could implement. You just need to amend the CASE conditions to fully meet your logic.

data have;
  infile datalines truncover;
  input ID  route $ date  ddif  Keep;
  rowid+1;
  datalines;
1 OP  200 0 0
1 OT  207 7 1
1 OT  240 33  1
2 OT  200 0 1
2 OP  250 50  1
2 OT  290 40  1
2 OT  297 7 0
3 OP  200 0 1
3 OP  250 50  0
3 OT  270 20  1
3 OP  290 20  0
3 OT  500 210 1
;
run;

proc sql;
  create table want as
    select 
      t1.id,
      t1.route,
      t1.date,
      t1.ddif,
      t1.keep,
      case
        /* Within the same ID, if date is apart 30 days from the row before and after then keep=1 */
        when t1.date-t0.date < 30 and abs(t1.date-t2.date) <30 then 0
        /* If OP and OP next to each other and days apart <30, the keep the first (keep=1) and delete the later (keep=0) */
        when t1.route='OP' and t1.route=t0.route and t1.date-t0.date < 30 then 0
        /* similar if 2 OT next to each other and days apart <30 */
        when t1.route='OT' and t1.route=t0.route and t1.date-t0.date < 30 then 0
        else 1
        end
        as keep_calc

    from 
      have t1

    left join have t0
      on
            t1.id=t0.id
        and t1.rowid=t0.rowid+1

    left join have t2
      on
            t1.id=t2.id
        and t1.rowid=t2.rowid-1
  ;
quit;

[Patrick]

@Dunne

BTW: The 30 days difference you're using indicates that you're after logic which deals with "financial" months of 30 days. If so then consider using the SAS DATDIF() function with a basis of 360/30 as this will return correct results for the difference between any calendar dates.

https://go.documentation.sas.com/?docsetId=fedsqlref&docsetTarget=p1whj5qezkb2p6n1w7g1ran2t6fn.htm&d...

[Dunne]

Sorry because I didn't provide the clear background. OT and OP are 2 medications. OP can use to replace OT but not reverse. And my purpose is to calculate OT use or OP with purpose to replace OT.
30 days are the length I define to count, if 2 prescriptions are 30 days apart I would like to keep both of them no matter it's OT or OP. If 2 prescriptions are both OT (or both OP) and days apart <=30 I would keep the first one and delete the later. If 2 prescriptions one is OT and one is OP and days apart <= 30 then I want to keep OT no matter it's prescribe first or later. Thing is more complicated when number of prescriptions > 2.