SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

I have a table that has ID, Code, and Date. Each ID can have multiple codes, each occurring on a different date. I only want the ID & Code for the most recent date.

 

How do I do this?


Accepted Solutions
Solution
‎11-16-2015 01:46 PM
Super User
Posts: 5,424

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

Two (of many more perhaps) choices:

  1.  First sort, like in your post title. The a new sort on ID alone, and nodupkey.
  2. Using Join transformation, Group By on ID, having date = max(date)
Data never sleeps

View solution in original post


All Replies
Solution
‎11-16-2015 01:46 PM
Super User
Posts: 5,424

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

Two (of many more perhaps) choices:

  1.  First sort, like in your post title. The a new sort on ID alone, and nodupkey.
  2. Using Join transformation, Group By on ID, having date = max(date)
Data never sleeps
Frequent Contributor
Posts: 77

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

Thank you! I was thinking about the double sort, but wasn't sure if the second sort would re-sort (and wipe out the first).

 

Ended up going with the join transformation option instead.

 

Thank you again!

Super User
Posts: 5,424

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

Happy to help 😊
But the double sort should be reliable. The PROC SORT doc states that NODUPKEY will keep the first row encountered for each by group.
What to chose is just matter of taste. The SQL alternative could perform better if your source table is stored in a powerful RDBMS server.
Data never sleeps
🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 277 views
  • 1 like
  • 2 in conversation