topic Most Recent Records in SAS Enterprise Guide

Most Recent Records

MauiBound — Wed, 16 May 2012 18:32:29 GMT

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.

Re: Most Recent Records

FloydNevseta — Wed, 16 May 2012 20:11:36 GMT

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

m.loan_id = s.loan_id and

m.execution_id = s.max_id

;

quit;

Re: Most Recent Records

Linlin — Thu, 17 May 2012 14:52:32 GMT

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.

Re: Most Recent Records

MauiBound — Thu, 17 May 2012 21:29:32 GMT

Thank you Linlin. That was exactly what I was looking for!:smileycool:

Re: Most Recent Records

Petersas — Tue, 22 May 2012 04:55:30 GMT

you can sort in the desc order and keep only the 1st record as mentioned above!!!