topic Grouping observations with start and end marking in SAS Programming

Grouping observations with start and end marking

Cornelis — Mon, 26 Feb 2024 13:09:32 GMT

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

Re: Grouping observations with start and end marking

andreas_lds — Mon, 26 Feb 2024 14:13:29 GMT

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.

Re: Grouping observations with start and end marking

Cornelis — Mon, 26 Feb 2024 15:06:17 GMT

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

Re: Grouping observations with start and end marking

mkeintz — Mon, 26 Feb 2024 15:40:49 GMT

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.

Re: Grouping observations with start and end marking

Cornelis — Tue, 27 Feb 2024 06:51:30 GMT

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;