Calculating Maximum within one hour for a Variable...

[rkumar23]

Date                 Time   library  Var1     Var2

09/01/2015           00:00  xxxxx     2       6

09/01/2015           00:15  xxxxx     1       9

09/01/2015           00:30  xxxxx     5       8

09/01/2015           00:45  xxxxx     0       10

09/01/2015           01:00  xxxxx     7        6

09/01/2015           01:15  xxxxx     9        6

09/01/2015           01:30  xxxxx     10       0

09/01/2015           01:45  xxxxx     11       9

09/01/2015           02:00  xxxxx     18       10

Could somebody please throw me idea how to calculate maximum value for Variable "Var1" & "Var2"

within One hour of Boundaries so for example on 09/01/2015 between 00:00 - 00:45 for values(2,1,5,0)

the output should be 5 ...Whereas for Var2 it should be 10..

Then it should start boundaries for 01:00 - 01:45 and extract out maximum value  so "Var1" will have

11 and "Var2" will have 9 ...

Important is the boundaries of an hour where data is in the 15min interval ...and we may have possibility

where for certain interval data may be missing however while setting boundaries we still should consider

one hour...so just for example may possible it has 01:30 values can be missing however if so it still

just consider boundary of 01:00 - 01:45...

[Patrick]

See below:

data have;

  infile datalines truncover;

  input Date:ddmmyy. Time:time. library $ (Var1 Var2) (:8.);

  format date date9. time time5. datetime dt_interval_start datetime.;

  datetime=86400*date + time;

  dt_interval_start=intnx('dthour',datetime,0,'b');

  datalines;

09/01/2015 00:00 xxxxx 2 6

09/01/2015 00:15 xxxxx 1 9

09/01/2015 00:30 xxxxx 5 8

09/01/2015 00:45 xxxxx 0 10

09/01/2015 01:00 xxxxx 7 6

09/01/2015 01:15 xxxxx 9 6

09/01/2015 01:30 xxxxx 10 0

09/01/2015 01:45 xxxxx 11 9

09/01/2015 02:00 xxxxx 18 10

;

run;

proc sql;

  create table want1 as

    select *, max(var1) as max_var1, max(var2) as max_var2

    from have

    group by dt_interval_start

    ;

  create table want2 as

    select distinct dt_interval_start, max(var1) as max_var1, max(var2) as max_var2

    from have

    group by dt_interval_start

    ;

  /* without dt_interval_start pre-calculated in a previous data step */

  create table want4 as

    select distinct

        intnx('dthour',(86400*date + time),0,'b') as dt_interval_start format=datetime.

      , max(Var1) as max_var1

      , max(Var2) as max_var2

    from have

    group by calculated dt_interval_start

  ;

quit;

/* or if your data is already sorted */

data want3;

  set have;

  by dt_interval_start;

  retain max_var1 max_var2;

  max_var1=max(max_var1,var1);

  max_var2=max(max_var2,var2);

  if last.dt_interval_start then

    do;

      output;

      call missing(max_var1,max_var2);

    end;

run;

[naveen_srini]

Is this something you want? Try and test!

data want;

set have; /*your input dataset*/

array array1(10)_temporary_;

array array2(10)_temporary_;

if minute(time)=0 and second(time)=0 then do;

call missing (of array1{*});

call missing (of array2{*});

count=0;

end;

count+1;

array1(count)=var1;

array2(count)=var2;

if Minute( time )=45 then do;

var1=max(of array1

);

var2=max(of array2

);

end;

drop count;

run;

I am not sure if I understood the question well enough. My apologies if that didn't give you what you want.

Regards,

Naveen Srinivasan

L&T Infotech

[Ksharp]

Like Patrick's code.

data have;
  infile datalines truncover;
  input Date:ddmmyy. Time:time. library $ (Var1 Var2) (:8.);
  format date date9. time time5. ;
  datalines;
09/01/2015 00:00 xxxxx 2 6
09/01/2015 00:15 xxxxx 1 9
09/01/2015 00:30 xxxxx 5 8
09/01/2015 00:45 xxxxx 0 10
09/01/2015 01:00 xxxxx 7 6
09/01/2015 01:15 xxxxx 9 6
09/01/2015 01:30 xxxxx 10 0
09/01/2015 01:45 xxxxx 11 9
09/01/2015 02:00 xxxxx 18 10
;
run;
 
