BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anirudhs
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
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

4 REPLIES 4
andreas_lds
Jade | Level 19

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
Obsidian | Level 7
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
SAS Employee

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


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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