## Calculating Maximum within one hour for a Variable...

Frequent Contributor
Posts: 79

# Calculating Maximum within one hour for a Variable...

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...

Respected Advisor
Posts: 4,695

## Re: Calculating Maximum within one hour for a Variable...

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;

Frequent Contributor
Posts: 115

## Re: Calculating Maximum within one hour for a Variable...

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

Super User
Posts: 10,695

## Re: Calculating Maximum within one hour for a Variable...

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

Respected Advisor
Posts: 3,167

## Re: Calculating Maximum within one hour for a Variable...

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;

Frequent Contributor
Posts: 89

## Re: Calculating Maximum within one hour for a Variable...

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=;

;

Respected Advisor
Posts: 3,167

## Re: Calculating Maximum within one hour for a Variable...

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=;

;

Frequent Contributor
Posts: 79

## Re: Calculating Maximum within one hour for a Variable...

I used

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

Frequent Contributor
Posts: 89

## Re: Calculating Maximum within one hour for a Variable...

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

Super Contributor
Posts: 625

## Re: Calculating Maximum within one hour for a Variable...

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

Super Contributor
Posts: 319

## Re: Calculating Maximum within one hour for a Variable...

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;

Discussion stats
• 10 replies
• 1099 views
• 5 likes
• 8 in conversation