Flaging the latest date

[anirudhs]

Hi i want to use the latest date from the following data using the query builder 

CASE_ID,Activity_Creator,Decisioned_By,comment,Suspect_Reason,Agency_Date
25412,RAPID INVESTIGATION SERVICES,Agency,CREDIT REFER,06FEB2018:15:35:14.00
25412,RAPID INVESTIGATION SERVICES,Agency,CREDIT REFER,06FEB2018:15:33:00.00
14284,SHARP EAGLE INVESTIGATION PVT LTD,Agency,SALARY SLIP VERIFICATION,01NOV2017:15:33:41.00
14284,SHARP EAGLE INVESTIGATION PVT LTD,Agency,VERIFICATION OF SALARY SLIP,01NOV2017:15:40:11.00

want to create new column as flag and flag only the latest date .

so that i use its distinct records only. 

Or can anyone suggest how can we use group by in query builder.

[andreas_lds]

Clone of https://communities.sas.com/t5/Base-SAS-Programming/How-to-flag-the-latest-date-or-use-group-by/m-p/...

Please post the data you want to be flagged as data-step and the expected result dataset, so that we can see what you have and need.

In SQL you could do something like "... having Agency_Date = max(Agency_Date)" if Agency_Date is a proper sas datetime.

[anirudhs]

Hi I want the following records in the data set .
CASE_ID,Activity_Creator,Decisioned_By,comment,Suspect_Reason,Agency_Date
25412,RAPID INVESTIGATION SERVICES,Agency,CREDIT REFER,06FEB2018:15:35:14.00
14284,SHARP EAGLE INVESTIGATION PVT LTD,Agency,VERIFICATION OF SALARY SLIP,01NOV2017:15:40:11.00

As these are eg ... but the logic should work on multiple observations.. from which i should get the latest of the grouped dates.

[CaseySmith]

In the EG Query Builder (screenshot below), drag the variables you want to group by (ex. CASE_ID, Activity_Creator, Decisioned_By, and Agency_Date (but not Suspect_Reason, since different values) into selected columns (on the Select Data tab). Then, in the Summary column, select MAX. Notice the summary groups will be automatically selected (at the bottom). Now run the query and you should get the results you desire. If you want the full records, you could then left join that query result back with the original table.

Casey

[LinusH · Accepted solution]

In SQL you could probably do a case when agency_date=max(agency_date), not sure if you could pull it off in query builder though.
(Why) is it important to use the Query Builder specifically?