Desktop productivity for business analysts and programmers

Flaging the latest date

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Flaging the latest date

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.


Accepted Solutions
Solution
‎02-26-2018 12:00 AM
Super User
Posts: 5,917

Re: Flaging the latest date

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?
Data never sleeps

View solution in original post


All Replies
Valued Guide
Posts: 629

Re: Flaging the latest date

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.

Contributor
Posts: 70

Re: Flaging the latest date

Posted in reply to andreas_lds
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.
SAS Super FREQ
Posts: 401

Re: Flaging the latest date

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

 

screenshot.png

Solution
‎02-26-2018 12:00 AM
Super User
Posts: 5,917

Re: Flaging the latest date

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?
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 158 views
  • 1 like
  • 4 in conversation