BookmarkSubscribeRSS Feed
rkumar23
Calcite | Level 5

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

10 REPLIES 10
Patrick
Opal | Level 21

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
Quartz | Level 8

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
    Super User

    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
    Onyx | Level 15

    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
    Obsidian | Level 7

    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
    Onyx | Level 15

    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
    Calcite | Level 5

    I used

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

    Jaheuk
    Obsidian | Level 7

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

    Babloo
    Rhodochrosite | Level 12

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

    Loko
    Barite | Level 11

    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;

    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!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    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
    • 10 replies
    • 2264 views
    • 5 likes
    • 8 in conversation