CODEWITHSUNDEEP
sqlsasduplicatesmax
Jarom
Jarom

Reputation: 1077

Pulling Duplicate Record with max value in PROC SQL

I'm working with a database with multiple duplicate records. It has an active record indicator that is not correct, so I basically have to create an active indicator manually using the date for the record.

See an example of the data below:

application_nbr date             Amount
138352          4/26/2017    $10,000.00 
138352          4/22/2017    $9,500.00 
553652          5/1/2017     $2,500.00 
332621          4/15/2017    $7,500.00 
332621          4/3/2017     $8,500.00

I need a query that will pull this from the above:

application_nbr date         Amount
138352          4/26/2017    $10,000.00 
553652          5/1/2017     $2,500.00 
332621          4/15/2017    $7,500.00

I'm really not sure how to tackle this one. I'm thinking some kind of a subquery using max(date)? I know I can't use functions like max in the where clause. Is there a fairly simple way to accomplish this?

Upvotes: 1

Views: 1674

Answers (4)

Shenglin Chen
Shenglin Chen

Reputation: 4554

Proc sql;
   select * from have group by application_nbr having date=max(date);
quit;

Upvotes: 0

G.Arima
G.Arima

Reputation: 1171

Since you are using SAS dataset: 

proc sort data=have;
by application_nbr date;
run;

data want;
set have;
by application_nbr date;
if first.date;
run;

Or if you really want to use proc sql then 

proc sql;  
select a.*,b.amount
from
(select application_nbr,max(date) as date 
 from have 
 group by application_nbr) a
inner join
have b
on a.application_nbr=b.application_nbr; 
quit;

Let me know in case of any queries.

Upvotes: 1

z m
z m

Reputation: 1513

Updated:

SELECT a.*
FROM table_name a
JOIN (SELECT application_nbr, MAX(date) AS max_date
      FROM table_name
      GROUP BY application_nbr) b
ON a.application_nbr = b.application_nbr

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270421

There are several ways to do this. In proc sql, I would go for re-merging:

proc sql;
    select t.*
    from (select t.*, max(date) as maxdate
          from t
         )
    where date = maxdate;

The typical way to do this in SQL is:

select t.*
from t join
     (select application_nbr, max(date) as maxdate
      from t
      group by application_nbr
     ) tt
     on t.application_nbr = tt.application_nbr;

Upvotes: 1

Related Questions