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;

*keep those rows with the latest timestamp that have a datediff of 1*: this is not clear to me, would you please give more precisions, or better yet provide sample data and expected output? — GMB, Mar 17 '19 at 20:08
So the table that you create will only contain one record, ie the most recent record in the original table, is that correct? — GMB, Mar 17 '19 at 20:46
Well actually, the dataset is much larger (couple of thousand rows), i.e. a lot more dates. The example data only had one date example. So, I would like to keep the rows that differ by 1 in date and has the latest timestamp. In this case it means 10jan2016 00:00:06:00000 and 11jan2016 06:03:00:00000, i.e. the last row. In the real dataset it means "erroneous" lines are deleted. — n00b, Mar 17 '19 at 20:54

score 0 · Answer 1 · answered Mar 17 '19 at 22:02

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;

Select row with datediff 1 and latest timestamp

1 Answers1