DATA Step, Macro, Functions and more

find the latest time

Reply
Contributor
Posts: 47

find the latest time

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 

 

 

Contributor
Posts: 38

Re: find the latest time

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

data want;
set have;
by room end_time;
if last.room then output;
run;
Super User
Posts: 6,966

Re: find the latest time

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

data want;
set have;
by classday room;
if last.room;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,420

Re: find the latest time

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.

Occasional Contributor
Posts: 18

Re: find the latest time

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.

Ask a Question
Discussion stats
  • 4 replies
  • 274 views
  • 0 likes
  • 5 in conversation