CODEWITHSUNDEEP
sasproc-sql
Elliott
Elliott

Reputation: 1

Need Help to query SQL Server Table using SAS Proc SQL for most recent record

I am trying to query a sql server table of transactional data using SAS Proc SQL syntax.

I am unable to get the query to pull the most recent record from each case based on the last update date field.

Any assistance on the proper syntax would be appreciated.

Thanks, Elliott

max(date) having clause several others

I do not believe I can create a temp table on sql server as I do not have write capability to the server.

Upvotes: 0

Views: 108

Answers (2)

Richard
Richard

Reputation: 27498

Use group and having

Example:

create table want as
select * from transactions
group by case
having date = max(date)

You may get higher performance by forcing the heavy lifting to occur in the SQL Server

proc sql;
  connect to SQL ...
  create table want as 
  select * from connection to SQL
  (
    select * from transactions
    group by case
    having date = max(date)
  )

Upvotes: 0

yabwon
yabwon

Reputation: 341

How about something with a sub-query, like this:

select 
  a.* 
from 
  YourTable as a
inner join
  (
  select 
    groupingID
  , max (lastUpdateDate) as LUDT
  from 
    YourTable
  group by 
    groupingID
  ) as b
on 
  a.groupingID = b.groupingID
  and
  a.lastUpdateDate = b.LUDT
;

?

Upvotes: 1

Related Questions