Data manipulation question

[AMFR]

Hello all,

My data looks like this.

Encounter	Date	CAM_TS	cam	lag_cam	del_over
A	8/5/2018	05AUG18:20:45:00	1	1	0
A	8/6/2018	06AUG18:08:22:00	1	1	0
A	8/7/2018	07AUG18:09:00:00	1	1	0
A	8/8/2018	08AUG18:00:04:00	1	1	0
A	8/9/2018	09AUG18:08:01:00	1	1	0
A	8/10/2018	10AUG18:08:46:00	1	1	0
A	8/11/2018	11AUG18:12:23:00	1	1	0
A	8/12/2018	12AUG18:21:00:00	1	1	0
A	8/13/2018	13AUG18:09:19:00	1	1	0
A	8/14/2018	14AUG18:00:00:00	0	1	1
A	8/15/2018	15AUG18:00:00:00	0	0	1
A	8/16/2018	16AUG18:04:09:00	0	0	1
A	8/17/2018	17AUG18:00:00:00	0	0	1
B	10/8/2017	.	.	.	0
B	10/9/2017	09OCT17:09:17:00	0	.	1
B	10/10/2017	.	.	0	0
B	10/11/2017	.	.	.	0
B	10/12/2017	12OCT17:02:26:00	1	.	0
B	10/13/2017	13OCT17:08:41:00	0	1	1
B	10/14/2017	14OCT17:08:46:00	0	0	1
B	10/15/2017	15OCT17:04:04:00	1	0	0
B	10/16/2017	16OCT17:20:16:00	1	1	0
B	10/17/2017	17OCT17:21:00:00	0	1	1
B	10/18/2017	18OCT17:21:30:00	1	0	0
B	10/19/2017	19OCT17:08:00:00	1	1	0
B	10/20/2017	20OCT17:00:07:00	1	1	0
B	10/21/2017	21OCT17:00:00:00	1	1	0
B	10/22/2017	22OCT17:21:32:00	1	1	0
B	10/23/2017	23OCT17:21:00:00	0	1	1
B	10/24/2017	24OCT17:08:12:00	0	0	1

I have a data where I have to find when patient is out of delirium, two consecutive cam should be 0. First we need to extract those first two cam_ts when two consecutive cam=0. For example for encounter A the first two consecutive cam_ts when cam=0 would be on 14th Aug and 15th Aug, and after that I need to write a piece of code that would give me the output of the data before two consecutive cam=0. And I only need to look at the first two consecutive cam=0. For example encounter B has two time consecutive cam=0, we would consider delirium is over after first two consecutive cam=0.

Please ignore lag_cam and del_over columns. These are the columns I created to solve my question but it didn't help

Hope I am making sense, any help would be greatly appreciated.

Thanks

[novinosrin]

Hi @AMFR  Assuming I understand

data have;
input Encounter	$ Date :mmddyy10.	CAM_TS :datetime20.	cam	lag_cam;
format date mmddyy10. CAM_TS datetime20.;
cards;
A	8/5/2018	05AUG18:20:45:00	1	1
A	8/6/2018	06AUG18:08:22:00	1	1
A	8/7/2018	07AUG18:09:00:00	1	1
A	8/8/2018	08AUG18:00:04:00	1	1
A	8/9/2018	09AUG18:08:01:00	1	1
A	8/10/2018	10AUG18:08:46:00	1	1
A	8/11/2018	11AUG18:12:23:00	1	1
A	8/12/2018	12AUG18:21:00:00	1	1
A	8/13/2018	13AUG18:09:19:00	1	1
A	8/14/2018	14AUG18:00:00:00	0	1
A	8/15/2018	15AUG18:00:00:00	0	0
A	8/16/2018	16AUG18:04:09:00	0	0
A	8/17/2018	17AUG18:00:00:00	0	0
B	10/8/2017	.	.	.
B	10/9/2017	09OCT17:09:17:00	0	.
B	10/10/2017	.	.	0
B	10/11/2017	.	.	.
B	10/12/2017	12OCT17:02:26:00	1	.
B	10/13/2017	13OCT17:08:41:00	0	1
B	10/14/2017	14OCT17:08:46:00	0	0
B	10/15/2017	15OCT17:04:04:00	1	0
B	10/16/2017	16OCT17:20:16:00	1	1
B	10/17/2017	17OCT17:21:00:00	0	1
B	10/18/2017	18OCT17:21:30:00	1	0
B	10/19/2017	19OCT17:08:00:00	1	1
B	10/20/2017	20OCT17:00:07:00	1	1
B	10/21/2017	21OCT17:00:00:00	1	1
B	10/22/2017	22OCT17:21:32:00	1	1
B	10/23/2017	23OCT17:21:00:00	0	1
B	10/24/2017	24OCT17:08:12:00	0	0
;


data want;
 set have;
 by encounter cam notsorted;
 retain del_over;
 if first.cam then del_over=0;
 if not (first.cam and last.cam) and cam=0 then del_over=1;
run;

[AMFR]

Thank you very much, after identifying when delirium is over, this is exactly what I was doing created lag_cam and del_over.

After that in first step I want to have data look like this.

