Select row with datediff 1 and latest timestamp

Question

Sorry, there might be a really simple solution to this but I am new to SQL and SAS data integration studio. The code is in "proc SQL" in SAS-language.

I am trying to create a job in SAS DI and I am having trouble removing rows that are wrong. This is the dataset. There is no Primary-key, but the date is supposed to be unique. Hence, there should only be one row per date.

I would like to keep the row with the latest timestamp that has a datediff of 1. In this example it means the last row in the example data.

I have tried this code with no sucess:

proc sql;
create table TEST as
select datetime1, datetime2, column1, column2, column3 from table1 t1
where datetime1=(select max(datetime1) from table1 t2 where t1.datetime1=t2.datetime1)
order by datetime1;
quit;

Answer 1

If you want one row per date, then I think this is the logic:

proc sql;
create table TEST as
    select datetime1, datetime2, column1, column2, column3
    from table1 t1
    where datetime1 = (select max(tt1.datetime1)
                       from table1 tt1
                       where datepart(tt1.datetime1) = datepart(t1.datetime1)
                      )
    order by datetime1;
quit;