topic Re: Identify ids with consecutive values meeting threshold in SAS Programming

Identify ids with consecutive values meeting threshold

SarahW13 — Sat, 22 Sep 2018 04:40:10 GMT

Hello all!

I have a dataset for which I need to identify ids that had two consecutive values of ≥180.

Below is a sample dataset:

Id date value

1 1/3/15 140

1 2/9/15 145

1 3/4/15 132

2 1/8/16 150

2 2/15/16 180

2 3/14/16 181

3 1/1/15 110

3 2/1/15 180

3 3/1/15 190

4 1/7/17 130

4 2/8/17 180

From this example, I would want to identify that id 2 and id 3 had two consecutive values of ≥180.

Any advice?

Re: Identify ids with consecutive values meeting threshold

Kurt_Bremser — Mon, 24 Sep 2018 07:37:19 GMT

Retain a flag variable, use by id;

if first.id then flag = 0;
if not first.id and lag(value) >= 180 and value >= 180 then flag = 1;
if last.id and flag then output;

Edit: changed second comparison from "greater" (>) to "greater or equal" (>=).

Re: Identify ids with consecutive values meeting threshold

Astounding — Sat, 22 Sep 2018 21:28:26 GMT

That looks like the right set of tools, but you might need to tweak the program. I could be wrong (can't test it right now), but the program might erroneously flag this situation:

ID value

1 100

1 200

2 100

2 200

If that situation turns out to be a problem, here's the fix:

data want;

set have;

by id;

prior_value = lag(value);

if first.id then flag=0;

else if value >= 180 and prior_value >= 180 then flag+1;

if last.id and flag;

run;

Re: Identify ids with consecutive values meeting threshold

PGStats — Sun, 23 Sep 2018 05:06:54 GMT

Extract those IDs with:

data want;
do until(last.id);
    set have; by id;
    if met then if value >= 180 then consec = 1;
    met = value >= 180;
    end;
do until(last.id);
    set have; by id;
    if consec then output;
    end;
drop met consec;
run;

Re: Identify ids with consecutive values meeting threshold

Kurt_Bremser — Mon, 24 Sep 2018 07:35:49 GMT

@Astounding wrote:

That looks like the right set of tools, but you might need to tweak the program. I could be wrong (can't test it right now), but the program might erroneously flag this situation:

ID value

1 100

1 200

2 100

2 200

If that situation turns out to be a problem, here's the fix:

data want;

set have;

by id;

prior_value = lag(value);

if first.id then flag=0;

else if value >= 180 and prior_value >= 180 then flag+1;

if last.id and flag;

run;

Just checked it:

data have;
input id value;
cards;
1      100
1      200
2      100
2      200
;
run;

data want (keep=id);
set have;
by id;
retain flag;
if first.id then flag = 0;
if not first.id and lag(value) >= 180 and value >= 180 then flag = 1;
if last.id and flag then output;
run;

Result is an empty dataset, as it should be. SAS does not optimize the if condition, so the lag() function is reliably executed in every iteration.

That's why I specifically omitted the "obvious" else for the check of "first.id", and used a separate check for "not first.id" instead.