Is there some reason you are excluding midnight from your data? I would expect to see one of
26-Jul-23 24 OR 27-Jul-23 0 as a midnight value for ID 223257?
The below code doesn't exactly match your "want" because you don't have a midnight for 223257 and do for the 232706 id. So the definition of "24 hours" differs a bit between the two. If there is data problem with midnight consistently I might look into that. When midnight is not present your actual time interval between start and end is 25 hours. So something needs to be considered about either the problem requirement or the data condition when starting.
You do want to test your data step code before showing it. You didn't apply an informat for date so that throws invalid data messages and without the Infile datalines missover; (or truncover) only every other line of the data is actually read sort of because of the missing value for STATUS on most of your lines. The option Missover or Truncover will stop reading when the input line runs out. Without it the ID value is read as STATUS for most of the lines and the reading pointer advances at that point.
Quite often a process that uses date and time is best done by creating a DATETIME value and appropriate functions with those values because then SAS will take care of those pesky change of date behaviors.
My take on your problem (note the corrected data step code).
data Testing;
infile datalines missover;
input ID Date :date10. Hour Status;
format date date9.;
datalines;
223257 26-Jul-23 1
223257 26-Jul-23 2
223257 26-Jul-23 3
223257 26-Jul-23 4
223257 26-Jul-23 5
223257 26-Jul-23 6
223257 26-Jul-23 7
223257 26-Jul-23 8 1
223257 26-Jul-23 9
223257 26-Jul-23 10
223257 26-Jul-23 11
223257 26-Jul-23 12
223257 26-Jul-23 13
223257 26-Jul-23 14
223257 26-Jul-23 15
223257 26-Jul-23 16
223257 26-Jul-23 17
223257 26-Jul-23 18
223257 26-Jul-23 19
223257 26-Jul-23 20
223257 26-Jul-23 21
223257 26-Jul-23 22
223257 26-Jul-23 23
223257 27-Jul-23 1
223257 27-Jul-23 2
223257 27-Jul-23 3
223257 27-Jul-23 4
223257 27-Jul-23 5
223257 27-Jul-23 6
223257 27-Jul-23 7
223257 27-Jul-23 8
223257 27-Jul-23 9
223257 27-Jul-23 10
232706 26-Jul-23 1
232706 26-Jul-23 2
232706 26-Jul-23 3
232706 26-Jul-23 4
232706 26-Jul-23 5
232706 26-Jul-23 6
232706 26-Jul-23 7
232706 26-Jul-23 8
232706 26-Jul-23 9
232706 26-Jul-23 10
232706 26-Jul-23 11
232706 26-Jul-23 12 1
232706 26-Jul-23 13
232706 26-Jul-23 14
232706 26-Jul-23 15
232706 26-Jul-23 16
232706 26-Jul-23 17
232706 26-Jul-23 18
232706 26-Jul-23 19
232706 26-Jul-23 20
232706 26-Jul-23 21
232706 26-Jul-23 22
232706 26-Jul-23 23
232706 26-Jul-23 24
232706 27-Jul-23 1
232706 27-Jul-23 2
232706 27-Jul-23 3
232706 27-Jul-23 4
232706 27-Jul-23 5
232706 27-Jul-23 6
232706 27-Jul-23 7
232706 27-Jul-23 8
232706 27-Jul-23 9
232706 27-Jul-23 10
232706 27-Jul-23 11
232706 27-Jul-23 12
232706 27-Jul-23 13
232706 27-Jul-23 14
;
run;
proc sql;
create table want as
select b.id, b.date, b.hour ,b.datetime2 format datetime18.
from (select id, date,hour,status, dhms(date,hour,0,0) as datetime
from testing
where status=1) as a
left join
(select id, date,hour,status, dhms(date,hour,0,0) as datetime2
from testing) as b
on a.id=b.id and
a.datetime le b.datetime2 le intnx('hour',a.datetime,24,'b')
order by b.id, b.date, b.hour
;
quit;
The Input statement wants some way to know that DATE is to be read as date value using an informat such as Date10. The :date10. on the input statement tells SAS to read the next value and attempt to use that format. You could also use an INFORMAT statement to assign a format to the variable .
Proc SQL is a way to match records. Select lists variables or calculated values. The From provides instructions of where data comes from. Where places constraints on what is selected.
Note that placing a select /from /where inside paraentheses as a sub query provides one group of values, in this case those that only have Status=1 and refers to the whole set with the alias A. Values from that group are addressed with a.<variable name>.
Note that I used the DHMS function to combine the date and hour into a single datetime value.
The Left JOIN says take every record from A and join on the second alias (all of the records in testing plus an added datetime variable) where the ID is the same and the second datetime variable is between the Status=1 datetime and , using the INTNX function to advance that time, up to + 24 hours.
