BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PerNielsen
Calcite | Level 5

I have a data set that I would like to produce means, sum etc for a fixed interval of 15 minutes starting at at not so even time e.g. 10:02:53. So the file should be a sum or mean of the data between 10:02:53 and 10:17:53 and then again from 10:17:54 to 10:32:54 etc. I have tried with the round function but that does not work.

I need to merge two data sets and one has one data point per second and the other have a summary every 15 minutes but not starting on times that are easy to handle.

Anyone have a solution for this?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Looks to me like you will need something like this:

/* Create a datetime variable */

DT = dhms(date, hour(time), minute(time), second(time));

format DT datetime17.;

/* Calculate time interval */

timeInterval = floor((DT - '27FEB2015:10:05:48'dt) / '00:15:00't);

And then summarize your data by timeInterval.

PG

PG

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Start by assigning each row to a group variable.  You can then group by the group variable.  E.g.

data have;

  VALUE=123; TIME="10:02:53"t; output;

  VALUE=123; TIME="10:10:53"t; output;

  VALUE=123; TIME="10:17:53"t; output;

  VALUE=123; TIME="10:20:53"t; output;

run;

data want;

  set have;

  retain first_time current_group;

  if _n_=1 then do;

    first_time=time;

    current_group=1;

  end;

  else if time > first_time + "00:17:00"t then do;

    first_time=time;

    current_group=current_group+1;

  end;

  format first_time time time7.;

run;

ballardw
Super User

Since your problem involves a data merge you should provide a few lines of example data from each set and what you expect the resulting merge to look like.

If the issue involves getting items into actual time order then I would check to make sure you are using DATETIME variables if the data is longer than a given day. Then merging or set and sort would put things in the correct order.

PerNielsen
Calcite | Level 5

The data looks like this. I need to be able to summarize the data 'mot', 'stan', 'lie' and 'step' within 15 min interval starting at 10:05:48, so when summarizing the data I need to be able to specify when the first 15 min period should start.

Date           Mot  stan   Lie Step   Time     Cow

27/02/2015    0    100    0    0    10:01:18    4009

27/02/2015    0    100    0    0    10:01:19    4009

27/02/2015    0    100    0    0    10:01:20    4009

27/02/2015    0    100    0    0    10:01:21    4009

27/02/2015    0    100    0    0    10:01:22    4009

27/02/2015    0    100    0    0    10:01:23    4009

27/02/2015    0    100    0    0    10:01:24    4009

27/02/2015    0    100    0    0    10:01:25    4009

27/02/2015    0    100    0    0    10:01:26    4009

27/02/2015    0    100    0    0    10:01:27    4009

27/02/2015    0    100    0    0    10:01:28    4009

27/02/2015    0    100    0    0    10:01:29    4009

27/02/2015    0    100    0    0    10:01:30    4009

27/02/2015    0    100    0    0    10:01:31    4009

I want something like this:

datetimecow_TYPE__FREQ_mmotionmstandingmlyingmsteps
27FEB15:11:16:2340090900001000
27FEB15:11:31:2340090900001000

Does this make sense?

PGStats
Opal | Level 21

Looks to me like you will need something like this:

/* Create a datetime variable */

DT = dhms(date, hour(time), minute(time), second(time));

format DT datetime17.;

/* Calculate time interval */

timeInterval = floor((DT - '27FEB2015:10:05:48'dt) / '00:15:00't);

And then summarize your data by timeInterval.

PG

PG
PerNielsen
Calcite | Level 5

This works perfectly, Thanks for your help and thanks to all the participants in this discussion. Your input has been really valuable for me.

Ksharp
Super User

You can use sub-query of SQL to get it as well.

select *,(select sum(mot)  from have where  a.Time <= Time  <= a.Time+15*60) as mot

from have  as a

cmepdx
Calcite | Level 5

You could do something with a format to assign the intervals based on datetime ranges,  such as the following:

data intvls;

    format datetime datetime.;

    do datetime = '27FEB15:11:16:23'dt to '28FEB15:11:16:23'dt by 900;

        start = datetime;

        end = intnx('second', start, 899);

    output;

    end;

run;

data formats;

    set intvls;

    label = _n_;

    fmtname = 'cstmInt';

    type = 'I';

run;

proc format cntlin = formats;

run;

data have;

    do datetime = '27FEB15:11:16:23'dt to '28FEB15:11:16:23'dt;

    output;

    end;

run;

data want;

    format datetime datetime.;

    set have;

    interval = input(datetime, cstmInt.);

    nmotion = rannor(5);

run;

proc sql;

    SELECT interval 'Interval',

           min(datetime) AS intStart format datetime. 'First Time',

           max(datetime) AS intEnd format datetime. 'Last Time',

           count(*) 'Count',

           mean(nmotion) 'Mean nmotion'

    FROM want

    GROUP BY interval;

quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5140 views
  • 13 likes
  • 6 in conversation