DATA Step, Macro, Functions and more

Coding for an outcome based on dropping below a threshold

Reply
New Contributor
Posts: 4

Coding for an outcome based on dropping below a threshold

[ Edited ]

Hello,

 

I am having trouble figuring out how to code for an outcome where the event begins the first time the variable measures below 70 and it continues until there are 60 minutes worth of measurements 70 or above (the measurements occur every five minutes). The event then ends and the person is eligible for a new event the next time he or she drops below 70.

 

data WORK.GLU;
infile datalines dsd truncover;
input SubID:BEST12. DateTime:dATETIME. Glu:BEST12.;
datalines4;
1,15MAY06:00:00:00,68
1,15MAY06:00:05:00,64
1,15MAY06:00:10:00,70
1,15MAY06:00:15:00,66
1,15MAY06:00:20:00,65
1,15MAY06:00:30:00,70
1,15MAY06:00:35:00,71
1,15MAY06:00:40:00,76
1,15MAY06:00:45:00,80
1,15MAY06:00:50:00,82
1,15MAY06:00:55:00,83
1,15MAY06:01:00:00,88
1,15MAY06:01:05:00,90
1,15MAY06:01:10:00,90
1,15MAY06:01:15:00,92
1,15MAY06:01:20:00,89
1,15MAY06:01:30:00,93
1,15MAY06:01:35:00,90
1,15MAY06:01:40:00,98
1,15MAY06:01:45:00,102
1,15MAY06:01:50:00,99
1,15MAY06:01:55:00,94
1,15MAY06:02:00:00,94
1,15MAY06:02:05:00,93
1,15MAY06:02:10:00,88
1,15MAY06:02:15:00,87
1,15MAY06:02:20:00,83
1,15MAY06:02:30:00,84
1,15MAY06:02:35:00,86
1,15MAY06:02:40:00,78
1,15MAY06:02:45:00,73
1,15MAY06:02:50:00,69
1,15MAY06:02:55:00,67
1,15MAY06:03:00:00,66
1,15MAY06:03:05:00,68
1,15MAY06:03:10:00,70
1,15MAY06:03:15:00,70
1,15MAY06:03:20:00,70
1,15MAY06:03:30:00,70
1,15MAY06:03:35:00,70
1,15MAY06:03:40:00,70
1,15MAY06:03:45:00,70
1,15MAY06:03:50:00,70
1,15MAY06:03:55:00,70
1,15MAY06:04:00:00,70
;;;;

 

The first event would last 25 minutes (starts when the meaurement dips to 68) at and the next dip occurs 130 minutes later and lasts 20 mins (starts when it dips to 69). In the scenario where we "run out" of data at the end so that we don't have 60 minutes worth of data after the last dip below 70 then the event would end at the last data point.

 

 

I would like to output startDateTime, EndDateTime, eventNo, and eventDuration for each event per subject.

 

Super User
Posts: 10,500

Re: Coding for an outcome based on dropping below a threshold

What is your desired output?

 

Also a bit more data for input, especially if you want anything tested helps. This link will show how to post some of your data as datastep code to allow us to build a working data set. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

It will help if you can also provide an example of desired output, or at least indicate the important times for your requirements along with that example data.

 

I'm afraid that 3 data points when you are talking about extended time periods and multiple intervals doesn't quite give much feeling for what is going on. Your text mentions 25 minutes and then 130 minutes but how does time relate to the list of values shown?

New Contributor
Posts: 4

Re: Coding for an outcome based on dropping below a threshold

Thank you for directing me to that macro and for advising me on what information to add to my post.

Super User
Posts: 10,500

Re: Coding for an outcome based on dropping below a threshold

Okay that helps.

Now if I understand correctly then the first record above would be the start of a potential event.

If that is the case then I suspect that the end of the 60 minute event interval would occur in one of these records:

 

1,15MAY06:01:20:00,89
1,15MAY06:01:30:00,93
1,15MAY06:01:35:00,90

 

So the question is, for your purpose which is the one ends the interval (or show which one if not one of the three I show above)? This is not a trivial question as your report time could be reporting an instaneous measurement or the output time represents some sort of aggregate over the elapsed time from the previous reading and hence a 60 minute interval has a couple of possible different meanings for what should be accumulated.

 

Does anything special happen if the same ID has gap of more than 5 minutes in sequential records?

 

And for the $64,000 question: What information do you want added to the data? It seems like possibly a counter for numer of "event", an indicator for start of event or end of event, or possibly a repeated value for record during the event.

New Contributor
Posts: 4

Re: Coding for an outcome based on dropping below a threshold