Encounter	Date	CAM_TS	cam	del_over	First_del_over_ts	Second_Del_over_ts
A	8/5/2018	05AUG18:20:45:00	1	0	14AUG18:00:00:00	15AUG18:00:00:00
A	8/6/2018	06AUG18:08:22:00	1	0	14AUG18:00:00:01	15AUG18:00:00:01
A	8/7/2018	07AUG18:09:00:00	1	0	14AUG18:00:00:02	15AUG18:00:00:02
A	8/8/2018	08AUG18:00:04:00	1	0	14AUG18:00:00:03	15AUG18:00:00:03
A	8/9/2018	09AUG18:08:01:00	1	0	14AUG18:00:00:04	15AUG18:00:00:04
A	8/10/2018	10AUG18:08:46:00	1	0	14AUG18:00:00:05	15AUG18:00:00:05
A	8/11/2018	11AUG18:12:23:00	1	0	14AUG18:00:00:06	15AUG18:00:00:06
A	8/12/2018	12AUG18:21:00:00	1	0	14AUG18:00:00:07	15AUG18:00:00:07
A	8/13/2018	13AUG18:09:19:00	1	0	14AUG18:00:00:08	15AUG18:00:00:08
A	8/14/2018	14AUG18:00:00:00	0	1	14AUG18:00:00:09	15AUG18:00:00:09
A	8/15/2018	15AUG18:00:00:00	0	1	14AUG18:00:00:10	15AUG18:00:00:10
A	8/16/2018	16AUG18:04:09:00	0	1	14AUG18:00:00:11	15AUG18:00:00:11
A	8/17/2018	17AUG18:00:00:00	0	1	14AUG18:00:00:12	15AUG18:00:00:12
B	10/8/2017	.	.	0	13OCT17:08:41:00	14OCT17:08:46:00
B	10/9/2017	09OCT17:09:17:00	0	1	13OCT17:08:41:01	14OCT17:08:46:01
B	10/10/2017	.	.	0	13OCT17:08:41:02	14OCT17:08:46:02
B	10/11/2017	.	.	0	13OCT17:08:41:03	14OCT17:08:46:03
B	10/12/2017	12OCT17:02:26:00	1	0	13OCT17:08:41:04	14OCT17:08:46:04
B	10/13/2017	13OCT17:08:41:00	0	1	13OCT17:08:41:05	14OCT17:08:46:05
B	10/14/2017	14OCT17:08:46:00	0	1	13OCT17:08:41:06	14OCT17:08:46:06
B	10/15/2017	15OCT17:04:04:00	1	0	13OCT17:08:41:07	14OCT17:08:46:07
B	10/16/2017	16OCT17:20:16:00	1	0	13OCT17:08:41:08	14OCT17:08:46:08
B	10/17/2017	17OCT17:21:00:00	0	1	13OCT17:08:41:09	14OCT17:08:46:09
B	10/18/2017	18OCT17:21:30:00	1	0	13OCT17:08:41:10	14OCT17:08:46:10
B	10/19/2017	19OCT17:08:00:00	1	0	13OCT17:08:41:11	14OCT17:08:46:11
B	10/20/2017	20OCT17:00:07:00	1	0	13OCT17:08:41:12	14OCT17:08:46:12
B	10/21/2017	21OCT17:00:00:00	1	0	13OCT17:08:41:13	14OCT17:08:46:13
B	10/22/2017	22OCT17:21:32:00	1	0	13OCT17:08:41:14	14OCT17:08:46:14
B	10/23/2017	23OCT17:21:00:00	0	1	13OCT17:08:41:15	14OCT17:08:46:15
B	10/24/2017	24OCT17:08:12:00	0	1	13OCT17:08:41:16	14OCT17:08:46:16

And after separating out the two consecutive TS when delirium is over, data should look like this because I don't want to include days after delirium is over.

Encounter	Date	CAM_TS	cam	del_over	First_del_over_ts	Second_Del_over_ts
A	8/5/2018	05AUG18:20:45:00	1	0	14AUG18:00:00:00	15AUG18:00:00:00
A	8/6/2018	06AUG18:08:22:00	1	0	14AUG18:00:00:01	15AUG18:00:00:01
A	8/7/2018	07AUG18:09:00:00	1	0	14AUG18:00:00:02	15AUG18:00:00:02
A	8/8/2018	08AUG18:00:04:00	1	0	14AUG18:00:00:03	15AUG18:00:00:03
A	8/9/2018	09AUG18:08:01:00	1	0	14AUG18:00:00:04	15AUG18:00:00:04
A	8/10/2018	10AUG18:08:46:00	1	0	14AUG18:00:00:05	15AUG18:00:00:05
A	8/11/2018	11AUG18:12:23:00	1	0	14AUG18:00:00:06	15AUG18:00:00:06
A	8/12/2018	12AUG18:21:00:00	1	0	14AUG18:00:00:07	15AUG18:00:00:07
A	8/13/2018	13AUG18:09:19:00	1	0	14AUG18:00:00:08	15AUG18:00:00:08
B	10/8/2017	.	.	0	13OCT17:08:41:00	14OCT17:08:46:00
B	10/9/2017	09OCT17:09:17:00	0	1	13OCT17:08:41:01	14OCT17:08:46:01
B	10/10/2017	.	.	0	13OCT17:08:41:02	14OCT17:08:46:02
B	10/11/2017	.	.	0	13OCT17:08:41:03	14OCT17:08:46:03
B	10/12/2017	12OCT17:02:26:00	1	0	13OCT17:08:41:04	14OCT17:08:46:04

[novinosrin]

Okay @AMFR   Try this

data want;
 set have;
 by encounter cam notsorted;
 if first.encounter then _iorc_=0;
 del_over= not (first.cam and last.cam) and cam=0;
 if del_over then _iorc_=1;
 if not _iorc_;
run;

[novinosrin]

If my understanding is correct, the previous doesn't require a RETAIN because of the boolean operation. Sorry about that. So a modified version would be

data want;
 set have;
 by encounter cam notsorted;
 if not (first.cam and last.cam) and cam=0 then del_over=1;else del_over=0;
run;

/*or*/

data want;
 set have;
 by encounter cam notsorted;
 del_over= not (first.cam and last.cam) and cam=0;
run;