topic Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS? in SAS Enterprise Guide

How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 08:46:56 GMT

Hello Guys,|

Simple question. I have a dataset which looks like below (in reality much bigger). I want to remove the first 3 seconds and the last 3 seconds of every measurement. How can that easily be done?

Value - DateTime

5 - 0.0

15 - 1.5

74 - 2.2

19 - 3.1

13 - 4.2

71 - 4.9

40 - 5.6

31 - 6.8

87 - 7.8

10 - 8.4

11 - 9.3

47 - 10.1

9 - 0.0

11 - 0.9

7 - 1.9

10 - 2.8

11 - 4.5

74 - 5.2

10 - 6.6

11 - 7.1

71 - 7.8

80 - 8.9

16 - 9.9

15 - 10.9

So I actually want the BOLD ones removed.. Would like to hear. Thank you

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

PeterClemmensen — Mon, 16 Nov 2020 08:50:34 GMT

Why the first 4 in the second measurement?

Do you have an ID for each of your measurements?

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 08:59:13 GMT

I need the first and last 3 seconds removed because don't actually add something. The client doesn't need them. Yes, I forgot to say but they also have an ID yes. So record 1 till 12 have the same ID record 13 till 24 to etc..

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

PeterClemmensen — Mon, 16 Nov 2020 09:08:01 GMT

Ok. Do all IDs have 12 observations exactly?

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

Kurt_Bremser — Mon, 16 Nov 2020 09:09:31 GMT

Use a double DO loop. In the first loop, record first and last time. In the second loop, only OUTPUT when time is between start + 3 and end - 3.

For code, provide data in usable form.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

PeterClemmensen — Mon, 16 Nov 2020 09:10:46 GMT

If so, you can do it in a single pass:

data have;
input ID Value DateTime;
datalines;
1 5 0.0
1 15 1.5
1 74 2.2
1 19 3.1
1 13 4.2
1 71 4.9
1 40 5.6
1 31 6.8
1 87 7.8
1 10 8.4
1 11 9.3
1 47 10.1
2 9 0.0
2 11 0.9
2 7 1.9
2 10 2.8
2 11 4.5
2 74 5.2
2 10 6.6
2 11 7.1
2 71 7.8
2 80 8.9
2 16 9.9
2 15 10.9
;

data want;
   do _N_ = 1 by 1 until (last.ID);
      set have;
      by ID;
      if _N_ not in (1:3, 10:12) then output;
   end;
run;

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 09:21:07 GMT

@PeterClemmensen No they don't all have 12 observations.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 09:19:59 GMT

@Kurt_Bremser
See usable data below:

data have;
input ID Value DateTime;
datalines;
1 5 0.0
1 15 1.5
1 74 2.2
1 19 3.1
1 13 4.2
1 71 4.9
1 40 5.6
1 31 6.8
1 87 7.8
1 10 8.4
1 11 9.3
1 47 10.1
2 9 0.0
2 11 0.9
2 7 1.9
2 10 2.8
2 11 4.5
2 74 5.2
2 10 6.6
2 11 7.1
2 71 7.8
2 80 8.9
2 16 9.9
2 15 10.9
;

Please note that not every measurements has 12 observations.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

PeterClemmensen — Mon, 16 Nov 2020 09:29:04 GMT

data want(drop=i j);
   do i = 1 by 1 until (last.ID);
      set have;
      by ID;
   end;
   do j = 1 by 1 until (last.ID);
      set have;
      by ID;
      if 3 < j < i-2 then output;
   end;
run;

Result:

ID  Value DateTime 
1   19    3.1 
1   13    4.2 
1   71    4.9 
1   40    5.6 
1   31    6.8 
1   87    7.8 
2   10    2.8 
2   11    4.5 
2   74    5.2 
2   10    6.6 
2   11    7.1 
2   71    7.8

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

Kurt_Bremser — Mon, 16 Nov 2020 09:30:39 GMT

data want;
do until (last.id);
  set have;
  by id;
  if first.id then start = datetime;
  if last.id then end = datetime;
end;
do until (last.id);
  set have;
  by id;
  if start + 3 le datetime le end - 3 then output;
end;
drop start end;
run;

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 09:57:45 GMT

Thank you. I will check it

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 09:58:01 GMT

Thank you. I will check it.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

FreelanceReinh — Mon, 16 Nov 2020 12:36:11 GMT

Hello @AK100,

If performance is an issue, you may want to try a single-pass solution.

EDIT: The code below counts observations, not seconds. Assuming that the first 3 seconds correspond to 0<=DateTime<=3 (although these don't seem to be SAS datetime values), this could be adapted, but without a known end time the definition of the "last 3 seconds" seems doubtful to me.

data want(drop=_:);
do _i=1 by 1 until(last.id);
  set have;
  by id;
  if _i>3 then do; /* conditional only for performance improvement */
    _v=lag2(value);
    _dt=lag2(datetime);
  end;
  if _i>5 & last.id=0 then do;
    value=_v;
    datetime=_dt;
    output;
  end;
end;
run;

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 10:22:16 GMT

@FreelanceReinhard.

So this is the same as above, but much faster?

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

FreelanceReinh — Mon, 16 Nov 2020 13:31:01 GMT

The result is the same (EDIT: as obtained with draycut's solution), but the code should run somewhat faster -- how much faster depends, as usual, on hardware and software conditions, but also on the data (here, for example: the distribution of the number of observations per ID).

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 14:08:08 GMT

Where does the 5 stand for in your code?

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

FreelanceReinh — Mon, 16 Nov 2020 14:25:50 GMT

The LAG2 values in the 5th observation of an ID would come from the 3rd observation (because 5-2=3), hence would not qualify for output if the first three observations are to be excluded. Therefore, _i>5 is a necessary condition for the OUTPUT statement to be executed.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

AK100 — Mon, 16 Nov 2020 15:04:31 GMT

@FreelanceReinhard. So what if I wanted to remove the first and last 8 seconds.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

FreelanceReinh — Mon, 16 Nov 2020 15:16:45 GMT

Then you'd change the IF conditions and the length of the LAG queues as follows:

...
  if _i>8 then do; /* conditional only for performance improvement */
    _v=lag7(value);
    _dt=lag7(datetime);
  end;
  if _i>15 & last.id=0 then do;
...

To make the code flexible, you could use macro expressions involving a macro variable, say, d=8, in these four places. But, again, this code doesn't care for seconds, only for observations.

Re: How to remove the first 3 seconds and last 3 seconds of a measurement in SAS?

Kurt_Bremser — Mon, 16 Nov 2020 16:02:36 GMT

@AK100 wrote:
@FreelanceReinhard. So what if I wanted to remove the first and last 8 seconds.

Are we talking about seconds here or measurements (observations)?