Grouping observations with start and end marking

[Cornelis]

Hello SAS users,

I have a table where I want to mark every observation with events 'Opened' and 'Close'.

Column Valve describes the position of a valve, where 1 = open and 0 = close.

At 26FEB24:04:52 the valve is opened and column 'Status' must mark this with 'Opened'

At 26FEB24:04:54, the valve is closed and must have the status 'Closed'.

In fact, the valve is opened during 3 minutes.

Could you give me a clue how to program/add column Status with Opened/Closed taking into account that the valve will be opened for 1, 2, 3 and more minutes?

Thanks and looking forward,

Best Regards,

Cornelis

[andreas_lds]

It is not clear (to me) in which way the Datetime has an influence on the Status variable.

Please not that it will be easier to help you would post the data in usable form.

[Cornelis]

Hello Andreas,

Column Status is the resulting output after running a SAS program.

Observation 3: The valve is open and the datetime = 26FEB24:04:52:00.

At  26FEB24:04:54:00, observation 5, the valve is still open, but will be closed of very soon, so the Status is marked as 'Close".

The total time that a valve is open lies between 04:52 - 04:54 hour.

Does this make sense?

Regards,

Cornelis

[mkeintz · Accepted solution]

Please provide your sample data in the form of a working DATA step.  The program below is untested in the absence of such sample data:

data want;
  set have;
  by valve notsorted;
  if valve=1 then do;
    if first.valve=1 and last.valve=1 then status='Open and Closed';
    else if first.valve=1 then status='Open';
    else if last.valve=1 then status='Closed';
  end;
run;

The "notsorted" option of the BY statement is especially useful here, since it allows you to track changes between valve=1 and valve=0, without regard to whether the valve change is positive or negative.

[Cornelis]

Thank you very much for your quick solution!

The not-sorted option do the trick.

Indeed, supplying the SAS code is helpful. For the completeness, here is the SAS code and already validated:

data have;
input valve $1-2 datetime $3-20 ;
cards;
0	26FEB24:04:51:00
1	26FEB24:04:52:00
1	26FEB24:04:53:00
1	26FEB24:04:54:00
0	26FEB24:04:55:00
0	26FEB24:04:56:00
0	26FEB24:04:57:00
1	26FEB24:04:58:00
1	26FEB24:04:59:00
0	26FEB24:05:00:00
0	26FEB24:05:01:00
1	26FEB24:05:02:00
0	26FEB24:05:03:00
0	26FEB24:05:04:00
;
run;



data want;
  set have;
  by valve notsorted;
  if valve=1 then do;
    if first.valve=1 and last.valve=1 then status1='Open and Closed';
    else if first.valve=1 then status1='Open';
    else if last.valve=1 then status1='Closed';
  end;
run;