topic Flaging the latest date in SAS Enterprise Guide

Flaging the latest date

anirudhs — Thu, 15 Feb 2018 06:54:47 GMT

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.

Re: Flaging the latest date

andreas_lds — Thu, 15 Feb 2018 08:03:54 GMT

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

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.

Re: Flaging the latest date

anirudhs — Thu, 15 Feb 2018 09:22:44 GMT

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.

Re: Flaging the latest date

LinusH — Fri, 16 Feb 2018 15:53:20 GMT

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?

Re: Flaging the latest date

CaseySmith — Fri, 23 Feb 2018 07:16:54 GMT

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