Flagging Consecutive Lab Values > then 4.5

Reply
Occasional Contributor
Posts: 5

Flagging Consecutive Lab Values > then 4.5

If I had a dataset that contains ids and lab results and I wanted to flag patients that had two consecutive lab results > 4.5 how do I go about it?

Example:

ID     Result

111     3.9

111     4.6

111     4.7

111     1.2

222     1.2

333     4.6

333     4.7

So I would want to have another field called flag and it would field in this example 111 4.6 and 111 4.7 and also 333 4.6 and 4.7.

Thanks so much!

Super User
Posts: 10,543

Re: Flagging Consecutive Lab Values > then 4.5

data want;

     set have;

     by id;

     if first.id then flag=.;

     else flag = ((Result > 4.5) and (lag(result)>4.5));

run;

Will assign a flag value of one for records where the current and previous records have result > 4.5. If you need to have the flag on both records then there's signficantly more code involved.

This does require the data to be sorted by ID. Personally I would be much happier to also see a date or time variable to ensure the order is correct.

Respected Advisor
Posts: 3,777

Re: Flagging Consecutive Lab Values > then 4.5

Sorry but that conditional LAG is not going to work here.

data have;
   input Id Result;
   cards;
111     3.9
111     4.6
111     4.7
111     1.2
222     1.2
333     4.6
333     4.7
;;;;
data want;
   set have;
   by id;
   flag = ((Result > 4.5) and (lag(result)>4.5));
   if first.id then flag=0;
  
run;
proc print;
  
run;
Respected Advisor
Posts: 3,777

Re: Flagging Consecutive Lab Values > then 4.5

To flag the "run" you can read the data twice.

data have;
   input Id Result;
   cards;
111     3.9
111     4.6
111     4.7
111     1.2
111     4.8
111     4.9
111     4.9
111     1.3
222     1.2
333     4.6
333     4.7
;;;;
data want;
   do until(last.id);
      do n1 = 1 by 1 until(flag or last.id);
         set have;
         by id;
         flag = ((Result > 4.5) and (lag(result)>4.5));
         if first.id then flag=0;
        
end;
     
do n2=1 to n1;
         set have;
         if flag eq 1 and (n2 ge n1-1) then flag2=1; else flag2=0;
        
output;
        
end;
     
end;
  
drop n1-n2 flag;
   run;
proc print;
  
run;
Occasional Contributor
Posts: 5

Re: Flagging Consecutive Lab Values > then 4.5

Thank you so much!  This worked for what I need! Smiley Happy

Ask a Question
Discussion stats
  • 4 replies
  • 295 views
  • 0 likes
  • 3 in conversation