First Record of Each Group in Query Builder

jen123 — Sat, 08 Oct 2011 19:53:46 GMT

I have a table called "test". It contains all the employment applications received. The dataset contains numerous rows for each application because each is reviewed by various people. I wrote a code create a dataset containing data when each application was first reviewed. Is there a way to do the same in query builder? Thanks!

First Record of Each Group in Query Builder

LinusH — Mon, 10 Oct 2011 17:59:57 GMT

Depends on how your source table look like (and how you solved in your program).

But if it can be solved in a single, fairly simple SQL, the chances are, yes, you might can.

If you have an application id, and a timestamp on each row which defines the time when the application was reviewed, you might do something like this:

Define MIN as summary on your timestamp column
Define the application id as Group By column. You'll have to un-check "Automatically Select groups" if you have other columns that you want to keep, other than the id.
Define a Having condition that will filter out rows that has timestamp = min(timestamp):

Filter data - Filter Summarized Data - New Filter
Basic Filter
Select MIN_of_timestamp column from the data columns section
Define it as Equal to - Value - Columns - Timestamp

And run...

/Linus

topic First Record of Each Group in Query Builder in SAS Enterprise Guide

First Record of Each Group in Query Builder

First Record of Each Group in Query Builder