Quartz | Level 8

## Data manipulation question

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

4 REPLIES 4
Tourmaline | Level 20

## Re: Data manipulation question

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;``````
Quartz | Level 8

## Re: Data manipulation question

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
Tourmaline | Level 20

## Re: Data manipulation question

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;``````
Tourmaline | Level 20

## Re: Data manipulation question

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;``````
Discussion stats
• 4 replies
• 390 views
• 0 likes
• 2 in conversation