find the latest time

[lerdem]

Hi,

I have a table as below:

 term      classday            crn     room      begin_time       end_time

2016     17feb2015         201     110          0700               0750

2016     17feb2015         206     110          1000               1100

2016     17feb2015         150     110          1500               1550

2016     17feb2015         150     110          1700               1730

2016     18feb2015         250     120          1500               1550

2016     18feb2015         201     120          1600                1700

2016     18feb2015         203     120           1600                1650

2016     18feb2015         202     133           1500               1600 

These are class days, class room, class times.  I need to find for each room latest class. So i need tol pull

 term      classday            crn     room      begin_time       end_time

2016     17feb2015         150     110          1700               1730

2016     18feb2015         201     120          1600                1700

2016     18feb2015         202     133           1500               1600 

[DanZ]

proc sort data=have;
by room end_time;
run;

data want;
set have;
by room end_time;
if last.room then output;
run;

[Kurt_Bremser]

proc sort data=have;
by classday room end_time;
run;

data want;
set have;
by classday room;
if last.room;
run;

[RW9]

proc sort, by room date time, then datastep, by room, if first.room then output.  

proc sort data=have;
  by room date time;
run;
data want;
  set have;
  by room;
  if first.room;
run;

Or SQL max date time.  There are many examples on here of this.

[ad123123]

Hi,

You can use following SQL query:-

proc sql;
create table want as select * from have t1 
where end_time=(select max(end_time) from have t2 where t1.term=t2.term 
and t1.classday=t2.classday 
and t1.room=t2.room
group by term,classday,room);
quit;

Please let me know if it helps you.

Regards,

Abd.