This solution assumes the time data is continuous, i.e. each record is separated from the next by 1 second only. You can use the retain statement to get the incremental counts of speeds above (and, apparently, also equal) to 42 and 55. data have;
input date_time speed;
informat date_time datetime16.;
format date_time datetime16.;
cards;
11DEC16:23:53:21 35.00
11DEC16:23:53:22 39.00
11DEC16:23:53:23 45.00
11DEC16:23:53:24 55.00
11DEC16:23:53:25 56.00
11DEC16:23:53:26 58.00
11DEC16:23:53:27 56.00
11DEC16:23:53:28 55.00
11DEC16:23:53:29 50.00
11DEC16:23:53:30 40.00
11DEC16:23:53:31 53.00
11DEC16:23:53:32 55.00
11DEC16:23:53:33 56.00
11DEC16:23:53:34 58.00
;
run;
proc sort data=have out=have_sorted;
by date_time;
run;
data want;
set have_sorted;
by date_time;
retain above_42 above_55;
if speed >= 42 then do;
above_42 + 1;
if speed >= 55 then above_55 + 1;
else above_55 = 0;
end;
else above_42 = 0;
if missing(above_42) then above_42 = 0;
if missing(above_55) then above_55 = 0;
run; And if you wanted the final counts for each number of sustained seconds: /* Group runs for sustained counts */
data groupings;
set want;
by date_time;
retain group_42 group_55;
if _n_ = 1 then do;
group_42 = 1;
group_55 = 1;
end;
else do;
if above_42 = 0 then group_42 + 1;
if above_55 = 0 then group_55 + 1;
end;
run;
proc sql noprint;
create table sustained_42 as
select distinct seconds, count(distinct group_42) as count_42
from (select distinct max(above_42) as seconds, group_42
from groupings
where above_42 ^= 0
group by group_42)
group by seconds
order by seconds
;
create table sustained_55 as
select distinct seconds, count(distinct group_55) as count_55
from (select distinct max(above_55) as seconds, group_55
from groupings
where above_55 ^= 0
group by group_55)
group by seconds
order by seconds
;
quit;
data perfect;
do seconds = 1 to 10;
output;
end;
run;
data want_final;
merge perfect sustained_42 sustained_55;
by seconds;
array counts count_42 count_55;
do over counts;
if missing(counts) then counts = 0;
end;
run;
... View more
