topic Re: Update values on missing consecutive in New SAS User

Update values on missing consecutive

cmemtsa — Thu, 15 Oct 2020 07:52:57 GMT

data T;
input ID L EXP;
Datalines;
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 50
3 3 50
;
data want;
 do until(last.id);
  set T;
  by id;
  if l<=1 then flag=1;
 end;

 do until(last.id);
  set T;
  by id;
  if not flag then exp=0;
  output;
 end;
 drop flag;
 run;

Hi,

with this code, this is the correct output (per ID if First.ID and L>1 all the sequential Exp updated to 0)

1 1 10
1 2 20
1 4 20

1 5 20
2 1 10
2 2 60
3 2 0
3 3 0

How can this code be amended in order if per ID the sequence of L is broken, the rest Exp to update to 0? So the output should be:

1 1 10
1 2 20
1 4 0

1 5 0
2 1 10
2 2 60
3 2 0
3 3 0

Thanks.

Re: Update values on missing consecutive

Kurt_Bremser — Thu, 15 Oct 2020 08:33:13 GMT

Manipulate flag in the second DO loop if the condition is met:

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;

Re: Update values on missing consecutive

cmemtsa — Thu, 15 Oct 2020 08:49:38 GMT

It works. Thank you!

Re: Update values on missing consecutive

yabwon — Thu, 15 Oct 2020 08:55:26 GMT

one other option would be:

data T;
input ID L EXP;
Datalines;
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 50
3 3 50
;
run;

data want;
  set T;
  by id;
  dif_L = dif(L);

  if first.ID then 
    do; 
      _tmp_EXP = .;
      if 1 ne L then _tmp_EXP + 0; 
    end;
  else if dif_L ne 1 then 
         do; 
           _tmp_EXP + 0; 
         end;
  
  if _tmp_EXP = 0 then EXP = 0;

  drop _tmp_EXP dif_L;
run;

Bart

Re: Update values on missing consecutive

cmemtsa — Thu, 15 Oct 2020 09:02:59 GMT

This works too. Thanks!

Re: Update values on missing consecutive

Ksharp — Thu, 15 Oct 2020 10:32:26 GMT

data T;
input ID L EXP;
Datalines;
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 50
3 3 50
;
data tt;
 set t;
 by id;
 if first.id or dif(l) ne 1 then group+1;
run;
data want;
 do until(last.group);
  set Tt;
  by group;
  if first.group and l<=1 then flag=1;
 end;

 do until(last.group);
  set Tt;
  by group;
  if not flag then exp=0;
  output;
 end;
 drop flag group;
 run;

Re: Update values on missing consecutive

cmemtsa — Thu, 15 Oct 2020 11:49:04 GMT

It works. Thanks.