topic Re: Calculating Maximum within one hour for a Variable... in ODS and Base Reporting

Calculating Maximum within one hour for a Variable...

rkumar23 — Sat, 10 Jan 2015 08:00:48 GMT

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

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

Patrick — Sat, 10 Jan 2015 10:17:25 GMT

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;

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

naveen_srini — Sat, 10 Jan 2015 10:23:22 GMT

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

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

Ksharp — Sat, 10 Jan 2015 13:17:31 GMT

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

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

Haikuo — Sun, 11 Jan 2015 00:22:48 GMT

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;

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

Jaheuk — Mon, 12 Jan 2015 15:05:39 GMT

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

;

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

Haikuo — Mon, 12 Jan 2015 15:12:39 GMT

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

;

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

Loko — Mon, 12 Jan 2015 16:06:25 GMT

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;

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

rkumar23 — Tue, 13 Jan 2015 01:08:00 GMT

I used

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

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

Jaheuk — Thu, 22 Jan 2015 08:33:18 GMT

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

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

Babloo — Thu, 22 Jan 2015 11:38:50 GMT

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