topic Re: Spot the non-consecutive values and update accordingly a different column data in SAS Programming

Spot the non-consecutive values and update accordingly a different column data

cmemtsa — Fri, 23 Oct 2020 07:49:19 GMT

data T;
input ID L EXP;
Datalines;
A1 1 10
A1 1 10
A1 2 20
A1 2 20
A1 4 20
A1 4 20
A1 5 20
A2 1 10
A2 2 60
A2 2 60
A2 3 60
A2 3 60
A2 5 50
A2 7 50
A3 2 60
A3 4 60
;

 data want;
do until (last.id);
  set T;
  by id;

  if l <= 1 then flag = 1;
end;

put flag=;

do until(last.id);
  set T;
  by id;
  if not first.id and lag(l) ne l - 1  then flag = 0;
  if not flag then exp = 0;
  output;
end;
drop flag;
run;

Hi,

On this dataset (already sorted by ID, L) , I want to update EXP to 0 in these cases:

1. The First.L per ID is not 1 (and all the subsequent to it)

2. The first time the sequence of L is broken per ID and all the subsequent to it. (there are repetitions of L)

i.e the output should be

A1 1 10
A1 1 10
A1 2 20

A1 2 20

A1 4 0
A1 4 0
A1 5 0
A2 1 10
A2 2 60
A2 2 60
A2 3 60
A2 3 60
A2 5 0

A2 7 0
A3 2 0
A3 4 0

The above code does not properly work in this case.

How should be amended?

Thanks.

Re: Spot the non-consecutive values and update accordingly a different column data

andreas_lds — Fri, 23 Oct 2020 08:38:28 GMT

Please explain what "The first time the sequence of L is broken per ID" means.

Re: Spot the non-consecutive values and update accordingly a different column data

cmemtsa — Fri, 23 Oct 2020 08:45:31 GMT

e.g. in A1 we have L with sequence 1,1,2, 2, (which is normal: could be also 1,2,2) and then goes to 4. All EXP should be set to 0 for L=4 and all the subsequent (per ID).

Re: Spot the non-consecutive values and update accordingly a different column data

andreas_lds — Fri, 23 Oct 2020 09:41:03 GMT

Maybe this solves it, i renamed "L" to increase readability.

data want;
   set have;
   by ID;

   retain reset;

   LastLorax = lag(Lorax);

   if first.Id then do;
      LastLorax = .;
      reset = 0;

      if Lorax ^= 1 then do;         
         reset = 1;
      end;
   end;
   else do;
      if Lorax - LastLorax > 1 or Lorax = 4 then do;
         reset = 1;
      end;
   end;

   if reset then do;
      exp = 0;
   end;

   keep ID Lorax Exp;
run;

Re: Spot the non-consecutive values and update accordingly a different column data

cmemtsa — Fri, 23 Oct 2020 11:34:03 GMT

It works for this samples dataset with taking out the (or Lorax=4) condition.
I'm testing it in other samples as well, see how it responds.
Thanks.