topic Re: SAS date and time constraints by group in SAS Programming

SAS date and time constraints by group

monday89 — Thu, 04 Jun 2020 17:19:30 GMT

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;

Re: SAS date and time constraints by group

smantha — Thu, 04 Jun 2020 16:45:50 GMT

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

Re: SAS date and time constraints by group

monday89 — Thu, 04 Jun 2020 16:48:34 GMT

it is datetime23. format

Re: SAS date and time constraints by group

ballardw — Thu, 04 Jun 2020 16:52:42 GMT

@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.

Re: SAS date and time constraints by group

monday89 — Thu, 04 Jun 2020 17:03:27 GMT

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;

Re: SAS date and time constraints by group

ballardw — Thu, 04 Jun 2020 19:37:24 GMT

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.