How to test the occurrence of a certain value before the occurrence of an other value for group data

[SAS_taozi]

Hi all,

I have a long table (group data) of activation status for equipments  we have. Different status 1 = activated 0= not activated. We have measurements across all time points. I wanted to know whether if the occurrence of 0 is before 1 for all equipments (if 1 occurred before 0, meaning that it was activated but later on it's not activated any more, that does not make sense, right? for such data, I need to manually review them). Below is an example:

ID Status

1   0

1   0

1   0

1   1

1   1

2   0

2   1

4   1

4   0

4   0

What I want is ....

ID Status Check

1   0         1

1   0         1

1   0         1

1   1         1

1   1         1

2   0         1

2   1         1

4   1         0

4   0         0

4   0         0

different equipments may have different numbers of records... Do anyone know how to do this? very much appreciated!

[ballardw]

Maybe

Data want;
   set have;
   by id;
   retain check;
   if first.id then check=(status=0);
run;

If you data is not sorted by ID but only grouped add NOTSORTED to the BY statement.

When you do By group processing SAS creates automatic variables that indicate whether the particular record is the first. or last. of the group.

Retain keeps the value of a variable across data step boundaries.

The comparison (status=0) will return 1 for true and 0 for false.

You did not describe what to do with missing values so I am assuming that status is never missing.

[SAS_taozi]

Hi there, thanks very much and for the detailed explanation. I guess first. and last. would not suffice what I want here... Another example I had is:

Obs ID Status

1      5    0

2      5    1

3      5    0

4      5    1

5      5    0

So the status actually changes intermittently, I definitely need to manually review it, because for obs 2 and obs 3, status = 1 already occurred but followed by a status = 0. It does not make sense, because for our equipment, once activated, it is not even feasible to deactivate it.... Do you have any ideas to solve this problem? Thanks!

[ballardw]

So what is the desired result from this not-actually-supposed-to-happen data? Quite possible but need to understand the actual output needed.

---

@SAS_taozi wrote:

Hi there, thanks very much and for the detailed explanation. I guess first. and last. would not suffice what I want here... Another example I had is:

Obs ID Status

1      5    0

2      5    1

3      5    0

4      5    1

5      5    0

So the status actually changes intermittently, I definitely need to manually review it, because for obs 2 and obs 3, status = 1 already occurred but followed by a status = 0. It does not make sense, because for our equipment, once activated, it is not even feasible to deactivate it.... Do you have any ideas to solve this problem? Thanks!

---

If you are getting data like that how is it collected if the status supposedly cannot go from 1 to 0???

[SAS_taozi]

Hi there, you are correct, the data should not go from 1 to 0, is should only go from 0 to 1... I guess it's a manual error when recording. I just want to check the overall data quality... when there is data go from 1 to 0, I need to what are the IDs for them, so I could manually check on them again. But I need a list of IDs at least...I don't want to check on every equipment.

[Tom]

It is pretty easy to detect when a zero follows a one.  Going back in time and changing flag on the record that had the one would be harder.  But you can see them it it is the LAST.STATUS and not LAST.ID.

data want;
  set have;
  by id status notsorted ;
  if first.id then check=1;
  if last.status and status=1 and not last.id then check=0;
  retain check;
run;

If we add an extra STATUS=1 for ID=4 you can see how it works a little clearer.  Notice that CHECK changes to zero on the second observations for ID=4.

Obs    ID    Status    check

  1     1       0        1
  2     1       0        1
  3     1       0        1
  4     1       1        1
  5     1       1        1
  6     2       0        1
  7     2       1        1
  8     4       1        1
  9     4       1        0
 10     4       0        0
 11     4       0        0

[SAS_taozi]

Hi there, thanks very much for your reply! I actually only want one check value per ID, like an overall check score... which means if it's 1 then change to 0, it should be 0 for this ID since it is not qualified. Is there a way to do it? Thanks!

[Tom]

So just keep the last observation per ID.

if last.id;

If you want to do it in one step then use a double DOW loop.

data want;
do until (last.id);
  set have;
  by id status notsorted ;
  if first.id then check=1;
  if last.status and status=1 and not last.id then check=0;
end;
do until (last.id);
  set have;
  by id ;
  output;
end;
run;