- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.