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.
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.
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
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.