proc sql;
  create table want1 as
    select *, max(var1) as max_var1, max(var2) as max_var2
    from have
    group by date,hour(time)
    ;
quit;

Xia Keshan

Message was edited by: xia keshan

[Haikuo]

Yet, another option using SAS built-in procs, say if we have datetime variable like the one in :

data have;

     infile datalines truncover;

     input Date:ddmmyy. Time:time. library $ (Var1 Var2) (:8.);

     format date date9. time time5. datetime dt_interval_start datetime.;

     datetime=86400*date + time;

     dt_interval_start=intnx('dthour',datetime,0,'b');

     put datetime= datetime11.;

     datalines;

09/01/2015 00:00 xxxxx 2 6

09/01/2015 00:15 xxxxx 1 9

09/01/2015 00:30 xxxxx 5 8

09/01/2015 00:45 xxxxx 0 10

09/01/2015 01:00 xxxxx 7 6

09/01/2015 01:15 xxxxx 9 6

09/01/2015 01:30 xxxxx 10 0

09/01/2015 01:45 xxxxx 11 9

09/01/2015 02:00 xxxxx 18 10

;

run;

proc summary data=have;

     var var1 var2;

     class datetime;

     format datetime datetime11.;

     output out=want (where=(_type_=1)) max=;

run;

[Jaheuk]

why not use HOUR as a classification ??

it is by date and hour!

data step . . . .

hhour=hour(time);

. . . .

proc summary data=have missing nway;

class date hhour;

var var1 var2;

output out=want (drop=_:) max=;

;

[Haikuo]

Good Point!  Then I guess you don't even need additional hhour=hour(time), instead, just:

proc summary data=have missing nway;

class date time;

var var1 var2;

format time time2.;

output out=want (drop=_:) max=;

;

[rkumar23]

I used

Hai.kuo  method which really worked for me...Thanks all for your reply....

[Jaheuk]

please mark this as "answered" so I receive "points"

[Babloo]

what is purpose of ':' in your input statement?

[Loko]

hello,

a data step solution:

data have;
input Date ddmmyy10. Time :time5. library $ Var1 Var2;
format date date9. time time5.;
datalines;
09/01/2015 00:00 xxxxx 2 6
09/01/2015 00:15 xxxxx 1 9
09/01/2015 00:30 xxxxx 5 8
09/01/2015 00:45 xxxxx 0 10
09/01/2015 01:00 xxxxx 7 6
09/01/2015 01:15 xxxxx 9 6
09/01/2015 01:30 xxxxx 10 0
09/01/2015 01:45 xxxxx 11 9
09/01/2015 02:00 xxxxx 18 10
10/01/2015 03:00 xxxxx 7 6
10/01/2015 03:15 xxxxx 9 6
10/01/2015 03:30 xxxxx 10 0
10/01/2015 03:45 xxxxx 11 9
10/01/2015 04:45 xxxxx 12 19
;
run;

proc sort data=have;
by date;
run;

data want (keep=dateout hourinterval maxvar1 maxvar2) ;
set have end=last;
by date;

retain dateout hourflag maxvar1 maxvar2;
format dateout date9.;

if first.date then
do;
  if _N_ ne 1 then do;*write to output when first date changes;
   hourinterval=cats(hourflag||"-"||left(hour(time)));
   output;
  end;
  dateout=date;
  maxvar1=var1;
  maxvar2=var2;
  hourflag=hour(time);
  put hourflag=;
end;
else if hourflag=hour(time) then
do;
  if maxvar1 lt var1 then maxvar1=var1;
  if maxvar2 lt var2 then maxvar2=var2;
end;
else if hourflag ne hour(time) then
do;
  put _N_=;
  hourinterval=cats(hourflag||"-"||left(hour(time)));
  output;
  hourflag=hour(time);
  maxvar1=var1;
  maxvar2=var2;
end;

/*write last record*/
if last then
do;
  hourinterval=cats(hourflag||"-"||left(hour(time)));
  output;
end;

run;