sas

Set the value of a variable based on the previous value of another variable

suppose to have the following:

data have;
  input ID :$20. Date :date09. Variable1 Variable2 Index; 
  format Date date9.;
cards;
0001 22JAN2020 1  0  33
0001 13FEB2020 .  .  .
0001 13FEB2020 0  1  22
0001 03OCT2021 .  .  .
0002 02SEP2014 0  1  1
0002 10OCT2014 .  .  .
0002 10OCT2014 1  0  13
;

Is there a way to get the following?

data want;
  input ID :$20. Date :date09. Variable1 Variable2 Index; 
  format Date date9.;
cards;
0001 22JAN2020 1  0  33
0001 13FEB2020 .  .  .
0001 13FEB2020 0  1  .
0001 03OCT2021 .  .  .
0002 02SEP2014 0  1  1
0002 10OCT2014 .  .  .
0002 10OCT2014 1  0  .
;

In other words where there are replicated dates, if the value of Variable1 is missing (there is also Variable2 but Variable1 is sufficient) then the value of Index at the next row having the same date should be missing (ID per ID).

For example: ID 0001. The second replicated at date 13FEB2020 should have Index = . because the previous record at the same date has Variable1 = .

Thank you in advance.

Solution

  • Try this

    data want;
   set have;
   by ID Date;
   if first.Date then _iorc_ = Index;
   else Index = _iorc_;
run;

    Result:

    ID    Date       Variable1  Variable2  Index
0001  22JAN2020  1          0          33
0001  13FEB2020  .          .          .
0001  13FEB2020  0          1          .
0001  03OCT2021  .          .          .
0002  02SEP2014  0          1          1
0002  10OCT2014  .          .          .
0002  10OCT2014  1          0          .