topic Re: bring in latest record from teradata in SAS Programming

bring in latest record from teradata

nickspencer — Tue, 05 Jan 2021 02:36:13 GMT

I am trying to bring in latest record from teradata based on two IDs, but without grouping them.
Both these IDs have been reassigned to each other so I want to pick up the latest.
I do not want to have duplicates by any IDs.
Have:
ID1 ID2 date
123 987 12/01/2020
123 654 05/10/2020
345 999 01/20/2020
456 999 12/20/2020
231 789 12/10/2020
231 789 12/21/2020

Want:
ID1 ID2
123 987
456 999
231 789

Since the data is in teradata, might be efficient to use sql/teradata sql instead of data step? Please advice.

Re: bring in latest record from teradata

SASKiwi — Tue, 05 Jan 2021 03:45:06 GMT

Something similar to this should do it:

libname TD teradata noprompt = "< Teradata connection string>" DATABASE = MyDatabase schema = MySchema; 

proc sql;
  create table Want as
  select  A.ID1
         ,A.ID2
         ,A.Date
  from TD.Have as A
  inner join 
  (select  ID1
          ,ID2
          ,max(Date) as Max_Date
   from TD.Have
   group by  ID1
            ,ID2
  ) as B
  on A.ID1 = B.ID1
  and A.ID2 = B.ID2
  and A.Date = B.Max_Date
  ;
quit;