SAS date and time constraints by group

[monday89]

I have the following dataset.

What I would like to do is

• if group = 2 and (stop_date_time_1 is NOT MISSING and occurs on or before final_stop_date_time) then keep = 1
• else if group = 2 and (stop_date_time_1 is missing) and start_date_time_1 occurs AFTER final_stop_date_time) then keep = 0

SubjectID	final_stop_date_time	start_date_time_1	stop_date_time_1	group
11	12FEB2020:10:59:00	11FEB2020:15:30:00	11FEB2020:16:08:00	2
11	12FEB2020:10:59:00	11FEB2020:16:08:00	12FEB2020:10:59:00	2
11	12FEB2020:10:59:00	12FEB2020:21:40:00	.	2

Want: 

SubjectID	final_stop_date_time	start_date_time_1	stop_date_time_1	group	keep
11	12FEB2020:10:59:00	11FEB2020:15:30:00	11FEB2020:16:08:00	2	1
11	12FEB2020:10:59:00	11FEB2020:16:08:00	12FEB2020:10:59:00	2	1
11	12FEB2020:10:59:00	12FEB2020:21:40:00	.	2	0

I tried the following and doesn't work

data want; set have; 

if group = '2' and
(stop_date_time_1 <= final_stop_date_time) then keep = 1 ;
else if
(group = '2' and ((start_date_time_1 > final_stop_date_time ) and missing(stop_date_time_1)) OR
(group = '2' and (stop_date_time_2 > final_stop_date_time))
then keep = 0;
run;

[smantha]

are your fields numeric or character. If the fields are numeric you can use missing(<fieldname>) instead of <fieldname>='.'.

[monday89]

it is datetime23. format

[ballardw]

---

@smantha wrote:

are your fields numeric or character. If the fields are numeric you can use missing(<fieldname>) instead of <fieldname>='.'.

---

The MISSING function will work with character variables as well. So a better suggestion than you first thought.

[monday89]

Still doesn't work

data want; set have; 

if group = '2' and 
(stop_date_time_1 <= final_stop_date_time) then keep = 1 ;
else if 
(group = '2' and ((start_date_time_1 > final_stop_date_time ) and missing(stop_date_time_1)) OR 
(group = '2' and (stop_date_time_2 > final_stop_date_time)) 
then keep = 0;
run;

[ballardw]

Show the log for your data step. Show the code and any messages. Copy from the LOG and paste into a code box opened on the forum with the</> icon. The code box is important because the message windows will reformat text including SAS generated diagnostic text.

(group = '2' and ((start_date_time_1 > final_stop_date_time ) and missing(stop_date_time_1)) OR 
(group = '2' and (stop_date_time_2 > final_stop_date_time)) 

count the ( and ) you have mismatched parentheses.