I am trying to run a query in Enterprise Guide that gives me the most recent records. I am working with mortgage tables that show multiple submits per LOAN_ID. So what I am trying to do is to return the latest full record (50 fields) with the LOAN_ID being unique in the output. To find the latest record, I can use either of 2 fields:
ANALYSIS_DATE (DATE)
EXECUTION_ID (NUMERIC - AUTONUMBER)
I realize that I can group by LOAN_ID and take the MAX of either of these fields, but that does not give me then newest entire record. Can anybody point me in the right direction?
Mahalo.
I don't use EG much, but in SQL query you have to join the summary query to the original table like in the query below. Maybe that will give you a clue to what you have to do in EG.
proc sql;
create table want as
select
m.*
from
mortgage_table m
join
( select loan_id, max( execution_id ) as max_id
from mortgage_table
group by load_id ) s
on
m.loan_id = s.loan_id and
m.execution_id = s.max_id
;
quit;
you can get what you want by using Sort task , not the query builder. first sort you data by loan_id and date, then sort by loan_id again and select the sort option
'only keep the first record of each 'Sort By' '. please check the attached screen shot.
Thank you Linlin. That was exactly what I was looking for!:smileycool:
you can sort in the desc order and keep only the 1st record as mentioned above!!!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.