CODEWITHSUNDEEP
sas
Jinhua Wang
Jinhua Wang

Reputation: 1759

SAS group rows based on time intervals

I am doing some analysis on a dataset with a variable named "time". The time is in the format of HH:MM:SS.

However, now I want to group the rows based on 5 secs and 1 min time intervals respectively in two different analysis.

I checked some StackOverflow posts online, but it seems that they used a variable called "Interval" which starts at 1 and increases when the interval ends. 

data _quotes_interval; 
  set _quotes_processed;
  interval = intck('MINUTE1',"09:30:00"t,time)+1;
run;

What I want to do is to keep the time format. For example, if the original time is 9:00:30, and I am doing the 1 min interval, I want to change the time to 9:00:00 instead.

Is there a way to do this?

Upvotes: 0

Views: 724

Answers (2)

Reeza
Reeza

Reputation: 21274

SAS stores time as the number of seconds. Since you want to round to the nearest minute or 5 minute, a translation would be, 5*60 = 300 & 60 seconds. The SAS round function supports this. 

time_nearest_minute = round(time, 60);
time_nearest_minute5 = round(time, 300);

Edit based on comment:

Time_nearest_second5 = round(time, 5);

Upvotes: 2

Dirk Horsten
Dirk Horsten

Reputation: 3845

For the processing by minute, you can use

data _quotes_interval / view = _quotes_interval; 
  set _quotes_processed;
  interval = intnx('MINUTE', time, 0, 'begin');
run;

intnx is by default used to add or substract time intervals. Here I add 0 binutes, but that is just because I need the function for its fourth parameter, which specifies to go back to the 'begin' of the interval of 1 minute.

PS: For preformance reasons, I would use the view= option, to create a view on the existing data instead of copying all data.

For processing by 5 second intervals

try interval = intnx('SECOND5', time, 0, 'begin');

Disclaimer:

I do not have SAS on this computer. If it does not work, react in a comment and I will test it at work.

Upvotes: 1

Related Questions