CODEWITHSUNDEEP
sqlsas
Atal Atmar
Atal Atmar

Reputation: 9

Get the latest date based on another column

enter image description here

I have a dataset that includes a column DATETIME and a column BANKCASENUMBER. I want to select the rows with the latest dates for each BANKCASENUMBER.

This is what I've tried:

PROC SQL;    
CREATE TABLE WORK.QUERY_FOR_WORK_QUERY_FOR_ACCOUNT AS 
   ORDER BY t1.DATETIME LIMIT 1
   SELECT t1.LOGINNAME, 
      t1.FORENAME, 
      t1.SURNAME, 
      t1.BANKCASENUMBER, 
      MAX(t1.DATETIME), 
      t1.'Inbound/outbound'n, 
      t1.'succesvol?'n
  FROM WORK.WORK_QUERY_FOR_ACCOUNTACTIVITIES t1
  GROUP BY t1.BANKCASENUMBER;
QUIT;

The returned table should givall the rows with the latset DATETIME for each BANKCASENUMBER. So each BANKCASENUMBER should appear once.

Upvotes: 0

Views: 126

Answers (2)

DCR
DCR

Reputation: 15657

this seems a little simpler. Since we are grouping by LOGINNAME the having statement will refer to the max(DATETIME) within each group

proc sql noprint;
   create table QUERY_FOR_WORK_QUERY_FOR_ACCOUNT as
     select * from WORK_QUERY_FOR_ACCOUNTACTIVITIES
       group by LOGINNAME
       having DATETIME = max(DATETIME);
quit;

Upvotes: 0

Josh Eller
Josh Eller

Reputation: 2065

I believe this is what you're looking for. The idea is to get the latest timestamp for each bank case number in a subquery, and join it to your main table.

PROC SQL;    
CREATE TABLE WORK.QUERY_FOR_WORK_QUERY_FOR_ACCOUNT AS
    SELECT t1.LOGINNAME, 
      t1.FORENAME, 
      t1.SURNAME, 
      t1.BANKCASENUMBER, 
      t1.DATETIME, 
      t1.'Inbound/outbound'n, 
      t1.'succesvol?'n
    FROM WORK.WORK_QUERY_FOR_ACCOUNTACTIVITIES t1
    JOIN (
        SELECT  t2.bankCaseNumber,
                MAX(t2.dateTime) as maxDateTime
        FROM WORK.WORK_QUERY_FOR_ACCOUNTACTIVITIES t2
        GROUP BY t2.BANKCASENUMBER
    ) m
        ON  m.bankCaseNumber = t1.bankCaseNumber
        AND t1.dateTime = m.maxDateTime
;
QUIT;

Upvotes: 1

Related Questions