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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.