topic EG - Using query builder, how to get row with latest date value? in SAS Enterprise Guide

EG - Using query builder, how to get row with latest date value?

ncsthbell — Wed, 07 Mar 2012 20:06:55 GMT

The data I am retrieving in the EG project has multiple rows for a 'Vendor# & 'Invoice#'. Each of these rows has a column with a date field 'Last_updated'. I need to be able to retrieve the row for this Vendor#/Invoice# with the latest date in the 'Last_updated'. I am trying to build this using EG query builder but I do not see a function that I can use to accomplish this. Any suggestions on how to accomplish this in EG?

THanks

EG - Using query builder, how to get row with latest date value?

Technolero — Wed, 07 Mar 2012 20:29:43 GMT

On the Select Data tab, choose Vendor# and Invoice# and Last_updated. In the Last_updated row, select MAX from the Summary column. EG will automatically do summarization of the latest date by Vendor# and Invoice#.

EG - Using query builder, how to get row with latest date value?

ncsthbell — Wed, 07 Mar 2012 20:51:06 GMT

This works if I only include the Vendor & Invoice number. I have many more columns of data that I need, the Vendor & Invoice number are just keys that I would be looking to group by. I put the other columns on my select tab and since it is looking at each column on the select, I did not get what I need. I should have mentioned that I have other data I need on the select tab and the 'grouping' used for finding the lastest dated row would be by vendor# and invoice#.

EG - Using query builder, how to get row with latest date value?

Technolero — Wed, 07 Mar 2012 20:59:15 GMT

I would run the query that I described above, then use that output to join back to your original data set to select the rows that match Vendor#, Invoice#, and Last_Updated.

EG - Using query builder, how to get row with latest date value?

ncsthbell — Wed, 07 Mar 2012 21:00:55 GMT

Thanks, I'll try that.