Identifying consecutive values

[hjjijkkl]

yes, that is correct

[andreas_lds]

---

@hjjijkkl wrote:
I want the output that look like this
R45 9/10/19 70
R45 10/8/19 60
R30 9/7/19 50
R30 8/18/20 40
continues ..............................
............................

---

Do you want the observations having Visit < 80?

EDIT: If not, please explain for each observation listed why do you expect it in the result.

[hjjijkkl]

No, I don't want all observations that are visit <80. I will change the variable "visit" to "outcome" variable
I only want the records that have 2 consecutive (back to back 2x) outcome <80.
For example, If the first outcome was <80 and 2nd outcome >80 and 3rd outcome <80 then this isnot significant.
I want the outcome table to have the (ID, date, and the outcome) variables so that, I would now what the ID is and on what dates and what the outcomes where.

Please Help me on this. Thanks!

[mkeintz · Accepted solution]

So much of this thread has been spent just trying to clarify what the task statement actually should be:  "consecutive dates" vs "consecutive records",    two  less than 80, but not more than two.  I've had to read a lot of answers to respondent questions to finally understand what the objective is.

I think your best option is to create an intermediate dataset with a dummy variable (_UNDER80=1 if visit<80).  Then read that data set with a BY ID _UNDER80 NOTSORTED to count record-groups and output groups of size 2 if _UNDER80=1:

data have;
  input ID :$3.	Date :mmddyy.	Visit ;
  format date date9.;
datalines;
R45 3/15/19  90
R45 4/15/19 100
R45 5/15/19  70
R45 6/15/19  60
R90 3/15/19  90
R90 4/15/19 100
R90 5/15/19 120
R90 6/15/19 100
R30 3/15/19  90
R30 4/15/19  87
R30 5/15/19  50
R30 6/15/19  40
run;

data need;
  set have;
  _under80=(visit<80);
run;

data want (drop=_:);
  do _count=1 by 1 until (last._under80);
    set need;
    by id _under80 notsorted;
  end;
  do until (last._under80);
    set need;
    by id _under80 notsorted;
    if _count=2 and _under80=1 then output;
  end;
run;

Edited addition:

There is a way to do this in a single step - i.e. avoiding the DATA NEED step.  It requires the ability to compare whether the current visit and upcoming visit (_nxt_visit below) both have the same status when filtered as less-than 80.

data want (drop=_:);
  do _count=1 by 1 until (last.id or (sign(79.5-visit) ^= sign(79.5-_nxt_visit)));
    set have (keep=id);
	by id notsorted;
	merge have have(firstobs=2 keep=visit rename=(visit=_nxt_visit));
  end;

  do _i=1 to _count;
    set have;
	if _count=2 and visit<80 then output;
  end;
run;

So now, instead of

    DO _COUNT=1 BY 1 UNTIL (last._under80);

I have

    DO _COUNT=1 BY 1 UNTIL (LAST.ID or (SIGN(79.5-VISIT)^-SIGN(79.5-NXT_VISIT)));

The loops both stop at the same record.  Note I use SIGN(79.5-VISIT) for force the result to only be -1 or +1.  If I had used SIGN(80-VISIT), then the sign could also be zero (for visit=80).  I would have gotten the same output dataset, but the visit=80 records would be unnecessarily treated as their own group,   And this tactic works fine as long as VISIT values are integers.

The real "trick" here is using the self merge with a "FIRSTOBS=2" option as a way to compare current and upcoming VISIT values.

[Kurt_Bremser]

@mkeintz Two minds thinking alike. My suggested solution would have looked VERY similar (just different variable names).

@hjjijkkl See how important a well-stated question is? Study the double DO loop, it is one of the must-have tools for a data step coder.

[hjjijkkl]

Sorry for the confusion! but, Thank you all for your help! with some tweaks of the above code, I was able to get the output I was looks for.