The SAS Output Delivery System and reporting techniques

Calculating Maximum within one hour for a Variable...

Reply
Frequent Contributor
Posts: 78

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,132

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: 9,867

    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,156

    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=_Smiley Happy max=;

    ;

    Respected Advisor
    Posts: 3,156

    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=_Smiley Happy max=;

    ;

    Frequent Contributor
    Posts: 78

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

    I used

    Master   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" Smiley Wink

    Super Contributor
    Posts: 434

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

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

    Super Contributor
    Posts: 308

    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;

    Ask a Question
    Discussion stats
    • 10 replies
    • 1015 views
    • 5 likes
    • 8 in conversation