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!
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.
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).
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.