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
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;
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 |
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;
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;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.