The first event actually ends at

 

1,15MAY06:00:30:00,70

 

Because that is the first value 70 or above that begins a continuous 60 min interval of values 70 or above (.i.e., the end can only be defined retrospectively). The first such value 70 or above 

 

1,15MAY06:00:10:00,70

 

is not the end of the first interval because it is not followed by an hour of values at or above 70. If there are any missing readings (i.e., gaps) then they are assumed to be 70 or above. For the output I am envisioning something like

 

SubID

1

 

eventNo

1

 

StDtTm

15MAY06:00:00:00

 

EndDtTm

15MAY06:00:30:00

 

EventDur

30

 

One such record per event per subject

 

Super User
Posts: 9,681

Re: Coding for an outcome based on dropping below a threshold

Assuming I understand what you mean.




data WORK.GLU;
infile datalines dsd truncover;
input SubID:BEST12. DateTime:dATETIME. Glu:BEST12.;
format datetime datetime.;
datalines4;
1,15MAY06:00:00:00,68
1,15MAY06:00:05:00,64
1,15MAY06:00:10:00,70
1,15MAY06:00:15:00,66
1,15MAY06:00:20:00,65
1,15MAY06:00:30:00,70
1,15MAY06:00:35:00,71
1,15MAY06:00:40:00,76
1,15MAY06:00:45:00,80
1,15MAY06:00:50:00,82
1,15MAY06:00:55:00,83
1,15MAY06:01:00:00,88
1,15MAY06:01:05:00,90
1,15MAY06:01:10:00,90
1,15MAY06:01:15:00,92
1,15MAY06:01:20:00,89
1,15MAY06:01:30:00,93
1,15MAY06:01:35:00,90
1,15MAY06:01:40:00,98
1,15MAY06:01:45:00,102
1,15MAY06:01:50:00,99
1,15MAY06:01:55:00,94
1,15MAY06:02:00:00,94
1,15MAY06:02:05:00,93
1,15MAY06:02:10:00,88
1,15MAY06:02:15:00,87
1,15MAY06:02:20:00,83
1,15MAY06:02:30:00,84
1,15MAY06:02:35:00,86
1,15MAY06:02:40:00,78
1,15MAY06:02:45:00,73
1,15MAY06:02:50:00,69
1,15MAY06:02:55:00,67
1,15MAY06:03:00:00,66
1,15MAY06:03:05:00,68
1,15MAY06:03:10:00,70
1,15MAY06:03:15:00,70
1,15MAY06:03:20:00,70
1,15MAY06:03:30:00,70
1,15MAY06:03:35:00,70
1,15MAY06:03:40:00,70
1,15MAY06:03:45:00,70
1,15MAY06:03:50:00,70
1,15MAY06:03:55:00,70
1,15MAY06:04:00:00,70
;;;;
run;
data temp;
 set glu;
 by subid;
 if first.subid then event=0;
 if first.subid or glu gt 70 or sum gt 60 then do;
  event+1;
  sum=0;
 end;
 sum+5;
drop sum;
run;
data want;
 set temp;
 by subid event;
 retain start;
 if first.event then do;start=datetime;n=0;end;
 n+1;
 if last.event then do;
  end=datetime;
  dur=5*n;
  output;
 end;
 format start end datetime.;
 keep subid event start end dur;
 run;

New Contributor
Posts: 4

Re: Coding for an outcome based on dropping below a threshold

Thanks so much for responding to my post with code and I apologize it has taken me so long to acknowledge your help. I ran your code and it doesn't quite give the correct result. For example, the first event would last 25 minutes and the next does not occur until 130 minutes later. Your code gives the first event duration as 30 and events of duration 5 thereafter until the second event. The following is the code my friend came up with:

 

 

proc sort data=glu;
by subid datetime;
run;

 

data b;
set glu; by subid;
keep subid event start stop length;
length start stop $18;
retain subid event start stop length;
if first.subid then do;
event=0;
length=-5;
end;
if glu<=70 then do;
if length<0 then do;
event=event+1;
length=length+5;
start=datetime;
stop=datetime;
end;
else do;
length=length+5;
stop=datetime;
end;
end;
else if length>=0 then do;
output; *if you don't want 0 length - if length>0 then output;
length=-5;
end;
if last.subid & length>=0 then do;
output; *if you don't want 0 length - if length>0 then output;
length=-5;
end;
run;

 

proc print data=b noobs;
run;

 

Again, thank you for your assistance!

Ask a Question
Discussion stats
  • 6 replies
  • 363 views
  • 0 likes
  • 3 in conversation